How to display negative amount of time

[Lynn_vdb]

Hi everyone,

I’m making a worktime sheet for my husband.

I’m not having any trouble deducting his start and stop times, but I made a column for the amount of hours he’s supposed to work, and a column for the hours he effectively worked. And if the result of this deduction is a negative number (for instance if he was supposed to work 8h but only did 7,5) Excell won’t show me the negative number... which should be -30min.

is there a solution for this problem?

thanks!

[#2]

@Lynn_vdb 

Change the way negative numbers are displayed

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac Excel for Mac 2011

https://support.microsoft.com/en-gb/office/change-the-way-negative-numbers-are-displayed-104b47a6-8a...

Create a custom number format

https://support.microsoft.com/en-us/office/create-a-custom-number-format-78f2a361-936b-4c03-8772-09f...

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

[#3]

Hello @Lynn_vdb,

Assuming the actual end time is in cell A1, the anticipated end time is in cell B1, and the difference is in cell C1, then that could be:

For hours:

(A1-B1)*24

 For minutes:

(A1-B1)*1440

Also, be sure that cell C1 is formatted as either General or Number.

[#4]

Maybe, for the formula, you could use absolute value (ABS function), but then use conditional formatting to test if he worked less than what he was supposed to and apply a custom number format of

-h:mm

or something similar. So, the cell contents will still be positive, but the formatting will display it as negative? If you need totals, then you would have to use sumif functions to test if the actual time is <=> the expected time and net the results.

Maybe someone else will have a better suggestion. I can't say I've ever tried to work with negative time values.

[#5]

@Lynn_vdb 

Time is always time, we can't go to the past and shift time back. It's always positive. However, you may show time difference not in terms of time, but in terms of hours or days or minutes difference between two events. Taking into account that  1 day is equal to 1, thus 1 hour is equal to 1/24, you may deduct time of event happened at 07:30 from one happened at 08:00, multiply difference on 24 and the result will be -0.5 if you apply General format to it. That means -1/2 hours difference.

Other words, =(B1-A1)*24 with applying General format to the result.