Write negative hours in Excel cell

%3CLINGO-SUB%20id%3D%22lingo-sub-330801%22%20slang%3D%22en-US%22%3EWrite%20negative%20hours%20in%20Excel%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-330801%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20when%20I%20try%20to%20insert%20a%20negative%20time%20in%20an%20Excel%20cell%2C%20for%20example%20-10%3A12%2C%20Excel%20interprets%20the%20input%20as%20the%20selection%20of%20lines%2010-12%2C%20and%20not%20as%20a%20time.%20Can%20you%20help%20me%20solve%20this%20problem%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-330801%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2953295%22%20slang%3D%22en-US%22%3ERe%3A%20Write%20negative%20hours%20in%20Excel%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2953295%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B..%2C%20what%20are%20he%20negative%20implications%20of%20changing%20the%20file%20data%20system%20to%201904%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-330822%22%20slang%3D%22en-US%22%3ERe%3A%20Write%20negative%20hours%20in%20Excel%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-330822%22%20slang%3D%22en-US%22%3EExcel%20cannot%20work%20with%20negative%20times%20unless%20you%20change%20the%20file's%20date%20system%20to%201904.%20But%20that%20has%20quite%20some%20negative%20implications.%20Better%20to%20just%20enter%20positive%20times%20and%20negative%20times%20in%20a%20separate%20column%20so%20you%20can%20act%20accordingly%20by%20adding%20values%20in%20the%20pos%20column%20and%20subtracting%20in%20the%20neg%20column.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2953920%22%20slang%3D%22en-US%22%3ERe%3A%20Write%20negative%20hours%20in%20Excel%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2953920%22%20slang%3D%22en-US%22%3EIf%20you%20copy%20information%20from%20a%20workbook%20with%20that%20box%20checked%20to%20a%20workbook%20without%20the%20box%20checked%2C%20any%20date%20values%20will%20be%20off%20by%20almost%20four%20years!!!!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2957743%22%20slang%3D%22en-US%22%3ERe%3A%20Write%20negative%20hours%20in%20Excel%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2957743%22%20slang%3D%22en-US%22%3Etry%20time-1%3C%2FLINGO-BODY%3E
New 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?

6 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