Working with dates

Hey fellow Gliders,

I am working on an employee scheduling App. I have to schedule employees for shift but these employees can only work 6 days straight and on the seventh day, they must be off-duty.
These dates are normally not straight forward and are requested by the client. The client sends a service request and asks for a number of employees for a period e.g. 10 August - 30 September. Now the law states that an employee can not work more than 6 days consecutively when working 12 hour shift.

What I am looking for is a way to put “OFF” on the days that fall on the 7th day within the given time period that the client requests.
I hope this makes sense.
If anyone has a work around or ideas I can try, I would highly appreciate it.

@Darren_Murphy you are usually my go to genius for this type of stuff…(No pressure :hugs:)

Hi @Luther,

I am new to this and learning. But I just watched this video by @Robert_Petitto that was awesome. It might give you some insights on how to accomplish what you are looking to do.

Good luck.


So an employee can work only for one client at a time, is that correct? When you assign them to a specific client, you want their calendar to show “off” every 7th day?

1 Like

Presumably the count of days worked should not reset when an employee is re-assigned?
For example:

  • Employee A has a day off, then works four days for Client X
  • On the 5th day, employee A is re-assigned to Client Y
  • I would assume that after working two days for Client Y, employee A would be due for a day off.


Assuming the above is true, I think I would separate the employee schedules and client schedules. That is, devise a schedule for all employees that gives them six on, one off. And then just assign employees to clients according to client service requests. The two would be independent of each other, with the employees taking days off as per their schedule, regardless of which client they are assigned to at the time.

Matching the two up would require the typical scheduling logic. That is, when considering any given client request, you compare the requested start and end dates to the list of employees that have availability during that period, and assign accordingly.


Yes that’s correct @ThinhDinh. Only 1 client can be serviced at a time.

The “OFF” or any other variable used for flagging will then be used in an ITE column to help calculate the total hours worked by each employee.

The employee after their day off, they will resume with the same client because the job card date will still be operational.

Let me try and elaborate further for clarity.
Let’s ignore different clients and only consider 1 client.
The client has a request for a period of 40 days spanning across 2 months August till September.
I have 6 employees to schedule and every 7th day from the start date of the project e.g. 10 August.

The 7th day is only considered with respect to the start date of the project and not the time prior to that (I think this is the important part I left out unfortunately).

Great question BTW!

So if all 6 employees start working for the same client on the same date, does that mean they will all have the exact same cycle of six days on and one day off?

If that’s the case, you could probably determine the off day based on the start date day of the week. So let’s say they all start working on a Monday, that means their day off will be Sunday. Then all you need is a math column to determine the Weekday number, and an if-then-else column to return true (meaning off) if it’s equal to 1 (Sunday).

Or am I over simplifying it?

1 Like

Thanks for entertaining this case up to this point. I truly appreciate it.

On the contrary I think I am the one that over simplified this in my thinking and explaining. You did however raise a few things for me to think about…

  1. There is a minimum number of employees that must always be present at the client location which in this case is 5.

  2. I am allowed to use 2 cycles whereby I can allocate the employees across a “6 days on & 1 day off” cycle and a “12 days on & 2 days off”.
    To avoid having a situation whereby everyone needs to go off on the same day, I need to work out how I will distribute my work force between these 2 cycles in a rotational manner so that even the hours worked are distributed fairly among the employees whereby the weekends tend to offer more in terms of remuneration.

I just need to go and think around this and will come back later to update the post on what I have come up with.
Thanks for the probing questions which have led to me realising the blind spots within this case.



Hope you can get somewhere with this.

Act as a scheduler. You will work with the information below:

My client wants to have 7 contractors with the emails being:,,,,,,

My client wants to have them on contract between 1 Aug 2023 and 19 Oct 2023.

Conditions: Each day, a minimum of 5 workers should be presented. Workers can follow a schedule of "6 days on & 1 day off", or "12 days on & 2 days off". Try to schedule as many people as possible on the weekends, since those hours are paid more.

Derive a JSON structure showcasing me the days off of each person tied to their email. Please only return the JSON, and nothing else. Schedule for the whole period from 1 Aug 2023 to 19 Oct 2023.

Example of a valid response.

"": "Aug 7 2023, Aug 14 2023",
"": "Aug 8 2023, Aug 15 2023"

No talk;just go.

Thank you so much @ThinhDinh :pray::raised_hands::hugs:.

This is very helpful and will set me off on the right path…

1 Like

The key is forcing it into JSON so you can work with it more easily down the line. No talk;just go. helped here, but please test thoroughly to see if it works in your instance.