[RESOLVED] Tricky Format Question ???

[JohnSavage]

Here’s one that I have not got a clue on. I have read the VB help on format and custom format but this does not help.

I want to save to a database a value as a double but display it as minutes/seconds/hundreds of a second just like a stopwatch time

01:45:23

Which would be 1 minute 45 seconds and 23 hundreds of a second.

Which would be stored in the database as a double value of 105.23 if you see what I mean, but displayed in my label as 01:45:23

I have quickly put together a test app with just a label and a command button which of course given I have do it does not work as you cannot add 0.01 to a label formatted as I have done.

Any pointers on how I can go about this one

Private Sub Command1_Click()
Label1.Caption = Label1.Caption + 0.01
Label1.Caption = Format(Label1.Caption, "#######00:00:00")
End Sub

[aikidokid]

You could try converting the caption to a string first - this may work.

[JohnSavage]

Now I have

Option Explicit
Dim TimeS As String

Private Sub Command1_Click()

TimeS = TimeS + 0.01
Label1.Caption = Format(TimeS, "#######00:00:00")

End Sub

Private Sub Form_Load()
TimeS = 0

End Sub

But the format does not work label1 remains at 00:00:00 no matter how many times I click command1, that is whatever the value of TimeS is makes no difference

[zeezee]

You could try converting the caption to a string first - this may work.

Caption is String??

@JohnSavage
This double value is a value you created right? So I dont think VB will be able to format it to your Format. Try this

Public Function FormatTime(TimeVal As Double) As String
    Dim TimeString As String
    Dim Mins As String
    Dim Secs As String
    Dim Fractions As String
    Mins = CStr(TimeVal \ 60)
    Secs = CStr(Int(TimeVal) Mod 60)
    Fractions = Mid$(CStr(TimeVal), InStr(1, CStr(TimeVal), ".") + 1)
    TimeString = Format(Mins, "0#") & ":" & Format(Secs, "0#") & ":" & Format(Fractions, "0#")
    FormatTime = TimeString
End Function

This is not complete. Add the validatins.

[zeezee]

You said you have double value. Then what is this?

Dim TimeS As String

[aikidokid]

You said you have double value. Then what is this?

Dim TimeS As String

That was because of what I suggested

I misread the question, sorry.

[zeezee]

No Problem. I hope OP resolves the error.

[MartinLiss]

Here's one way

Const TEST_DATA As Double = 105.23

Dim Hours As Integer
Dim Minutes As Integer

Minutes = TEST_DATA Mod 60
Hours = (Int(TEST_DATA) - Minutes) / 60
MsgBox Hours & ":" & Minutes & ":" & Format((TEST_DATA - Int(TEST_DATA)) * 100, "0#")

[zeezee]

Martin. I think its same as the one I gave in Post #4??
BTW, the problem here is his Double val has Minites, Seconds and 100th of Seconds. No Hours.

EDIT: ON second Thought Its a better one to my solution. So Op could make it a function ?

[MartinLiss]

Martin. I think its same as the one I gave in Post #4??
BTW, the problem here is his Double val has Minites, Seconds and 100th of Seconds. No Hours.

EDIT: ON second Thought Its a better one to my solution. So Op could make it a function ?

Sorry I didn't notice your post, but why do you say there is a problem with hours when the minutes are fairly easily converted into hours?

[JohnSavage]

Forget the double that is what I store it in the database as

Now I have

Option Explicit
Dim TimeVal As Double

Private Sub Command1_Click()

TimeVal = TimeVal + 0.01

Call FormatTime(TimeVal)

End Sub

Public Function FormatTime(TimeVal As Double) As String
    Dim TimeString As String
    Dim Mins As String
    Dim Secs As String
    Dim Fractions As String
    Mins = CStr(TimeVal / 60)
    Secs = CStr(Int(TimeVal) Mod 60)
    Fractions = Mid$(CStr(TimeVal), InStr(1, CStr(TimeVal), ".") + 1)
    TimeString = Format(Mins, "0#") & ":" & Format(Secs, "0#") & ":" & Format(Fractions, "0#")
    
    Label1.Caption = TimeString
End Function

Private Sub Form_Load()
TimeVal = 0
End Sub

