Time difference calculation

[Sandy]

Hi

Excel 2003

In C5 i have a start time eg 12.30pm and in D5 I have a finish time eg
20.45pm.

I need to calculate the hours and minutes worked in E5, with a minimum of 1
hour. In other words if 45mins is worked then 1 hour is returned, but
anything greater than 1 hour will be as is.

This nearly works, but if the start time is say 10pm and finish is next day
at 6am then it fails.
=IF(D5-C5<=TIME(1,0,0),TIME(1,0,0),IF(D5<C5,D5+1,D5)-C5)

I have tried to incorporate (D5<C5,D5+1,D5)-C5) into the first part of the
function but I cannot get it to work.

All assistance gratefully received.

Sandy

 

[Bernard Liengme]

Hi Sandy,
The trouble is, your formula begins by checking if D5<C5 and returns 1 hour,
so it cannot resolve the 'starts tonight, finishes next morning' since this
happen too late if your formula.
We could solve this with the ugly formula
=IF(IF(D5<C5,D5+1-C5,D5-C5)<=TIME(1,0,0),TIME(1,0,0),IF(D5<C5,D5+1-C5,D5-C5))
which computes the difference twice.

I think we tend to reach for the IF method too readily. I like to do the
'starts tonight, finishes next morning' thing with (D5-C5)+(D5<C5)
This computes D5-C5 and add 1 (that is 1 day or 24 hours) if the start time
appears to be before the end time.

Next we need to add 'but if result is less than one-hour, report 1 hour'.
How about =MAX(TIME(1,0,0), (D5-C5)+(D5<C5))
This compares 1 hour with the result of (D5-C5)+(D5<C5)) and returns the
larger value
No IF (ANDS or BUTS) !
best wishes

 

[Sandy]

Bernard

Absolutely brilliant

I presume (D5<C5) evaluates to 1 if the condition is true and 0 otherwise.

Thank you
Sandy