CODEWITHSUNDEEP
excel
Stephan
Stephan

Reputation: 1999

Negative time span values in Excel

I develop an app that is able to export its data into an Excel file. Some of the cells do contain negative time span values. I know that Excel is developed in a way (based on the 1900 date system) that leads to issues there so I enabled the 1904 date system. Otherwise I'm not able to show negative time values.

What I have is a time value in B12 (format of all cells is [h]:mm:ss).

enter image description here

Now in E12 I want to show the result of subtracting 40 hours of C12. For some reason, instead of -8 it shows +16. So I double checked in E13 to see whether the calculation itself is different. But there it works as expected.

One possible workaround which I can think of can be seen in E16. There I have a reference cell that contains 40 hours time as value. But I don't like it as this 40 h is some kind of uninteresting content here and should not pop up anywhere.

Another possible workaround would be to use TEXT as the cell format but then my apps users will loose the possibility to do further calculations with the exported values.

So the question is: Why is E12 showing +16 hours? What am I missing here?

Upvotes: 1

Views: 957

Answers (1)

miroxlav
miroxlav

Reputation: 12184

What am I missing here?

You are making incorrect assumption on how first parameter of TIME() function works. 40 does not mean 40 hours in result, but (I quote):

Hour Required. A number from 0 (zero) to 32767 representing the hour. Any value greater than 23 will be divided by 24 and the remainder will be treated as the hour value. For example, TIME(27,0,0) = TIME(3,0,0) = .125 or 3:00 AM.

Do you really need that function? You can calculate 40 hours like 40/24, i.e. with =C12-40/24 in E12. Then you get expected time -8:00.00.

If you have value >= 24:00:00 at some other location (e.g. A1), you can convert it using =DATEVALUE(A1) + TIMEVALUE(A1). Such values have their date part. But when you know how Excel date/time system works (1 = 24 hours, so 1/6 = 4 hours, 1.5 = 36 hours, etc...), you do not need to bother with these functions and you can calculate many time values directly as I shown you.

Upvotes: 1

Related Questions