Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Dec 2002
    Location
    Dumfries, Virginia, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change dates from one time zone to another (Excel 2000)

    All,

    I have a problem I need some assistance on. I have dates entered from a text report I've imported to an Excel spreadsheet. I need to , first change these dates to the format Excel understands, and then subtract 5 hours from the time. The time base used was Prime Meridian timezone, 5 hours before the East coast of the US. The change in hours can move the date back one day.

    Thanks for your help.

    Jim Turner

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Change dates from one time zone to another (Excel 2000)

    Hi Jim,

    Without knowing how your data is formatted, I'll assume you're working with a text string that represents both the date and time, or with a pair of text strings representing these.

    The simplest way to convert a text string to a date is with the DATEVALUE formula. Assuming your date is in A1,
    =DATEVALUE(A1)
    in another cell will convert it to the number that Excel uses to represent that date. Any time values in the same field will be ignored.

    Similarly, the simplest way to convert a text string to a time is with the TIMEVALUE formula. Again, assuming your time is in A1,
    =TIMEVALUE(A1)
    which will convert it to the number that Excel uses to represent that time of day. Any date values in the same field will be ignored.

    So, to get both the date and time as a single value in Excel, use:
    =DATEVALUE(A1)+TIMEVALUE(A1)

    Then, to subtract 5 hours, use:
    =DATEVALUE(A1)+TIMEVALUE(A1)-5/24

    Now you'll want to see the result as a new date & time. To do that, use a custom cell format, like:
    dd/mmm/yyyy hh:mm:ss

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Lounger
    Join Date
    Dec 2002
    Location
    Dumfries, Virginia, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change dates from one time zone to another (Excel 2000)

    Thanks for your suggestion, I meant to include a sample of the data but got too anxious about looking for help. An example of the data I'm working with is : 11/Oct/2002:03:49:15 This is all in one cell. I've tried converting this to date and time, however the seconds are throwing me off, I think. I tried the Datevalue and Timevalue you suggested, but it was returned not understanding the value.

    Jim <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change dates from one time zone to another (Excel 2000)

    Try the following horrible formula (assuming the data is in cell A1):

    =DATEVALUE(LEFT(A1,FIND(":",A1)-1))+TIMEVALUE(RIGHT(A1,8))-5/24

    As macropod remarked, you will have set the number format of the result to dd/mmm/yyyy hh:mm:ss

    Explanation:
    FIND(":",A1) returns the position of the first colon in A1.
    LEFT(A1, FIND(":",A1)-1) returns the part of A1 to the left of this, i.e. the date part.
    DATEVALUE turns this into a number Excel can interpret as a date (the number of days since 31 December 1899).
    RIGHT(A1,8) returns the last 8 characters of A1, i.e. the time part.
    TIMEVALUE turns this into a number Excel can interpret as time (as a fraction of 1 day).
    The date and time parts are added together and subtracts 5/24 (part of a day, correpsonding to 5 hours).

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Change dates from one time zone to another (Excel 2000)

    Hi Jim,

    The colon immediately after the year was throwing my solution off. Hans' method for handling it should give you the answer you need.

    Alternatively, if you do a search for the string "2002:" and replace it with "2002 " (omit the quotes for both), I think you'll find that Excel will automatically convert your date/time strings into its own date/time values. Then you only need to subtract 5/24 to calculate the revised result you're after.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    New Lounger
    Join Date
    Dec 2002
    Location
    Dumfries, Virginia, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change dates from one time zone to another (Excel 2000)

    Thanks for your help macropod and HansV, the information you sent me worked like a charm. I'm a first time "lounger" but I see I'll be back many times now.

    Regards.

Posting Permissions

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