Time Zone Conversion Formula AM to PM or Vice Versa

[KuraiChikara]

Hello, Im doing a formula for Eastern Time to Mountain time, the refernced times are just in different cells IE:

B1 has ET 18:00
C1 has the conversion difference 2:00
D1 has the formula for MT: =B1-$C$1 and it works

However, when I get from AM ET to PM MT I have issues.

If B1 is 1am 01:00 the calculation gives me number signs
#############

Instead of showing be 11pm (23:00)

 

[SpillerBD]

Time does not work with negative results.
If you were to start with a date and time and then subtract 1 hour you would be fine.

 

[Rick Rothstein]

Hello, Im doing a formula for Eastern Time to Mountain time, the refernced times are just in different cells IE:

B1 has ET 18:00
C1 has the conversion difference 2:00
D1 has the formula for MT: =B1-$C$1 and it works

However, when I get from AM ET to PM MT I have issues.

If B1 is 1am 01:00 the calculation gives me number signs
#############

Instead of showing be 11pm (23:00)

Try using this formula instead...

=24+B3-$C$1