Shift spans midnight at differing pay rates

[marshaji]

I have been a keen follower of this website and all the really fantastic excel wonders that you do and have implemented some of your dashboards and camera tool tips in my daily work. Hope you can help on this one.


I am working on a project for my boss, where we export data from a database in which we do not have access rights to create queries or report other than standard reports that were created.


The issue I have is that I require to pay personnel based on the following


Day of shift and shift group.


Monday evening £20


Monday Night £30


Weekend Rate irrespective of time is £30


What i require is to calculate is when a shift starts before midnight and crosses midnight, how many hours do I require to pay at what rate.


Small example


Date Day ShiftGroup Start End Duration Midweek Nights Wkd tl2


04/10/11 Tues Evening 18:00 23:00 5:00 5


04/10/11 Tues Night 23:00 06:00 7:00 1 6 6


08/10/11 Sat Morning 08:00 13:00 5:00 5 5


08/10/11 Sat Evening 20:00 01:00 5:00 1 4 5


What I believe I need to happen is the following:


1)Calculate duration of shift


2)Identify Day of week and Shift Group and populate correct number of hours for each type


3)Total Hours for Nights and Weekends as one total as this is the same rate.


If anyone require to see this as a spreadsheet I can certainly provide one, not sure how to upload it though, although I'm sure someone could point me in the right direction on this.


Jim ( marshaji)

 

[Luke M]

I think you need to more clearly define a few things. When exactly does the morning, evening, night, and weekend shift start? (noon, 8 am, midnight, etc)

I think some of the spacing got messed up in your last 3 columns. Could you double check and clarify (perhaps add some zeros so we know which column the data is actually in)?


Duration of shift is the easiest to calculate. That will be:

=E2+IF(E2<D2,1,0)-D2

Day of week is:

=TEXT(A2,"ddd")


The other questions require more info to answer.

 

[3G]

Luke-

I see where E>D works out, but, I'm having a problem with when the times span midnight. For example...


E2+IF(E2<D2,1,0)-D2 ('=23+0-18=5) - Sure no problem


E3+IF(E3<D3,1,0)-D3 ('=(6+1)-23=-16) - Bit confused


can you explain real quick? Sorry if this is a basic math question, but, just not getting the logic

 

[Luke M]

Sure thing. It's not really 23 and 18 (not integers, at least). Since those are times, it's really 23/24 and 18/24. The "+1" is to add another day, so it becomes 6 am plus 1 day (aka, 6 am tomorrow) - 23:00 (of today)


From a math perspective, this become:

=6/24 + IF(6/24 < 23/24,1,0) - 23/24

=6/24 + 24/24 - 23/24

=7/24

=7 hrs

 

[marshaji]

Hope this simplfies what i am trying to do


Core hours Mon - Fri are 08:00 - 23:59 £20

NIghts MOn - Fri are 24:00 - 07:59 £30

Weekends 00:00 - 23:59 £30


Date Day ShiftGroup Start End Dur Midweek Nights Wkd Total Night & Weekend Hours

20 30 30

04/10/2011 Tues Evening 18:00 23:00 05:00 5 0 0 0


04/10/2011 Tues Night 23:00 06:00 07:00 1 6 0 7


08/10/2011 Sat Morning 08:00 13:00 05:00 0 0 5 5


08/10/2011 Sat Evening 20:00 01:00 05:00 0 0 5 5


The file is here: https://rapidshare.com/files/3369902187/mars_file.xls

 

[Hui]

Marshaji


Firstly, Welcome to the Chandoo.org Forums


You can read about upload options here:

http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[Luke M]

I think this works...

Assuming midweek column is column G

in G2:

=F2-SUM(H2:I2)

in H2:

=IF(OR(B2="Sat",B2="Sun"),0,MAX(8/24-D2,0)+SUMIF(E2,"<"&8/24))

in I2:

=IF(OR(B2="Sat",B2="Sun"),F2,0)

in J2:

=SUM(H2:I2)

Duration and Day formulas previously given.


All results will be in hours (x:xx)

If you need a single integer, multiply each cell/formula by 24.

 

[marshaji]

Hi Luke M


I am trying this out as we speak and it appears to work.


All i need to do now is set this up as a template that i can import to and then fantastic


Thank you.


Jim