Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
subtract hours, not time of day
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.programming
|
|||
|
|||
subtract hours, not time of day
=IF(E18I8,"-","")&TEXT(ABS(I8-E18/24),"[h]:mm")
-- HTH RP (remove nothere from the email address if mailing direct) "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 | |||
Subtract 12 hours from date/time | Excel Discussion (Misc queries) | |||
using if statement to subtract 24 hours from time still shows as a negative time from both responses under 24 hours | Excel Worksheet Functions | |||
using if statement to subtract 24 hours from time | Excel Worksheet Functions | |||
Subtract hours from a time and get the correct time | Excel Worksheet Functions | |||
Time/Date subtract 24 hours | Excel Discussion (Misc queries) |