Weird time format and how can I reverse it?

[Vita]

Hey so my database has time stamps in a weird format. I have figured out how to decode it but I am struggling to figure out how to get the current time and encode it in the same format (so that another program can read it too) in vba.

Here is an example
9.99169 = 9:59:30.84 AM
Let me explain


So first we separate the whole number (in this case 9) and that becomes the hour. It is stored in military time so I have some code to adjust that below.
We then take the decimal and multiply it by 60. So .99169 x 60 = 59.5014. That whole number is our minutes. (So 9:59 now)
Then again multiply the decimal by 60. (.5014 x 60 = 30.084) and that whole number is our seconds. (9:59:30)
Here is the only variation and I am not sure it is even needed since I don't know what this time format is called but I then do (.084 x 1000 = 84) and add a decimal into the front of it. (so 9:59:30.84)
I don't think the last step needs to be multiplied by 1000 since milliseconds are usually written as thousands but on the other hand I thought it would follow suit with the formula. (input welcome)

If anyone knows what this method is called I would love to know.
Here is the code I wrote to decode it.

Public Function TimeConvert(Time As String, Optional FieldName As String)
Dim Hours As String, Minutes As String, Seconds As String, MSeconds As String, TimePos As String, AMPM As String, i As Integer
TimePos = "Hours"


Time = Format(Time, "00.00000")
        For i = 1 To Len(Time)
            Select Case TimePos
                Case "Hours"
                    If Not Right(Left(Time, i), 1) = "." Then
                        Hours = Left(Time, i)
                    Else
                        Time = Mid(Time, Len(Hours) + 1, Len(Time) - Len(Hours))
                        Time = Format(Time * 60, "00.00000")
                        If Hours >= 12 Then
                            If Hours <> 12 Then
                                Hours = Hours - 12
                            End If
                            AMPM = "PM"
                        Else
                            If Left(Hours, 1) = 0 Then
                                Hours = Right(Left(Hours, 2), 1)
                            End If
                            AMPM = "AM"
                        End If
                        'Debug.Print Time
                        TimePos = "Minutes"
                        i = 0
                    End If
                Case "Minutes"
                    If Not Right(Left(Time, i), 1) = "." Then
                        Minutes = Format(Left(Time, i), "00")
                    Else
                        Time = Mid(Time, Len(Minutes) + 1, Len(Time) - Len(Minutes))
                        Time = Format(Time * 60, "00.00000")
                        'Debug.Print Time
                        TimePos = "Seconds"
                        i = 0
                    End If
                Case "Seconds"
                    If Not Right(Left(Time, i), 1) = "." Then
                        Seconds = Format(Left(Time, i), "00")
                    Else
                        Time = Mid(Time, Len(Seconds) + 1, Len(Time) - Len(Seconds))
                        Time = Time * 1000
                        'Debug.Print Time
                        TimePos = "MSeconds"
                        i = 0
                    End If
                Case "MSeconds"
                    MSeconds = Time
                    i = 100
                Case Else
                    MsgBox "Error in TimeConvert() Function (Select Else)"
            End Select
        Next
        
        TimeConvert = Hours & ":" & Minutes & " " & AMPM
        Debug.Print Nz(FieldName, "Time Full Format") & ": " & Hours & ":" & Minutes & ":" & Seconds & "." & MSeconds & " " & AMPM
End Function

I am trying to figure out how to reverse the process though so I can store the current time using

time & Right(Format(Timer, "0.000"), 4)

[orange]

Vita,

my database has time stamps in a weird format

Where do these time stamps originate?
Are they within your application OR
Do you receive the data from another system/source?

[Vita]

I receive them from an odbc connection to a SQL server. An ERP that our company uses stores the time like that.

I wrote the following code to encode the current time in that format. It is a bit messy but it works. (probably could have done away with a variable or two)

Public Function CurrentTime()
Dim StrTime As String, chrString As String, CurrTime As String, MSeconds As String
Dim i As Integer, ColonCount As Integer
CurrTime = CStr(Time()) '"9:59:30"
MSeconds = Right(Format(Timer, "0.000"), 4) '".084"


For i = 1 To Len(CurrTime)
    chrString = Mid(CurrTime, i, 1)
    Select Case Asc(chrString)
        Case 48 To 58
            StrTime = StrTime + chrString
        Case Else
            i = Len(CurrTime)
            CurrTime = ""
    End Select
Next


StrTime = Format(StrTime, "hh:mm:ss")
For i = 1 To Len(StrTime)
    If Mid(StrTime, i, 1) = ":" Then
        ColonCount = ColonCount + 1
    End If
