CODEWITHSUNDEEP
exceltimesubtractionnegative-number
mustafa mohammed
mustafa mohammed

Reputation: 69

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).

Upvotes: 2

Views: 156

Answers (2)

ZygD
ZygD

Reputation: 24366

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.

Upvotes: 3

D_Bester
D_Bester

Reputation: 5911

I assume you are subtracting one date/time from another. Just format the cell with the answer as General. One day = 1.

=A3*24 gives you the number of hours +/-

=A3*24*60 gives you the number of minutes +/-

=A3*24*60*60 gives you the number of seconds +/-

enter image description here

This works positive or negative.

Upvotes: 2

Related Questions