Subtracting time in excel

I want to subtract time.
Cell C10 and C11 contains time .
CEll D10 contains C11 - C10

Ideally the answer should be  4 but i am getting some negative value .
Capture.PNG
Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ConsultantCommented:
Hi,

You are effectively subtracting 21 hours from 1 hour to get minus 20 hours.

Excel measures time as a fraction of a day (24 hours) so minus 20 hours = -20/24 = -0.833333....

You can then display that as hours (HH:mm format for example) or multiply by 24 to go from days to hours:

0.166666 x 24 = 4 (hours)

See F10 and F11 in the attached for examples.

Alan.
EE-29088485-Version1.xlsx
Business Systems Analyst , ex-Senior Application EngineerCommented:
or in cell D10, try apply formula such as:

``````=IF(C11<C10, TEXT((TODAY()+1+C11)-(TODAY()+C10),"h"),TEXT(C11-C10,"h"))
``````
Commented:
I provided you with the answer in your previous question - https://www.experts-exchange.com/questions/29088470/Subtract-time.html
Author Commented:
@alan  formula you gave in excel file will work fine for all the negative values .
But what about the positive ones .
EE-29088485-Version1.xlsx
Excel & VBA ExpertCommented:

``````=MOD(C11-C10,1)*24
``````
and format the formula cell as General.

Experts Exchange Solution brought to you by