Help with random scheduling

Question

Help with random scheduling

Anonymous

I apologize if this has been asked and answered previously. If so, please direct me to the appropriate answer(s).

As reference, I am a drug and alcohol random testing scheduler. There are approximately 900 employees in the data set, with varying shift schedules.

Assumptions (see example at end):

Assume 4 columns and 900 rows to the spreadsheet (one row per employee):

Column A: employee name

Column B: shift schedule assignment (40 hour, 1-3, or A-D)

Column C: scheduled date (manually copy/pasted value from column D)

Column D: Random date generation

Starting day for each schedule set (see below) rotation is 1/2/25

Please note, shift time is irrelevant, only added for example purposes

Schedule set 1: 40 hour work week- M-F 8a-5p

Schedule set 2: 24 hours on, 48 hours off. 3 separate shifts. Ex. shift 1 Mon 6a-Tues 6a, Shift 2 Tues 6a-Wed 6a, Shift 3 Wed 6a-Thur 6a, repeat with shift 1, etc.

Schedule set 3: 24 hours on, 72 hours off. 4 separate shifts-A, B, C, D. repeats as indicated in schedule set 2.

I currently use randbetween(date) function to determine a random date, but then I have to verify the date against the shift schedule set, and this is a labor intensive manual process. I use the randbetween(date) function because my testing period is not a true calendar quarter, so I manually modify the parameters each quarter with the actual dates.

How do I check to ensure each randomly generated date is on a scheduled day of work based off the shift id?

Ultimately, I want the formula to return a randomly generated date for each employee, using the shift id to determine the employee is scheduled.

Although I have the specific shift data entered in an outlook calendar, I do not have a spreadsheet identifying all shift dates for all employees. I receive the data in a pdf file that identifies the employee name and shift number, so I would need to create a spreadsheet to properly access the data.

Here is an example of what I need. (I might not have typed the randbetween(date) function exactly, but I have the formula currently correct in the sheet. If I made a syntax error (I'm writing this from memory), then that would need to be fixed first. In this example of my current setup, I will return a date between 1/1/2025 and 3/31/2025, but I then will need to verify the date returned is a date that shift 2 works. Shift 2 dates, based on the previously posted assumptions, would be 1/3/25, and every 3rd day thereafter.

Name	Shift	Scheduled Date	Random Determination
John Doe	2		=randbetween(DATE(2025,1,1),(2025,3,31))

TYIA.

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Answer 1

Thank you. That makes sense. I made the schedule today so it's in a similar format.

Shift schedule is 7 columns with start date manually entered. After that, from memory, I want to say i set the formula to: if (([previous cell]+3)<=date(2025,12,31),[previous cell]+3,"wrong year") because I like to leave indicators that I went to far. I also want to use the sheet every year, so this way I just need a quick find/replace in formulas 202x with 202x+1.

Now I'll just have check the shift, then generate once shift is determined. Please help with this one last problem in the formula. Can I set the randomly generated number to compare against a max date so I can search the entire index but not return a result greater than a quarter end date. This will let me minimize the formula changes as I move forward. Here is my attempt for the full formula.

=if([shift schedule]=1,if((INDEX(B$:B$,RANDBETWEEN(1,COUNTA(B$:B$)))<=(date(2025,3,31)),(INDEX(B$:B$,RANDBETWEEN(1,COUNTA(B$:B$))),[run again]),if([shift schedule]=2, etc... until iteration 7 is checked for shift schedule.

How do I set the "[run again]" and is the random determination check against the date in the correct syntax? I'm not at my office so i can't enter this formula to try to error check it at this time. I'm just brainstorming on my phone.

Also, i could use the randomly generated selection to calculate a quarter [roundup(month/3,0)] as an error check.

I know i keep asking more involved questions, but the hope is to generate one reusable formula and data set that only require minor modifications. Ultimately I will work to remove the required upkeep, but that will be much easier after I understand the formula.

Answer 2

Anonymous

Here is an sample for get rand date from list in column E.

=INDEX(E2:E5,RANDBETWEEN(1,COUNTA(E2:E5)))

0 comments

Answer 3

Thank you for the response.

Unfortunately I do not have dummy data that I can post.

Based on your response, it seems like I will need to create another file with schedules so I can track accordingly.

Will you please provide an example of the modification to the randbetween formula that would compare the randomly generated date against shift schedules? Or provide a rough example of how to have the random function only be generated from available schedule dates?

I imagine using a simple if statement... something like if([rand formula result]=[scheduled work day],[rand formula result],[run this again]).

I will make a quick spreadsheet for shift tracking, I just don't know what that will look like at this stage.

I appreciate the feedback.

Answer 4

From you description, you may use index& match or xlookup to get next available day of that shift if you have a list of date for each shift.

Could you share a test file with some dummy data in it? Then I can have a try on it.

For sharing file, you may upload it to OneDrive or any other cloud drive and then post the link here. *Please make sure you have removed any sensitive or private information in the sample file before uploading.

Share via

Help with random scheduling

4 answers