<<Time Total Problem >>

[Scooterdog]

First of all, my understanding of cell calculations does not go much futher
than =a1+a2!
With that, I will try to explain what I am trying to accomplish.

If I arrive at a location at 19:15 and show off duty at 20:30, I am paid 3
extra terminal miles for the last 15 minutes. This much I now have.

The problem is: If I arrive at 19:15 and show off duty at 23:00; NOW I earn
both 1'58 minutes Overtime(19 miles) plus 21 extra terminal miles. The
extra terminal miles stop at 21:58 because now the overtime begans.

Is there a formula that will add the arrival time 19:15 up to--BUT not
beyond--21:58?

Again, If I show off duty BEFORE 21:58--say 20:30--I think I would get the
correct answer of 3.
If this helps: I have to allow 1 hour after arrival time before the extra
terminal pay begins. The cal
Thank you for your time in advance.

 

[macropod]

Try:
=MIN(A1+A2,0.915277777777778)
Excel stores times as fractions of a day. 21:58 = 0.915277777777778 days.
The MIN function tells Excel to return the lower of the expressions, in this
case, A1+A2 or 0.915277777777778.

Cheers

 

[Scooterdog]

Please read the second paragraph of this letter, if you will. It starts
with--"If I arrive at". This is what I need help on. Can you help me??
Thank you for your time...

 

[Paul]

With arrival time in A1 and off duty time in B1, this formula will calculate
the time difference less 1 hour, but only up to 21:58
=MIN(B1,TIME(21,58,0))-A1-TIME(1,0,0)
To get the number of terminal miles (if I am right in thinking that it is
one for each 5 mins or part thereof), use
=ROUND((MIN(B1,TIME(21,58,0))-A1-TIME(1,0,0))*288,0)

When you enter such formulas, Excel may unhelpfully reformat the cells for
you. Just reformat as you wish in order to see sensible results.