# Question on formulas How to.

#### medic63

##### New Member
I am working on a spreadsheet that will calculate elapse time on call out jobs. I have the basic formulas in that calculate the elapse time directly from colum B1 as out time to C1 as in time in colum D1 in total elapse time. We do from time to time work more than one job on a call out and require an elapse time for all jobs done on the one call out. for this I would enter a referance # in (example) Colum A1 and the start time in colum B1, Colum C1 would normaly be filled in with the in time but because we did another job it is left blank, Colum A2 would recieve the ref# of the new job with B2 geting the start time and C2 getting the end or in time. there could be many calls done between the start and end. The question is how do I get excel to recognize that the first job was not the end of the call and look for the next in or end time to calculate the total elapse time for all jobs in colum D?
The second question is when a call goes over 5.01 hour a meal allowance is granted of \$8.60 and if over 8.01 hours \$12.40, over 16.01 hours \$24.20. I have a colum to enter the amount of the meal allowance but how do I set it up to automaticly fill in the dollar value in relation to the length of the call out

If you can help me with this problem please e-mail me at medic63@bcgroup.net

Thanks
Tomas

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
As to your first question, you could do some cute things but your simplest solution is to just require an end time for each entry.

Failing that, one way to go would be to insert a new column D, and change your formulae to refer to the new D where you currently say C, e.g.
D1=if(C1="",B1,C1)

You could then also insert a new column C for those cases where B is blank, ending up with something like
C2=if(B2="",E1,B2)
and change your formulae to use the new C where you currently use B.

This approach would lump all the time into the last job, simplifying your meal allowance calculation. Keeping in mind that you've now inserted 2 new columns, it would then be something like
G1=(if(F1<=5,0,if(F1<=8,8.60,12.40))).

If you go with something like this, budget YOUR time for testing This sounds great as I work it out in my head, but you know how it is...

I know know if this approach works exactly for your business needs, but you might play around with it anyway, in case it spurs you to an alternate solution.

I would probably use another column to
to designate Call.

You can consider the following:
A Out Return Elapsed
1 6:00 8:00 2:00
2 8:00
3 9:00
4 12:00 12:30 4:30
5 13:00 15:00 2:00
6 15:00
7 16:00
8 18:00 23:30 8:30

for the last line

=IF(C9="","",IF(D8="",C9-INDEX(B5:B9,COUNTA(C5:C9),1,1),C9-B9))

For meal allowance consider a Lookup either self-contained or with numbers in a Table

=LOOKUP(D9*24,{0,5.01,8.01,16.01;0,8.6,12.4,24.2})

version 2
Ref Call T_Call Out Return Elapsed Meals
1 1 2:00 6:00 8:00 2:00
2 2 8:00 9:00 1:00
3 2 12:00 3:00
4 2 5:10 13:10 1:10 8.60
5 3 1:40 13:20 15:00 1:40
6 4 15:00 16:00 1:00
7 4 18:00 2:00
8 4 8:30 23:30 5:30 12.40
.. 5

Total call is =IF(B2<B3,SUMPRODUCT((\$B\$2:\$B\$20=B2)*(\$F\$2:\$F\$20)),"")

Elapsed is =IF(D2="",E2-E1,E2-D2)

With meals info in range named rMeals
=IF((C2<>""),IF(C2*24>5.01,LOOKUP(C2*24,rMeals),""),"")
This message was edited by Dave Patton on 2002-09-14 09:19

Replies
14
Views
1K
Replies
1
Views
209
Replies
17
Views
2K
Replies
0
Views
332
Replies
0
Views
865

1,217,755
Messages
6,138,422
Members
450,136
Latest member
Tabako1960

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back