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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 06:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com