fomatting cells to hrs . mins

[Guest]

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 #######################

 

[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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

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
#######################

 

[Nick Hodge]

In it's default set up XL cannot work with negative time.

See

http://www.cpearson.com/excel/datetime.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Guest]

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
(e-mail address removed)

----- 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 #######################