CODEWITHSUNDEEP
excel
Landers
Landers

Reputation: 89

Adding/Subtracting Whole numbers from Time

I have tried every which way to format cells to subtract the result from time for instance the formula in the cell = 11(this is 11 minutes) I want to take that result minus 8:00:00 to give me 7:49:00 but it doesn't work the result is ####### no matter how big I make the cell. And if I format the cells with the formula to custom [m]:ss then the value changes.

Sample of the Worksheet:

image

I want Y2 = X3-W3 in a time format.

Upvotes: 1

Views: 3941

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

Try this:

=TIME(HOUR(X3),MINUTE(X3)-W3,SECOND(X3))

The ######### is because you have a negative time. Becuase Excel reads time as decimals of 24 hours, 8:00:00 is .3333 and if you subtract 11 from that you get -10.6666 and date/time can not be negative.

Upvotes: 0

Don Trembly
Don Trembly

Reputation: 17

So, if A1=11

Then in some other cell, (B1 in this example): =TIME(,A1,)

Then subtract from the cell with 8:00:00. (If it's C1...:)

=C1-B1

That will give you the time you want.

Info: The main thing is that you have to tell Excel that your cell with the "11" in it, is minutes. By using the =TIME(,A1,) you will get the value of: 12:11 am. (If you keep it in Date format.) 12:11 am could also be viewed as: 0 Hours, 11 minutes, 0 seconds. And now that it knows, you should be able to subtract.

Upvotes: 1

Related Questions