Question on formulas How to.

medic63

New Member
Joined
Sep 12, 2002
Messages
13
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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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

Well-known Member
Joined
Feb 15, 2002
Messages
4,971
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Feb 15, 2002
Messages
4,971
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Forum statistics

Threads
1,144,358
Messages
5,723,891
Members
422,524
Latest member
wirkkarn

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top