Next
While ColonCount <> -1
    Select Case ColonCount
        Case 2
            CurrTime = Format(((Right(StrTime, 2) & MSeconds) / 60), ".00000")
            StrTime = Left(StrTime, Len(StrTime) - 3)
            ColonCount = ColonCount - 1
        Case 1
            CurrTime = Format(((Right(StrTime, 2) & CurrTime) / 60), ".00000")
            StrTime = Left(StrTime, Len(StrTime) - 3)
            ColonCount = ColonCount - 1
        Case 0
            CurrTime = StrTime & CurrTime
            If Left(CurrTime, 1) = 0 Then
                CurrTime = Mid(CurrTime, 2, Len(CurrTime) - 1)
            End If
            ColonCount = ColonCount - 1
    End Select
Wend
CurrentTime = CurrTime
End Function

[isladogs]

@Vita
The final step as incorrect 30.084 should remain as that as it means 84 milliseconds. .84 is 840 milliseconds

If you can do without the milliseconds, the process becomes trivial
As you are probably aware dates & times are actually stored as a double number datatype and formatted according to whatever the settings are in Windows
For example:

?CDbl(Now) 44944.7200347222 


?CDate(44944.7200347222 )
18/01/2023 17:16:51

For anyone who isn't aware, the 44944 is the number of days since 'day zero' = 30 Dec 1899
The .7200347222 is the time component expressed as a proportion of the day . So e.g. 6:00 AM = 0.25

Taking your original example:
Convert 9.99169 to a time value by dividing by 24

?9.99169/24
 0.416320416666667

Convert it back to time which on my system is formatted as e.g. hh:nn:ss

?CDate(9.99169/24)
09:59:30

[Vita]

@Vita
The final step as incorrect 30.084 should remain as that as it means 84 milliseconds. .84 is 840 milliseconds

If you can do without the milliseconds, the process becomes trivial
As you are probably aware dates & times are actually stored as a double number datatype and formatted according to whatever the settings are in Windows
For example:

?CDbl(Now) 44944.7200347222 


?CDate(44944.7200347222 )
18/01/2023 17:16:51

For anyone who isn't aware, the 44944 is the number of days since 'day zero' = 30 Dec 1899
The .7200347222 is the time component expressed as a proportion of the day . So e.g. 6:00 AM = 0.25

Taking your original example:
Convert 9.99169 to a time value by dividing by 24

?9.99169/24
 0.416320416666667

Convert it back to time which on my system is formatted as e.g. hh:nn:ss

?CDate(9.99169/24)
09:59:30

Okay thanks! I assumed the last step was unnecessary.
I was unaware that they were stored as a Double. That is useful to know thank you!
Timer but iirc it gets the current amount of seconds passed from the beginning of the day, apparently down to the milliseconds.

I don't need the milliseconds for when I am reading the time but to keep consistency I need them when writing so I might as well factor them in on the writing just for practice. (Not that milliseconds usually matter in this scenario anyways)
I wonder if there is anyway to get CDate to do milliseconds as well. Otherwise the code I wrote seems to work so no reason to fix it.
If I didn't need the milliseconds I could write something to take all the numbers after the period and multiply it by 24?


Where did you find that the epoch was 30 Dec 1899?
I was curious and and couldn't find it under the Now function syntax page. So since it says it uses the system time so I went to google system time so I could learn how that worked and it does work how you said but the wiki says the epoch time for windows is 1 Jan 1601 because of something to do with the "proleptic Gregorian calendar".
Apologies if this comes off as rude, it is not intended to be. I just found it genuinely interesting and would like to know where the 1899 comes from and why. I also learned about a thing called a leap second which I will be reading about haha. Thank you for sending me on this rabbit hole.

[June7]

Review https://learn.microsoft.com/en-us/of...-datetime-data

Do this test in Immediate Window:

?Format(0, "mm/dd/yyyy hh:nn:ss")
12/30/1899 00:00:00

[isladogs]

Other applications use a different start date. For example Excel has day zero as 31 Dec 1899 whereas for Access its 30 Dec 1899.

The Timer function does indeed count the number of seconds after midnight then resets the following midnight.
It returns values to centi second 'accuracy' though in fact it depends on system time which is updated about 60 times per second or every 0.016 seconds

There are other timers such as GetTickCount and timeGetTime but most also depend on the system timer
If you want to go further down this rabbit hole, have a look at my article Timer Comparison Tests (isladogs.co.uk)

[June7]

Get the same result with Excel VBA immediate window.

Calculating formulas in cell is another story.

Excel DATE() is different from VBA Date().

=DATE(1899,12,31)

Calculates as 12/31/3799

=DATE(1900,1,1)

Calculates as 1/1/1900

[isladogs]

Here's a few interesting articles on the subject
Access: Fixing Dates from Excel (sfmagazine.com)
Why is 1899-12-30 the zero date in Access / SQL Server instead of 12/31? - Stack Overflow
What is story behind December 30, 1899 as base date? (microsoft.com)

The story goes that we should blame the developers of Lotus-123 who didn't realise that the year 2000 was a leap year as it is divisible by 400 (unlike e.g. 1900 & 2100 which aren't leap years)