Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtract hours
I would like to subtract hours, not time of day and have it end up in h:mm
format. currently I am subtracting a cell with hours in h:mm format from a cell in number (0 decimal places) format with the following formula: =((HOUR(E18)*60)+MINUTE(E18)-($I$8*60))/60 As an example the cell E18 is 30 and cell I8 is 6:45 and my answer is -23.75. Can I get this cell to format to -23:45 ? I am calculating Comp time on a timesheet, so the number can be positive or negative. Thanks, Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtract hours
Hi!
Not sure what you're doing, but......... =((HOUR(E18)*60)+MINUTE(E18)-($I$8*60))/60 As an example the cell E18 is 30 and cell I8 is 6:45 and my answer is -23.75. Using the above, I get a result of -0.28125. The Hour and Minute functions need time serial numbers as arguments. That is, a fractional number from 0 to 1. Since there is no Hour 30 or Minute 30 that portion of the formula evaluates to 0. So you're getting this: 0-($I$8*60)/60 The only way to calculate negative time and have it formatted as TIME is to use the 1904 date system but this can lead to more problems than it's worth. Any current dates in your file will be off by 4 years! You could display negative times as a TEXT representation. That would mean that calculations on those values will be *much* more complicated! You could do something like this: Assume your formula returns -23.75: =IF(your_formula<0,"-"&TEXT(ABS(your_formula)/24,"[h]:mm"),your_formula) Format the cell as Custom: [h]:mm Biff "Steve@Blackhawk" wrote in message ... I would like to subtract hours, not time of day and have it end up in h:mm format. currently I am subtracting a cell with hours in h:mm format from a cell in number (0 decimal places) format with the following formula: =((HOUR(E18)*60)+MINUTE(E18)-($I$8*60))/60 As an example the cell E18 is 30 and cell I8 is 6:45 and my answer is -23.75. Can I get this cell to format to -23:45 ? I am calculating Comp time on a timesheet, so the number can be positive or negative. Thanks, Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding rows of hours and minutes to get a total | Excel Worksheet Functions | |||
Subtract Hours | Excel Worksheet Functions | |||
excel [HHHHH]:mm add subtract hours mins inc lnegative | Excel Worksheet Functions | |||
calculate hours just can't figure it out | Excel Worksheet Functions | |||
Subtracting paid hours from unpaid hours | Excel Worksheet Functions |