# Help with excel formula please??



## confused_girl (Jun 3, 2009)

Hi, this is the first time I’ve used this site but I need some help please?

I have a spreadsheet which we record the start and finish time of the drivers. I am trying to work out a formula to calculate the difference between two times if it meets a certain criteria.

If the night shift work between the hours of 6pm and 6am they get paid an extra allowance so I need a formula to calculate how many hours they have worked between these times.

You might also need to know that the way we record their times is using a 24 hr clock and minutes are recorded as below (I’ve used 5pm as an example!):

17.00 If they clock in on the hour (5pm)
17.25 If they clock in quarter past the hour (5:15am)
17.50 If they clock in half past the hour (5:30am)
17.75 If they clock in quarter to the hour (5.45am)

For example:

Start	Finish	Total
19.00	7.25	11.75

The driver above has worked 11.75 hours. (The total takes away half hour lunch break)

They have worked 11 hours between the hours of 6pm and 6am. I need a formula to work this out?

I hope this all makes sense, sorry it’s a bit long winded!!

Many thanks x :laugh:


----------



## Dragoen (Apr 10, 2009)

Try this:

```
Start | Finish |Total
  A2  |  B2    |  =IF(A2>B2,24-A2+B2-0.5,B2-A2-0.5)
```
That will calculate the totals regardless of the start time and the -0.5 takes out the lunch break too. To factor in the bonus for working from 18.00 on you will need to test A2 to see if it is > 17.75 (if you only have .25 start time increments) when you apply the hourly rate against C2, and then add in the bonus factor. If someone worked 16.00 to 4.50 it would get more involved. Could that happen?

Hope that helps,


----------



## confused_girl (Jun 3, 2009)

Hi Dragoen,

Many thanks for your reply. At the moment I use the following formula to calculate the total hours which works fine:

=IF(B2<1,0,(B2-A2)-0.5+24)

The big problem I have got is that their start and finish time although it should be 18.00 - 8.00 it changes depening on their routes so it could be at anytime. This is were it starts to get confusing! So yes a night driver could end up working 16.00 - 4.50.

Thank you x


----------



## Dragoen (Apr 10, 2009)

So given this example: Start 16.00, Finish 4.00
do you need to calculate the total hours overall and also the total hours that fall between the hours of 18.00 and 6.00?

So in that example the total hours is 12 and the total evening shift hours is 10 (ignoring the lunch break at the moment). I am thinking you need to apply a shift differential to the evening shift hours to compute their pay.

And would the same apply to someone who worked 20.00 to 8.00? 12 hours total but only 10 hours during the 18.00 to 6.00 time frame?


----------



## confused_girl (Jun 3, 2009)

Given the example Start 16.00, Finish 4.00 I just need to calculate the hours they worked between 18.00 and 6.00 (which is 10hrs.) Also for this formula, I do not need the lunch break taking away from it.

Yes the same will also apply to someone that worked 20.00 till 8.00 (Which again will be 10hrs)

So if someone worked 22.00 till 3.00 the formula should calculate 5hrs.

I hope I'm not confusing you!!

Big thank you x


----------



## Dragoen (Apr 10, 2009)

Hi CG,

First - the more detail you give the better, so thanks. Ok, using my first example, use column D for "Total Evening Shift hours" and put this in D2:

=IF(A2>B2,(MIN(24-A2,6)+MIN(B2,6)),IF(A2<6,A2,0)+IF(B2>18,B2-18,0))

That should do it.


----------



## confused_girl (Jun 3, 2009)

Thank you so much, that works brilliantly. You have been a great help x


----------



## Dragoen (Apr 10, 2009)

you are quite welcome


----------



## confused_girl (Jun 3, 2009)

Hi Dragoen, 

I'm so sorry but could I ask for your help one more time regarding the same query, I have tried to work this out myself but it is a bit over my head!!

I've now been told that if the drivers works between 18:00 and 6:00 then 0.5hrs has to be taken away from the total, but if they work 0.5 outside these hours (either side) then the total stays the same.

So if they work 17.00 - 6.00 then they earn 12hrs night shift allowance

But if they work 18.00 - 6.00 then they only earn 11.5hrs shift allowance.

Does this makes sense? 

Many thanks x


----------

