PDA

View Full Version : Solved: Time calculation



Barryj
07-29-2006, 08:46 AM
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
07-29-2006, 10:28 AM
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
07-29-2006, 12:30 PM
Another way

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

Cyberdude
07-30-2006, 07:43 PM
Here's a function for you to try:
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
where ETim is the "early" time and LTim is the "late" time.

Barryj
07-31-2006, 06:08 AM
Thanks guys, I will give them a try tomorrow and see how I go.

Barryj
07-31-2006, 06:25 PM
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
08-01-2006, 01:00 AM
=MAX((MOD(D9-D8,1))-8/24,0)*1.5+MIN(8/24,MOD(D9-D8,1))

Barryj
08-01-2006, 05:25 AM
Thanks XLD and the other guys problem solved.