Julian Time calculation

[Juggler_IN]

I am trying to estimate the Julian value to include the time component. I am attaching a UDF for Julian count and a potential VB function converted to VBA but I am not getting the results.

For example, for 16th July 2020 at 00:00:00 the expected Julian value is 2459046.5. My function "Jdt" is giving this value.
Reference link:Julian value

But, for example, for 16th July 2020 at 17:30:00 the expected Julian value is 2459047.22917. This is not getting populated. I used the VB function at:VB function

Public Function Jdt(ByVal sDate As Date) As String

    Dim p, q, r, s, t, i%, j%, k%

    i = Day(sDate)
    ' Debug.Print i
    j = Month(sDate)
    ' Debug.Print j
    k = Year(sDate)
    ' Debug.Print k
    p = Int(k / 100&)
    ' Debug.Print p
    q = Int(p / 4&)
    ' Debug.Print q
    r = 2& - p + q
    ' Debug.Print r
    s = Int(365.25 * (k + 4716&))
    ' Debug.Print s
    t = Int(30.6001 * (j + 1&))
    ' Debug.Print t

    Jdt = CStr(r + i + s + t - 1524.5)

End Function

Function Jtm(ByVal sDate As Double) As String
' Julian time is calculated as a fraction of a 24-hour period, starting from noon. So a value >= 0.5 is a morning hour on the next day.

    ' Get the fractional part of the Julian datetime, which is the time
    Dim dJTime As Double
    dJTime = GetFractionalPart(sDate)
 
    ' Get time in seconds
    Dim dJTimeInSec As Double
    dJTimeInSec = 86400 * dJTime + 43200    ' 43200 is half a day in seconds
 
    ' Calculate the hours
    Dim dJTimeInHours As Double
    dJTimeInHours = dJTimeInSec / 3600
 
    ' If the value is >= 24, it's a morning hour so increment the day value
    Dim intDay As Integer
    intDay = 0
    If dJTimeInHours >= 24 Then
        dJTimeInHours = dJTimeInHours - 24
        intDay = 1
    End If
 
    ' Calculate the minutes
    Dim dJTimeInHoursMin As Double
    dJTimeInHoursMin = GetFractionalPart(dJTimeInHours) * 60
 
    ' Calculate the seconds
    Dim dJTimeInHoursSec As Double
    dJTimeInHoursSec = GetFractionalPart(dJTimeInHoursMin) * 60
 
    ' Calculate the milliseconds
    Dim dJTimeInHoursMSec As Double
    dJTimeInHoursMSec = GetFractionalPart(dJTimeInHoursSec) * 1000
 
    ' Build the result and display it

    '    Dim tsSpan As New TimeSpan(intDay, CInt(Math.Truncate(dJTimeInHours)),
    '                               CInt(Math.Truncate(dJTimeInHoursMin)),
    '                               CInt(Math.Truncate(dJTimeInHoursSec)),
    '                               CInt(Math.Truncate(dJTimeInHoursMSec)))
    '    Return tsSpan.ToString("d\:hh\:mm\:ss\.fff")

    Jtm = TimeSerial(CInt(Trunc(dJTimeInHours)), _
                    CInt(Trunc(dJTimeInHoursMin)), _
                    CInt(Trunc(dJTimeInHoursSec)))

End Function

''' <summary>
''' Given a decimal, gets the fractional part only. For example, for 1234.5678, returns 0.5678
''' </summary>
''' <param name="dNumber">The decimal.</param>
''' <returns>The fractional part of the decimal.</returns>
Function GetFractionalPart(ByVal dNumber As Double) As Double
    Dim dInt As Double
    dInt = Trunc(dNumber)
    GetFractionalPart = (dNumber - dInt)
End Function

Any recommendations?

 

[mole999]

some info here > Converting Excel date to Julian day-of-year number « Inane Coding