Solved: Time calculation

[Barryj]

I have a starting time in cell D8 which is 03:09 and finishing time in D9 which is 12:20 for a total of 9:11 hours.

I am trying to do a calculation in cell D10 that would say if the hours are equal to 8 or less then = to D9, if D9 is greater than 8 then I want 9:11 hours minus 8 hours then times the difference by 1.5 then add this to 8 hours.

This should then give a figure of 9:46 hours.

Hope this makes some sense.

[Norie]

Barry

Try this formula.

=IF(D9-D8>TIME(8,0,0), TIME(8,0,0)+((D9-D8)-TIME(8,0,0))*1.5, D9-D8)

[Bob Phillips]

Another way

=MAX((D9-D8)-8/24,0)*1.5+MIN(8/24,D9-D8)

[Cyberdude]

Here's a function for you to try:
[vba]Function TimeDiff(ETim As Date, LTim As Date)
Application.Volatile (False)
If LTim - ETim > 0 _
Then TimeDiff = Format(LTim - ETim, "h:mm") _
Else TimeDiff = Format(1 + LTim - ETim, "h:mm")
End Function [/vba]
where ETim is the "early" time and LTim is the "late" time.

[Barryj]

Thanks guys, I will give them a try tomorrow and see how I go.

[Barryj]

Am still getting ######## when starting time is before midnight and finish time is after midnight.

Have included a file to show this cells are formated as custom hh:mm.

[Bob Phillips]

=MAX((MOD(D9-D8,1))-8/24,0)*1.5+MIN(8/24,MOD(D9-D8,1))

[Barryj]

Thanks XLD and the other guys problem solved.