Time difference from 24 hours

[The Incredible]

Hi
i would like to add few time durations & want to substract the sum of those
from whole day time that is 24 hours.

Some thing like i would like to add
5:10
6:18
1:13
9:08

want to sum up these & want to have the difference from 24 hours.

what format, formula should i use & how to implement in the worksheet... plz
let me know.

Thanks for the help & appreciatiate the cooperation.

Sounak

 

[Roger Govier]

Hi

Excel stores times as fractions of a day hence 1 represents 24 hours
=1-SUM(A1:A4)
will give your result

Ensure the cell with the formula is formatted as time hh:mm

 

[Mike H]

Hi,

Put the times in A1:a4 then this formula

=24-SUM(A1:A4)

Note if the sum of the times goes over 24 this goes wrong.

Mike

 

[David Biddulph]

Note that you are looking at the difference from 24 days, not from 24 hours.
Try =1-SUM(A1:A4) instead.
If you are worried about the time going negative when beyond 24 hours, try
Tools/ Options/ Calculation, and select 1904 date system, but be careful if
you are transferring dates between 1900 and 1904 systems.
If you want to stay in the 1900 system & get a text output for negative
values, try
=IF(1-SUM(A1:A4)<0,"-"&TEXT(SUM(A1:A4)-1,"hh:mm"),1-SUM(A1:A4))