Search code examples
exceltimesubtractionnegative-number

Excel to accept negative Time

How can I modify an Excel cell to accept minus time such as (12:30- in the format hours:minutes) , I want this so as to insert Employee monthly exceeded, expected hours (e.g. 00:30+ for July) or below the monthly expected hours (e.g. 01:33- for June).

Solution

  • Option #1

    You could use this function:

    =IF(A1-A2<0,TEXT(ABS(A1-A2),"[hh]:mm-"),TEXT(A1-A2,"[hh]:mm"))

    The result would look like:
    difference negative time

    Option #2

    Microsoft says that you could do a switch to the 1904 Date System:

    In Microsoft Office Excel 2003 and in earlier versions of Microsoft Excel, follow these steps:
    1. Open, or switch to, the workbook.
    2. On the Tools menu, click Options, and then click the Calculation tab.
    3. Click to select the 1904 Date System check box.
    4. Click OK.

    In Microsoft Office Excel 2007, follow these steps:
    1. Click the Microsoft Office Button, and then click Excel Options.
    2. Click the Advanced category.
    3. Under When calculating this workbook, click to select the Use 1904 date system check box, and then click OK.