Write negative hours in Excel cell

Copper Contributor

Hi, when I try to insert a negative time in an Excel cell, for example -10:12, Excel interprets the input as the selection of lines 10-12, and not as a time. Can you help me solve this problem?

9 Replies
Excel cannot work with negative times unless you change the file's date system to 1904. But that has quite some negative implications. Better to just enter positive times and negative times in a separate column so you can act accordingly by adding values in the pos column and subtracting in the neg column.

@Jan Karel Pieterse .., what are he negative implications of changing the file data system to 1904?

If you copy information from a workbook with that box checked to a workbook without the box checked, any date values will be off by almost four years!!!!!
try time-1

@Inalmin 

Has the cell been correctly formatted with user defined formatting ([hh]: mm)?

Also remember that the time must be entered here with a colon

... and (that is actually the whole point) summarize the minus time in quotation marks and closing characters:  =-"10:12".

Sometimes it can happen that Excel only displays pound signs «########» in cells that contain minus times.

It will work with the input if you enable the 1904 option in your Excel.

 

To be able to expect negative times, proceed as follows:

1. Call up the "Options" entry in the "File" tab.

2. Select the "Advanced" entry in the left-hand area.

3 In the right part of the window, scroll all the way down.

4. Select the Use 1904 Date Values check box.

5. Confirm with OK.

 

* Subjects enabling 1904 dates. However, you should not do this unless you are aware of the possible consequences.

Date systems in Excel

 

Hope I was able to help you with this info.

 

NikolinoDE

I know I don't know anything (Socrates)

 

Thanks to all

@Yea_So actually this simple formula works well, when displaying negative time. Its value can also be used in formulas.

 

I used a combination of the 1904 system date (under Options...), changing the cell format to [hh]:mm (so that I can calculate 40 hours of labor, etc), and using time.

 

I have an excel sheet with work hours, vacation hours, etc. 

'-10:12?

@Xaraox_ sure! As long as you're not using that said data to add/subtract.

 

E.G. employee John used 1 hour for Personal.

How would I, then delete from his Personal Pool?

the '-10:12 that you suggested wouldn't allow. Now, =-Time(10,12,0) would. 

So would changing the format to [hh]:mm.

So would, in a way (calculating between two (or more) cells containing times) using the 1904 Date system.

 

I'm new to this, so just trying to help fellow Excel users.

 

Cheers!