Results 1 to 6 of 6

20021202, 23:24 #1
 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

20021203, 00:03 #2
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,005
 Thanks
 2
 Thanked 406 Times in 335 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
CheersCheers,
Paul Edstein
[MS MVP  Word]

20021203, 12:00 #3
 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>

20021203, 12:21 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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).

20021203, 20:27 #5
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,005
 Thanks
 2
 Thanked 406 Times in 335 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.
CheersCheers,
Paul Edstein
[MS MVP  Word]

20021204, 12:09 #6
 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.