Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time difference from 24 hours
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time difference from 24 hours
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 -- Regards Roger Govier "The Incredible" <The wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time difference from 24 hours
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 "The Incredible" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time difference from 24 hours
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)) -- David Biddulph "Mike H" wrote in message ... 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 "The Incredible" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How toFind the Time difference in hours | Excel Worksheet Functions | |||
Difference of time in hours or minutes | Excel Discussion (Misc queries) | |||
HOURS DIFFERENCE IN TIME BETWEEN DIFFERENT DATES AND TIMES | Excel Worksheet Functions | |||
Calculate difference in time spanning a day, during office hours o | Excel Discussion (Misc queries) | |||
calculate difference in time to hours | Excel Worksheet Functions |