Excel 2007 : Adding AND subtracting time in Excel 2007

**CLUPICO** · 09-19-2009, 09:25 AM

My better half has asked for my assistance in constructing a table that would reflect the time differences between locations in different parts of the world (based on military time). I’ve been able to partially accomplish this by making use of Excel’s “Time” function as indicated in the example below. When I add time to the (EST) the function performs quite well. However, when the function encounters negative numbers, (time zones that run behind ours) it runs into problems. Any assistance would be greatly appreciated.
Chuck

US
(EST)
TIME Difference
(in hours) Local
Time Function
22:00 6 4:00:00 AM =A2+TIME(B2,0,0)
15:00 -1 #NUM! =A3+TIME(B3,0,0)

**daddylonglegs** · 09-19-2009, 09:33 AM

Rather than using TIME function (which doesn't accept negative values) try like this

=A3+B3/24

Although if you have A3 as 02:00 and B3 as -3 you'll still get problems because of going back to previous day....so change to this to fix that too....

=MOD(A3+B3/24,1)

In either case format the result cell in your required time format