spaceunderscore
New Member
Todays challenge conundrum for the excel wizards!
The goal is to count how many shift I have worked during a weekday and how many at weekend and how many weekends qualify for overtime Rate 1 (R1<30 weekend shifts) or overtime Rate 2 (R2=>31 weekend shifts). Importantly, an overtime weekend (Sat/Sun) should only counts if the weekday count is >5 to qualify for weekend overtime rate. If weekday count is <5 then any weekend shift is counted as a weekday rate.
A 7 day working week is commence Sunday to end Saturday. After working >30 shifts @R1, shift 31 or greater will be R2 and it will reset to R1 on 1st Jan until >30 again.
I have "A" column with dates ascending - (new date entries will be at the bottom of table). They are displayed "Day of Week, Date, Month, Year" * see note at bottom.
Column "B" any text within these cells are a reference to a job and should count as 1 if column A falls between Mon-Fri and cumulatively count the shift outputting the result in "WEEK to Date" column "C".
Scenario: Work Week1 Mon-Fri = 5 days worked and Week2 Mon-Thurs = 4 days worked
Result:
(Mon) C1=1, C2=2,C3=3, C4=4, C5=5, C6=5, (Sun) C7=5,
(Mon) C8=1, C9=2,C10=3,C11=4, (Fri not worked) C12=4,C13=4, (Sun) C14=4
Week 1 qualifies for OT wkend rate, wk2 does not.
Column "D" will count cumultively the weeknds worked that are not paid at overtime (R1 or R2) rate because the value of the "Week to Date" is <5. If >5 weekday shift this will be 0 in column D because this column is only for normal rate.
Column "E" is the Weekends at overtime rate (R1 or R2). This column should just count every weekend overtime shift "YEAR to Date" cumulatively. So that I see an increasing number of shifts going down the column but the count must reset on 1st Jan in column "A". After 30 of OT shifts in a year the OT rate goes from Rate 1 to Rate 2. I would like an output of this for every shift for further calculation another time in column F.
*Please note, column A has some entries with two rows the same date due to more than one shift in a day and therefore it is not always 7 rows betwen week commence and week end.
Also, I am a rusty intermediate user of excel 365 and not very good with fancy formulas. Your thoughts are very much appreciated as this formula should save a lot of time.
The goal is to count how many shift I have worked during a weekday and how many at weekend and how many weekends qualify for overtime Rate 1 (R1<30 weekend shifts) or overtime Rate 2 (R2=>31 weekend shifts). Importantly, an overtime weekend (Sat/Sun) should only counts if the weekday count is >5 to qualify for weekend overtime rate. If weekday count is <5 then any weekend shift is counted as a weekday rate.
A 7 day working week is commence Sunday to end Saturday. After working >30 shifts @R1, shift 31 or greater will be R2 and it will reset to R1 on 1st Jan until >30 again.
I have "A" column with dates ascending - (new date entries will be at the bottom of table). They are displayed "Day of Week, Date, Month, Year" * see note at bottom.
Column "B" any text within these cells are a reference to a job and should count as 1 if column A falls between Mon-Fri and cumulatively count the shift outputting the result in "WEEK to Date" column "C".
Scenario: Work Week1 Mon-Fri = 5 days worked and Week2 Mon-Thurs = 4 days worked
Result:
(Mon) C1=1, C2=2,C3=3, C4=4, C5=5, C6=5, (Sun) C7=5,
(Mon) C8=1, C9=2,C10=3,C11=4, (Fri not worked) C12=4,C13=4, (Sun) C14=4
Week 1 qualifies for OT wkend rate, wk2 does not.
Column "D" will count cumultively the weeknds worked that are not paid at overtime (R1 or R2) rate because the value of the "Week to Date" is <5. If >5 weekday shift this will be 0 in column D because this column is only for normal rate.
Column "E" is the Weekends at overtime rate (R1 or R2). This column should just count every weekend overtime shift "YEAR to Date" cumulatively. So that I see an increasing number of shifts going down the column but the count must reset on 1st Jan in column "A". After 30 of OT shifts in a year the OT rate goes from Rate 1 to Rate 2. I would like an output of this for every shift for further calculation another time in column F.
*Please note, column A has some entries with two rows the same date due to more than one shift in a day and therefore it is not always 7 rows betwen week commence and week end.
Also, I am a rusty intermediate user of excel 365 and not very good with fancy formulas. Your thoughts are very much appreciated as this formula should save a lot of time.
Attachments
Last edited: