fomatting cells to hrs . mins

R

reteps

I have created an annual leave chart in excell 2000 the cells are formatted in Custom to (H): MM How do I format a cell to show minus hrs. when the hrs go under the hrs allocated I just get a row of #######################
 
N

Norman Harker

Hi "reteps"

For negative hours you need to use the 1904 Date System:

Tools > Options > Calculation Tab
Check 1904 Date System

This applies to the entire workbook and you should take care not to
mix and match the two systems.

To enter a negative time use:

=-"3:45"


Incidentally, to aggregate hours we use custom format [h]:mm not
(h):mm

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
reteps said:
I have created an annual leave chart in excell 2000 the cells are
Click to expand...
formatted in Custom to (H): MM How do I format a cell to show minus
hrs. when the hrs go under the hrs allocated I just get a row of
#######################
 
M

Mark Graesser

Negative time vaules don't work in the 1900 date system.

You can change to the 1904 date system under Tools>Options>Calculation and Excel will handle negative times. However this can cause date compatability problems with other spreadsheets.

Another option is to use the following formula:

=IF(A1-B1<0,"-"&TEXT(ABS(A1-B1),"[h]:mm"),A1-B1)

This will display the negative time but it won't be a true number, it will be text. If this value is used in another formula you will need to replace this cell reference with the actual subtraction formula in that other formula.

Good Luck,
Mark Graesser
[email protected]

----- reteps wrote: -----

I have created an annual leave chart in excell 2000 the cells are formatted in Custom to (H): MM How do I format a cell to show minus hrs. when the hrs go under the hrs allocated I just get a row of #######################
 
Top