Deducting hours to show time less than 00:00

[CairoBabe]

I have a spreadsheet for an event that shows what is happening in a base time (UK) and the time in different time-zones, calculated from the base time and an offset, which varies from +11.00 to -08.00. The formula I am using is in the form =IF(ISBLANK($D54),"",$D54-G$3), where D54 is the base time and G3 is the offset.

The formulae works fine for most values. But when the result is less than 00:00 it doesn't work. For example, if the base time is between 01:00 and 07:00, it cannot display the time for the timezone with a -08:00 offset. So if the base time is 01:00, I want it to display 17:00 but all I see is *********. I'm not sure if it is a formula issue or a display format issue. Can anyone suggest a way around this please?

 

[rollis13]

You could try using 1904 date system:
from menu Options / Advanced / in section 'When calculating this workbook" check the "Use 1904 date system"
But since you are using Time Zones and Dates please check well if this option gives you other issues.

 

[CairoBabe]

You could try using 1904 date system:
from menu Options / Advanced / in section 'When calculating this workbook" check the "Use 1904 date system"
But since you are using Time Zones and Dates please check well if this option gives you other issues.

Thank you. I tried that but it gave negative times. I think I have managed to find a solution by including the date in the base cell, although formatting it to only show the time. That seems to work for showing the time in the other zones.

 

[rollis13]

Thanks for the feedback. Glad you found an alternative solution to your problem.