Create a list for 2023 week 1 until 52


I’m struggling to create a list for 2023 with unique employees based on their contract dates.

Name start end hours
A 1-2-2018 31-12-2018 40
A 1-1-2019 40
B 1-2-2018 31-12-2018 40
B 1-2-2018 1-5-2023 40
B 1-5-2023 40
C 1-2-2018 31-12-2018 40
C 1-2-2018 1-5-2023 40
C 1-5-2023 32

Based on the table above, I would like to make a list per employee that picks up their latest contract date and repeats 52 times (week 1 until week 52) with the correct contract hours. In the table above, for A, it would be 52 week rows with 40 hours. For B, it would be the same (52 weeks), but for C, it would be 40 hours from 1-1-2023 until 1-5-2023 and, from 1-5-2023 until the end of the year, it would be 32 hours.

Any idea how I can create this?
To recap:

  • I need to repeat 52 rows (max) for each unique employee.
  • I need to pick up their latest contract. This can be either in 2023 or a contract before 2023 (see example A).
  • I need to take into account changes/mutations within the years (see examples B and C).

Really looking forward to your help!


Hi @Tayfun , I’m a little confused…

If B and C both have their latest contract starting on 1-5-2023, why do you want 52 weeks returned for B but only up to the end of the year for C ?

Also, you said you want it to pick up “their latest contract date” and repeat, but for the C example you appear to want it to pick up their latest contract dates but also do something with part of their previous contract dates. Can you clarify?

In the meantime, maybe this gets you close to what you need, and can be refined

Contract Weeks.knwf (41.4 KB)

1 Like

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.