<<Time Total Problem >>

  • Thread starter Thread starter Scooterdog
  • Start date Start date
S

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.
 
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
 
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...
 
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.
 
Back
Top