cumulative time in excel

[Guest]

I am trying to get a function that will add time in the format of
hours:minutes. Each cell is the difference between actual time and forecast
time. Therefore sometimes I have a negative value. I cannot add the values if
I have a negative time cell.

example:
C1 is the difference between A1 and B1, lets say it equals :40 (40 minutes)
C2 is the difference between A2 and B2, lets say it equals :37 (37 minutes
C3 is the difference between A3 and B3, lets say it equals -:07 (7 minutes
under forecast)
The totals for column C should equal 1:10 (1 hour 10 minutes) but it doesn't
allow a negative time value.

Any ideas how I can make this work and how I should format the cells????

 

[Guest]

tools==>options==>Calculations==>1904 date system

This will allow negative times.

But beware dates are offset by 4 years.

 

[Peo Sjoblom]

If you want to use time format you can either change to 1904 system under
tools>options>calculation
however it will change dates in your system by adding 1462 days to any date
or you could use something like

=IF(A1>B1,ABS(B1-A1),B1-A1)

copy down

then to sum use

=SUMPRODUCT(--(B1:B30>A1:A30),C1:C30)-SUMPRODUCT(--(A1:A30>B1:B30),C1:C30)

will fail if the total is negative

you can also convert the time values to decimals and use them then convert
back to time but that won't help you if the total is negative

 

[Guest]

Since the actual date is a non-issue, this works great.

Thanks

 

[Guest]

Got it working.

Thanks

If you want to use time format you can either change to 1904 system under
tools>options>calculation
however it will change dates in your system by adding 1462 days to any date
or you could use something like

=IF(A1>B1,ABS(B1-A1),B1-A1)

copy down

then to sum use

=SUMPRODUCT(--(B1:B30>A1:A30),C1:C30)-SUMPRODUCT(--(A1:A30>B1:B30),C1:C30)

will fail if the total is negative

you can also convert the time values to decimals and use them then convert
back to time but that won't help you if the total is negative


--

Regards,

Peo Sjoblom