• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cumulative Count weekday entry within 7 days

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.
 

Attachments

  • Sample Records.xlsx
    85.8 KB · Views: 2
Last edited:
Hi and welcome to the forum. As per forum rules, a sample file would be very nice. Including realistic data and a correct result which you've added manually.
 
spaceunderscore
You start Your writing: Todays challenge for the excel wizards!
... challenge ...
Did You notice that there are also Forum for
Excel Challenges
Post an Excel related Challenge for others to Solve (You must have a solution) Don't post questions here!
If Your thread is ... the challenge ... then this should be open to Excel Challenges.
or
as written with #2 Reply ... this would be a challenge for many.

You ( spaceunderscore ) wrote that You're new here - Welcome.
Everybody can anytime reread New Users - Please Start Here
 
Last edited:
spaceunderscore
You start Your writing: Todays challenge for the excel wizards!
... challenge ...
Did You notice that there are also Forum for
Excel Challenges
Post an Excel related Challenge for others to Solve (You must have a solution) Don't post questions here!
If Your thread is ... the challenge ... then this should be open to Excel Challenges.
or
as written with #2 Reply ... this would be a challenge for many.
Hi vletm
I'm new here, I didn't realise challenge actually had special meaning on this forum. I don't have the answer so I'll refrain from using that term in furture then.
 
Back
Top