Calculating Time Variance

**winnie_shrub** · 01-26-2009, 01:28 PM

Hi,

I think I'd prefer to show the result as a decimal.

Please recommend a way forward.....

Thanks a lot!

**DonkeyOte** · 01-26-2009, 01:31 PM

Formula:

I14: =24*(SUM(E14:F14)-SUM(B14:C14))

You can dispense with H14 etc... dates in XL are integers and time decimal so you can simply add the two Actual values together (E & F) to form one number and subtract the sum of the Planned date + time to give you a result... multiply by 24 to force into decimal -- 2.5 equates to 2hr 30min.

Set format of I14 etc to be Numerical (to 2 decimals).

**winnie_shrub** · 01-27-2009, 04:27 AM

Thank you, that's precisely what I was after.

It has triggered another question though. I wonder if there's a way of getting Excel to recognise time without having to enter it with a colon (i.e. 1000 instead of 10:00)?

Any ideas anyone?