Excel datetime formatting UTC

timberadmin
timberadmin used Ask the Experts™
on
Hi Experts,

In VBA, I am having trouble formatting a date/time into this UTC format like this: 2018-03-11T00:01:00Z

Please help...
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
IT / Software Engineering Consultant
Top Expert 2016
Commented:
How about just:

s = Format(dt, "yyyy-mm-ddThh:mm:ssZ")

Open in new window


»bp
Shaun VermaakTechnical Specialist
Awarded 2017
Distinguished Expert 2018

Commented:
You cannot just reformat it, it is a date offset
Set dateTime = CreateObject("WbemScripting.SWbemDateTime")    
dateTime.SetVarDate (now())
wscript.echo  "Local Time:  " & dateTime
wscript.echo  "UTC Time: " & dateTime.GetVarDate (false)

Open in new window

https://stackoverflow.com/questions/15887700/utc-time-assignment-in-vbscript
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
Shaun,

That's not really true.  There are different formats for "UTC" time, see below link, specifically:

1994-11-05T08:15:30-05:00 corresponds to November 5, 1994, 8:15:30 am, US Eastern Standard Time.
1994-11-05T13:15:30Z corresponds to the same instant.
Date and Time Formats

This question didn't ask for info on calculating certain UTC values, rather just formatting a datetime value that had already been established...


»bp

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial