adding/subtracting hours from times and dates

[keke]

I have a spreadsheet that lists local times for future events all over the world in the following format: yymmddhhmmss. An example would be an event in Australia March 3 at 14:00 local time: 020303140000. When I look at the time in the formula field above the spreadsheet the time is formatted as "03/03/2002 02:00:00 PM" but it appears as I have noted above in the spreadsheet itself. I need to subtract or add hours to get to UTC (eg. Australia is +11 UTC), and then add/subtract more hours to get to my local time (-8 UTC in this case).

This is probably simple but I can't get it to work. Any help is welcome!!

keke

 

[Mark W.]

Excel store date/time values as days (and fractional parts thereof). -8/24 subtracts 8 hours; +11/24 adds 11 hours.

 

[keke]

On 2002-02-25 09:33, Mark W. wrote:
Excel store date/time values as days (and fractional parts thereof). -8/24 subtracts 8 hours; +11/24 adds 11 hours.

So how would that work? Where would I add and subtract? Would it be =(B1-(8/24)) if B1 is the date+time 020303140000 ?

keke

 

[Mark W.]

On 2002-02-25 09:51, keke wrote:

On 2002-02-25 09:33, Mark W. wrote:
Excel store date/time values as days (and fractional parts thereof). -8/24 subtracts 8 hours; +11/24 adds 11 hours.

So how would that work? Where would I add and subtract? Would it be =(B1-(8/24)) if B1 is the date+time 020303140000 ?

keke

Yes, and the parentheses aren't necessary (=B1-8/24 will suffice) because Excel will evaluate the division operator before the minus operator.
This message was edited by Mark W. on 2002-02-25 09:55