Subtracting 2 hours from a time....


L

Literalgar

I am creating a spreadsheet with a tab for different time zones... to
convert from the eastern time sheet where the times are manually entered, in
the mountain time spreadsheet, I am using the formula:
=IF('Eastern Time'!C3="","",('Eastern Time'!C3)-(TIME(2,,)))
The IF is just to show blanks instead of 12:00 for blanks on the Eastern
time page

This works fine, unless the eastern time is between midnight and 2:00 AM,
when I get a result of ############ in the mountain time. How can I get
around this?
 
Ad

Advertisements

P

Peo Sjoblom

Try


=IF('Eastern Time'!C3="","",MOD('Eastern Time'!C3-TIME(2,,),1))

--


Regards,


Peo Sjoblom
 
Ad

Advertisements

J

John C

It happens because of the 1900 date system. If you click on Tools|Options,
Calculate tab, check the 1904 date system checkbox, and you should be good to
go. (Re-enter your times though)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top