Calculating Time Variance

[winnie_shrub]

Dear Clever People,

I'm trying to calculate the variance between planned date & time of arrival vs actual date & time of arrival.

I attach the workbook as am a bit useless at explaining myself....

What I've done is in H14 subtract the actual date of arrival (F14) from planned date of arrival (C14). This result is the only way I could think of dealing with crossing over midnight. As a result I14 should subtract the actual time of arrival (E14) from planned time of arrival (B14):

=SUM(E14-B14,H14)

This method works well when the arrival was later than expected but doesn't work if the arrival was sooner than expected.

I'm sure you're all giggling at the novice........if so, please help!

[DonkeyOte]

No we're not giggling... dealing with negative time in XL is a real headache, you can't easily display a negative time value
(given mix of positive & negative time you could do it switching to 1904 system but this is really not advisable)

Alternatives:

-- store result as decimal ( eg -2.5 = gain of 2.5hrs )

-- ensure the time is always calculated positively but use an adjacent column to flag gain/loss such that you can sum the time accordingly
(ie differentiate between +ve/-ve)

Which would be your preference ?

[winnie_shrub]

Hi,

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

Please recommend a way forward.....

Thanks a lot!

[DonkeyOte]

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]

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?

[DonkeyOte]

I strongly advise against entering time values in anything other than time format.

It will cause you many more problems in the long run.