CODEWITHSUNDEEP
excel
Timothy Bolton
Timothy Bolton

Reputation: 75

how to do 2 seperate operations if start time of shift is before 7 but end time is after 7

I'm trying to make an excel sheet where I only need to put in start and end time and excel chooses the correct pay rate and how many hours I've worked (already done) and outputs how much I've earned. So far I have a column (D) for the date of shift (DAY, day of month, Month, year) column for hours worked (E), column for start time and end time (F, G) I have already written the formula to calculate the hours worked but in Australia where I live my pay rate increases after 7 PM, and increases again after 12 AM. Is there a way to have excel automatically know that it needs to take the hours worked before 7 PM and multiply it by a 24.41, then the hours worked between 7 PM and 12 AM by 26.54 etc, if my shift starts for example at 5:30 pm and ends at 3 AM?

These are the different payrates at the different times: (Time is in cell A1, Pay rate is B1, etc)

Time                    Pay Rate
Regular                 $24.41
Mon-Fri 7pm-midnight    $26.54
Mon-Fri 7midnight-7am   $27.60
Saturday                $29.30
Sunday                  $34.18
Public Holidays         $48.83

Thanks in advance

Upvotes: 0

Views: 96

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34285

Solutions to all of these type of questions use the standard formula for overlapping time periods

=max(0,min(end1,end2)-max(start1,start2))

The best way to do it IMO is to simplify it two ways

(1) By splitting any times that cross through midnight into two parts, one up to midnight and one from midnight onwards

(2) Using a lookup table to match the conditions (day of week and time of day) to the payscale.

Then use an array formula to do the lookup and calculation. Because you can't use MAX and MIN as above in an array formula, you have to write it out using if statements and the formula gets pretty long

=SUM((WEEKDAY(E2,2)>=PayRates!$A$2:$A$10)*(WEEKDAY(E2,2)<=PayRates!$B$2:$B$10)*24*PayRates!$E$2:$E$10*
IF(IF(G2<PayRates!$D$2:$D$10,G2,PayRates!$D$2:$D$10)-IF(F2>PayRates!$C$2:$C$10,F2,PayRates!$C$2:$C$10)<0,0,
IF(G2<PayRates!$D$2:$D$10,G2,PayRates!$D$2:$D$10)-IF(F2>PayRates!$C$2:$C$10,F2,PayRates!$C$2:$C$10)))

This has to be entered using CtrlShiftEnter

This is how my pay rates are arranged

enter image description here

NB When the finishing time of a pay rate is midnight, it is entered as 1 (you want it to be 24:00, but entering 24:00 just gives you the same as 00:00)

And this is the main sheet

![enter image description here

With the following columns

A,B and C are your input.

Split

=B2<A2

StartDate1 and StartTime1 are just a copy of your input

EndTime1

=IF(Split,1,B2)

StartDate2

=E2+Split

StartTime2

00:00

EndTime2

=IF(Split,B2,0)

Total1

The main formula

Total2

The main formula copied across by four columns to give any pay for the second day when the shift goes through midnight.

Total

Total1+Total2

Public holidays can be added fairly easily.

Upvotes: 1

Related Questions