I am trying to find an automatic way to plan a roster. The approach could be from Power Apps, Power Automate, DAX, M or also Excel.
In this situation, we have months with different start and end date. At this stage we have only 2 types of month:
- A- from day 26th to day 25th (the following month)
- B- each month of the year has a different start and end day (around 15th to 18th)
We have three different types of shifts:
- Sunday-Monday: start on Sunday and ends on Thursday and the next week starts on Monday and ends on Friday
- Monday-Sunday: start on Monday and ends on Friday and the next week starts on Sunday and ends on Thursday
- Monday-Monday: start on Monday and ends on Friday, and so on
Of course, it could happen that the month starts not on Sunday or Monday. i.e: for the month A, in August it starts on July Tuesday 26th, but the shifts that are Sunday-Monday, on that first week of the month they worked from Tuesday 26th to Thursday 28th and starts a new roster on Monday 1st August.
Another consideration is that a worker could not start at the beginning of the month. i.e: a worker that has month type A, is planned to work in August since Monday 8th August with shift A.
Please find attached an Excel with Month Type A.
My goal is that with data from shifts tab
- the month
- month type
- shift type
- start date (if there is no absence planned the same as month type started day)
- ends date (if there is no absence planned the same as month type ends day)
I can plan the days to work on that month. Ideal to have the roster by day because then when I have data from other source, I can check if a day is planned to work, and it was worked.
I think is a bit challenging what I´m asking so what I´m expecting is to have some ideas or suggestions to study and get inspired to find a solution to this.
Shift Example.xlsx (18.0 KB)