Zulu and Julian Date Conversion

[Lynn63]

Does anyone know of a function that will convert the system time to Greenwich Mean Time (Zulu)? I am also looking for a function to convert the system date to Julian Date. Any suggestions would be greatly appreciated!

Lynn

 

[Pat Hartman]

Use the Format() function to reformat the date to Julian format - Format(YourDate,"yyddd")

To convert any date/time to GMT, you need to know the timezone's offset from GMT (There is probably a windows function that will get this for you. I'm sorry I don't know it). Then use the DateAdd() function to add the hours represented by the offset to the local time.

 

[Travis]

Julian Date:

format(#5/1/2001#,"yy") & format(format(#5/1/2001#,"y"),"000")

This makes sure of the Leading Zero on the number of days in the year.

Now the GMT/UTC/ZULU

This is a Windows API which determines the Difference between the Local (PC Time) and the GMT/UTC/ZULU. This will allow you to add this difference into you Local time.

Add the following to a New Module:

Public Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type

Public Type TIME_ZONE_INFORMATION
Bias As Long
StandardName(32) As Integer
StandardDate As SYSTEMTIME
StandardBias As Long
DaylightName(32) As Integer
DaylightDate As SYSTEMTIME
DaylightBias As Long
End Type

Public Declare Function GetTimeZoneInformation Lib "kernel32" (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long

Public Sub TestTimeZone()
'Test Sub for showing how this works
Dim TZI As TIME_ZONE_INFORMATION
Call GetTimeZoneInformation(TZI)
Debug.Print TZI.Bias / 60 & " Hours"

End Sub

 

[raskew]

Travis-

Enjoyed your the 1st part of your solution. It obviously works. I just can't understand how. Could you expound?

Re the Windows code, can't get that to work. Get massive errors at the point of:
Public Declare Function GetTimeZoneInformation Lib "kernel32" (lpTimeZoneInformation As
TIME_ZONE_INFORMATION) As Long

Have you tried copying your code directly from this forum. Did it work? Your comments are welcomed.

Thanks,

Bob

 

[Lynn63]

Travis,

Thank you so much! The J-date function worked like a charm, and the GMT conversion is amazing! I'm on the East Coast (USA) and when I run the GMT module it tells me that GMT is 5 hours from my current time, which indeed it is. Thank you again for your help.

Lynn

 

[Travis]

Bob,

It works this way:

1. format(#5/1/2001#,"yy") Gets the Two digit equivalant of the Current Year (2000 = 00, 2001 = 01 etc.)

2. format(format(#5/1/2001#,"y"),"000") Gets the Number of days in the Year, but since we need to have a leading zero since the year is known to be 365 days (we wont go into the half days or back on the 360 days that banks use

) you can tell it to format it to three digits and include leading Zeros.

On the GETTIMEZONEINFORMATION. Yes I tested this coping it back. Lynn seems to have been successful so look at these possiblities.

1. Did you copy Both "TYPES" and the API Function to a new module? These need to be at the Top or the "Declaration Section" of a module. Public Sub TestTimeZone() is the test function. You will notice that I declare one of the Types locally as you cannot use a type directly but need declare it seperatly.

2. What Windows version do you have and what service pack is it up to?