FileDateTime to UTC

[breynolds0431]

Hello. I have a main file that updates data from an external file on Workbook_Open and Workbook_BeforeSave. When it updates, it writes the date/time to a named range in the sheet of the main file (not the external file). I then use that update's date and time to evaluate if an update should be made when the main file is opened/closed using the FileDateTime() of the external file. That is, if the date/time on the sheet in the main file is less than the date/time of the FileDateTime(ExternalFile), then the update should run.

The problem comes to play when someone from another time zone opens the main file. I know how to convert local time to UTC, which could be added during the update to the main file's sheet. But, I think I would also need a way to convert the FileDateTime(ExternalFile) to UTC as well. Otherwise, there would likely be a couple hours not being considered. Anyone know of a way to convert FileDateTime() to UTC?

Private Sub Workbook_Open()

'Will only run Hub Data update if the Hub modify date/time changed
Dim specs As Range: Set specs = ThisWorkbook.Sheets("UpdateSpecs").range("HubDtTm")
Dim FilePath As String: FilePath = "T:\FSD\AR\Hub Log\"
Dim MyFile As Variant: MyFile = Dir$(FilePath & "*.xlsb")

'specs is already UTC converted
'>>this is where (I'm thinking), FileDateTime would need to be converted to UTC
If specs < FileDateTime(FilePath & MyFile) Then
     Call UpdateHubData
End If

With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub

 

[John_w]

Try the code solution at:

Convert UTC time to local

I have an MS Access app which is used in two different time zones. The difference is 7 hours. I needed to find a time when the both of the offices are off so I can close their database and I can do

stackoverflow.com

and call the UTCtoLocal function from your code like this:

If specs < UTCtoLocal(FileDateTime(FilePath & MyFile)) Then

I would also change the specs variable to an Excel date, like this:

Dim specs As Date: Set specs = ThisWorkbook.Sheets("UpdateSpecs").range("HubDtTm").Value

 

[breynolds0431]

Try the code solution at:

Convert UTC time to local

I have an MS Access app which is used in two different time zones. The difference is 7 hours. I needed to find a time when the both of the offices are off so I can close their database and I can do

stackoverflow.com

and call the UTCtoLocal function from your code like this:

If specs < UTCtoLocal(FileDateTime(FilePath & MyFile)) Then

I would also change the specs variable to an Excel date, like this:

Dim specs As Date: Set specs = ThisWorkbook.Sheets("UpdateSpecs").range("HubDtTm").Value

Thanks! I used the LocaltoUTC so that both dates were being updated to UTC. Appreciate the recommendation on the Date variable for specs as well.

 

[John_w]

Thanks! I used the LocaltoUTC so that both dates were being updated to UTC.

Correct! Yes, call the LocaltoUTC function, not UTCtoLocal.