Which thanks to you is a great improvement on what I started with.

But it still does not work it displays 10 as 01 and then 20 as 02 and so on.

Another problem is when the number gets to say around 00:00:70 then is all of a sudden starts to display to around 7 decimal places

But thanks for your efforts so far

[MartinLiss]

Did you try mine?

[JohnSavage]

Martin

Yes yours worked, but forgive me it will take me a while to test it in the simple app I have going

Thanks

[JohnSavage]

Now I have tested it, it almost works the problem is still in the formatting

1:9:09 should be formatted as 01:09:09

Is there a simple way to get this , not that anything is ever simple in VB to me


ps I have noticed also that a well formatted number all of a sudden changes to 8 decimal places why does this happen as it is a problem I have had before

[MartinLiss]

Sure.

Const TEST_DATA As Double = 105.23

Dim Hours As Integer
Dim Minutes As Integer

Minutes = TEST_DATA Mod 60
Hours = (Int(TEST_DATA) - Minutes) / 60
MsgBox Format(Hours, "0#") & ":" _
     & Format(Minutes, "0#") & ":" _
     & Format((TEST_DATA - Int(TEST_DATA)) * 100, "0#")

[JohnSavage]

Still battling on, this is what I have now

Option Explicit
Dim TimeVal As Double

Private Sub Form_Load()
TimeVal = 30
End Sub

Private Sub ccrpUpDown1_DownClick()
TimeVal = TimeVal - 0.01
Call Display
End Sub

Private Sub ccrpUpDown1_UpClick()
TimeVal = TimeVal + 0.01
Call Display
End Sub

Private Sub Display()
Dim Hours As Integer
Dim Minutes As Integer
Minutes = TimeVal Mod 60
Hours = (Int(TimeVal) - Minutes) / 60
Label1.Caption = Format(Hours, "0#") & ":" & Format(Minutes, "0#") & ":" & Format((TimeVal - Int(TimeVal)) * 100, "0#")
Label2.Caption = TimeVal
End Sub

As you can see I have added a spinbutton to make changing the value quicker but the methods are the same. I have also added another label (label2) so I can see what is going on.

When the TimeVal = 30.49 then label1 has 00:30:49 which is perfect

But when TimeVal =30.5 then label1 has 00:31:50 which does not make any sense at all

[MartinLiss]

I'm confused. You say that 30.49 means 30 minutes and 49 seconds, but then you say that 30.5 isn't 30 minutes 50 seconds. What does the decimal portion represent?

[LaVolpe]

The decimal portion is in 100ths of a minute correct? But the display portion is in 60ths of a minute corrrect? I think your calcs for the seconds are off. Try this instead:
Format(60*(TimeVal - Int(TimeVal)) , "0#")

When the TimeVal = 30.49 then label1 has 00:30:49 which is perfect

P.S. If I am interpretting correctly, then the 30:49 is not perfect because .49 is not 49 seconds, it is closer to 30 seconds, 1/2 a minute.

[MartinLiss]

If what LaVolpe says is true then you just need to make a small change to my code.

Const TEST_DATA As Double = 30.5

Dim Hours As Integer
Dim Minutes As Integer

Minutes = TEST_DATA Mod 60
MsgBox Format(Hours, "0#") & ":" _
     & Format(Minutes, "0#") & ":" _
     & Format((TEST_DATA - Int(TEST_DATA)) * 60, "0#")

[JohnSavage]

Martin

Changing to 60 just makes more of a problem

I am trying to format a number of seconds into minutes / seconds and tenths of a second

so 90.03 should come out as 01:30:03

[MartinLiss]

So what would 90.5 be?

[LaVolpe]

And what would 90.75 be? Sounds like it should be 1:30:45

[JohnSavage]

No that would be

01:30:75

as the original (90.75) number is also in seconds

[MartinLiss]

So why isn't 30.5 ==> 00:31:50

[LaVolpe]

I'm sure Martinliss meant 00:30:50
If 90.75 is not 1:30:45 and 90.03 is 1:30:03 then 30.5 must be 00:30:50, likewise, 30.05 would be 30:05

I think both of us are confused. The decimal portion, what is the max range? Is it 0 to 99? If so is the display suppose to be .00 to .99?

