Subtracting 2 hours from a time....
 

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?

Subtracting 2 hours from a time....
 

Try


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

--


Regards,


Peo Sjoblom

"Literalgar" wrote in message
...
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?

Subtracting 2 hours from a time....
 

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)

--
John C


"Literalgar" wrote:

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?