+ Reply to Thread
Results 1 to 8 of 8

Timestamp with Time zone

  1. #1
    Registered User
    Join Date
    05-02-2019
    Location
    Guatemala
    MS-Off Ver
    365
    Posts
    3

    Timestamp with Time zone

    Hi,
    So, i'm working on a file that is used in diferent regions, and it gets updated with a macro. once the macro finishes, it adds a timestamp with the Now function to know when was the last time the file was updated. however, i need to add the time zone next to the date and time it was updated. is there a way to add that with VBA?

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: Timestamp with Time zone

    See http://www.cpearson.com/excel/TimeZo...lightTime.aspx

    Rather that add the time zone in a separate column, though, I'd do the conversion to Zulu, and format as, for example, yyyy-mm-dd hh:mmZ to make it clear.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,163

    Re: Timestamp with Time zone

    See this link at Mr Excel

    https://www.mrexcel.com/forum/excel-...time-zone.html

    It seems to work as far as I can see.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    5,871

    Re: Timestamp with Time zone

    .
    Richard:

    Took a look at the link you provided. The macros run here produce EST instead of DST. I'm not able to completely follow the code ... Shouldn't it display EDST or something similar ? Or is
    the required code just not there ?

    Apologies for asking .. hope you all don't mind.

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,163

    Re: Timestamp with Time zone

    Quote Originally Posted by Logit View Post
    .
    Richard:

    Took a look at the link you provided. The macros run here produce EST instead of DST. I'm not able to completely follow the code ... Shouldn't it display EDST or something similar ? Or is
    the required code just not there ?

    Apologies for asking .. hope you all don't mind.
    Must admit I'm not familiar with the code or have studied it any further. However I note the comment
    "However, if
    ' your Option Base directive is set to 0 or you have no
    ' Option Base diretive, the code won't work. Instead,
    ' change the (32)

    What happens if you set an Option Base as zero and then use
    Please Login or Register  to view this content.
    in the Private Type TIME_ZONE_INFORMATION block?

  6. #6
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    5,871

    Re: Timestamp with Time zone

    .
    No change here.

  7. #7
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,663

    Re: Timestamp with Time zone

    Sample Sub uses TZI.StandardName, change it to TZI.DaylightName
    Please Login or Register  to view this content.
    Also, for 64bit compatibility...
    Please Login or Register  to view this content.
    Last edited by CK76; 05-22-2019 at 04:15 PM. Reason: For clarity, and added compatibility part.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  8. #8
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,663

    Re: Timestamp with Time zone

    Oh if you want to store datetime offset (i.e. like datetimeoffset type for T-SQL) you could do something like below instead of using timezone function.
    I use variant of this when I need to prepare export from Excel into T-SQL (SQL server).

    Please Login or Register  to view this content.
    Or simply use UTCNow() to calculate hour difference and store that in separate column (UTCOffset).

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to convert from indian time zone to US time zone
    By prabhuduraraj09 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-18-2018, 05:42 AM
  2. Input: Time Zone. Output: Current Time
    By imsteve123 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-09-2015, 09:16 PM
  3. Need Help with an user form that convert GTM Time to Other selected Time Zone
    By odoualex in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-08-2014, 11:52 AM
  4. [SOLVED] Converting Time Values from One Time Zone to Another
    By Debraj Roy in forum Excel General
    Replies: 7
    Last Post: 09-03-2013, 06:49 AM
  5. Date and time conversion to another time zone
    By RUBEN PARRAS in forum Excel General
    Replies: 1
    Last Post: 05-21-2012, 09:03 PM
  6. Time Zone etc
    By lsmft in forum Excel General
    Replies: 2
    Last Post: 07-12-2007, 06:50 PM
  7. Converting Time Values from One Time Zone to Another
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 08-23-2005, 12:12 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1