Edited: you mentioned 10ths of a second in one of your posts? Just to be sure, you are not trying to display something like the following, are you?

hh : mm : ss : tt where tt is tenths.

[JohnSavage]

Lets start again the number I want to format is seconds.

I want to format it into minutes and seconds but also display it’s decimal element to 2 decimal places therefore

5.6 would be 00:05:60
5.06 would be 00:05:06
60.01 would be 01:00:01
75.09 would be 01:15:09
130.75 would be 02:10:75

Or am I going senile

[LaVolpe]

No you are not going senile and I misread something or you posted something incorrect.

Minutes = TimeVal Mod 60
Hours = (Int(TimeVal) - Minutes) / 60
Label1.Caption = Format(Hours, "0#") & ":" & Format(Minutes, "0#") & ":" & Format((TimeVal - Int(TimeVal)) * 100, "0#")

But when TimeVal =30.5 then label1 has 00:31:50 which does not make any sense at all

When I try that, the value is 00:30:50 not 00:31:50

[JohnSavage]

Here is the code

Private Sub Display()
Dim Hours As Integer
Dim Minutes As Integer
Minutes = TimeVal Mod 60
Hours = (Int(TimeVal) - Minutes) / 60

Label1.Caption = Format(Hours, "0#") & ":" & Format(Minutes, "0#") & ":" & Format((TimeVal - Int(TimeVal)) * 100, "0#")

Label2.Caption = Format(TimeVal, "#.00")
End Sub

When label2 is 30.49 then it formats label1 to 00:30:49 which is correct

When label2 is 30.50 then it formats label1 to 00:31:50 which is incorrect, label1 should be 00:30:50

[LaVolpe]

Your pc can't add . My label1 shows 00:30:50 and label2 shows 30.50
All I did was copy your exact code to a click event, and add the additional code of: Dim TimeVal As Double & TimeVal = 30.5

Edited. Ok, but if I make TimeVal=31.5, I get 32.5. Rounding issues when dividing. Calculate minutes differently: Minutes = Int(TimeVal) Mod 60

[Comintern]

I think you may be making this more difficult than you need to. All you have to do is pass the integer portion of the double to TimeSerial to cast it as a date. That part of the format is easy. The other part is to scale the float by 100 so you always get 2 digits when you format as a number:

Private Sub Test()

    Debug.Print CustomFormat(130.5)

End Sub

Private Function CustomFormat(dTime As Double) As String

    Dim d100ths As Double, dTemp As Date

    d100ths = (dTime - Int(dTime)) * 100            'Calculate 100ths and scale by 10^2.
    dTemp = TimeSerial(0, 0, Int(dTime))            'Convert to a date type.
    CustomFormat = Format$(dTemp, "nn:ss:") & Format$(d100ths, "##")  'Return formatted.

End Function

[JohnSavage]

LaVolpe

It now appears that my computer can now add up

Your change "Minutes = Int(TimeVal) Mod 60" did the job

Many Thanks to you again

Martin - thanks also to you

Comintern I am am sure your solution is correct but as I have no idea how to fit it into the code above I will stick with LaVolpe's solution - but thanks for your input

[Comintern]

Comintern I am am sure your solution is correct but as I have no idea how to fit it into the code above I will stick with LaVolpe's solution - but thanks for your input

Just convert it to a one-liner (no need for any other code).

Label1.Caption = Format$(TimeSerial(0, 0, Int(TimeVal)), "nn:ss:") & _
                 Format$((TimeVal - Int(TimeVal)) * 100, "##")

[JohnSavage]

Many thanks

[JohnSavage]

Comintern

Just got around to trying your solution this morning.

It does not work, it's OK for say 30.73 which it formats to 00:30:73

But 30.09 it formats to 00:30:9 and 30.00 it would format to 00:30:

But thanks anyway!!

[Comintern]

My bad. It the same thing Martin did in his first shot. It should be:

Label1.Caption = Format$(TimeSerial(0, 0, Int(TimeVal)), "nn:ss:") & _
                 Format$((TimeVal - Int(TimeVal)) * 100, "0#")

[JohnSavage]

Brilliant - one line instead of 5 now if I could code like that all my app's would be less than 1mb