Results 1 to 5 of 5

Thread: Displayin Date/Time in "original" time zone

  1. #1
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14

    Displayin Date/Time in "original" time zone

    I have date/times that were recorded in another time zone - So I want to be able to read what time zone the data were recorded in - next I want to display the date/time in the "original" date/time or local time. In other words - the data was recorded in Germany and I open the sheet in New York - I want the date/time to be shown in German time and/or New York time - is there a property I can set to control how the date/time is displayed.

    I want to do this via VBA code
    Last edited by Rasm; 04-15-2012 at 07:26 AM.
    xl2007 - Windows 7
    xl hates the 255 number

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    While both New York and all of Germany observe Daylight Savings Time, the start and stop dates are different in each location... how did you want to handle that? Also, do you have a cell that tells which location the times are to be considered in? If so, what sheet/cell is that in? By the way, in case it will matter, what city or cities in Germany are you using?

  3. #3
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14
    Rick
    I get data from all over - the records(rows) are date/time stamped from a process (production) - so when I report back my findings - I want to lock in the date/time stamp to what ever time zone the data was collected in. So regardless of what part of the world the sheet is viewed - the date/time stamp is locked and displayed as it was at the site where the data was originally collected. I hope this makes sense. Currently I add a ' in front of the date/time and then format it as a text field - But that way I have to use a format of YYYYMMDD hh:mm:ss - otherwise I cannot sort the date by time.
    I hope this makes it clear what I am trying to do. So In a perfect worl I want the date/time to be displayed in using the time zone where it was collected - but formatted to the PC that is opening the sheet. I.e. the Europeans use military time and not AM/PM
    xl2007 - Windows 7
    xl hates the 255 number

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Sorry about the delay getting back to you... I lost track of this thread.

    There is information about getting timezone information here...

    Untitled Page

    I am guessing you would want to put this in a cell next to the time when the time is entered? If so, then you would want to do that using event code.

  5. #5
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14
    Thanks Rick - yeah I was afraid I had to do a lot coding - but that is very good info - much appreciated
    xl2007 - Windows 7
    xl hates the 255 number

Similar Threads

  1. VBA Versions of my "Get Field" and "Get Reverse Field" formulas
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 4
    Last Post: 06-02-2017, 06:15 PM
  2. Reversing a "First Middle Last" Name to "Last, First Middle" Name Format
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 5
    Last Post: 01-06-2014, 10:04 PM
  3. Replies: 5
    Last Post: 04-18-2013, 02:30 AM
  4. Follow-up to "Excel Number Format: Indian Style Comma Separation"
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 2
    Last Post: 04-14-2012, 10:46 PM
  5. Ordinal Suffix (i.e., "st", "nd", "rd" and "th")
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 0
    Last Post: 03-20-2012, 03:46 AM

Posting Permissions

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