Question on formulas How to.

[medic63]

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

 

[Gates Is Antichrist]

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.

 

[Dave Patton]

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})

 

[Dave Patton]

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