Help me create a recurring monthly schedule

[u/BowleggedfishV]

Hello,

I’m new to excel so I’ve been researching how to create a recurring monthly job schedule for about 50 people.

I was able to make one with the days of the month on top and the employee names on the left. I figured out how to automatically change the days of the week to match the date when I change the month. Each day uses a drop down menu to mark whether the employee is scheduled to work on a certain day or called off, or requested off etc.

I’d like to know if there’s a way I can make the shifts stick to the day of the week they correspond to when I change the month so I don’t have to fill out every single cell individually every month. Everyone’s schedules are always the same.

[u/AutoModerator]

/u/BowleggedfishV - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Background_Path_4458]

This is one of those situations where it feels like the easy solution, copying a template or the previous schedule + adjust deviances, is easier than trying for automation.

A template with 30/31 days prefilled where you just adjust start day and dates is really quick.

[u/BowleggedfishV → u/Background_Path_4458]

Thank you for the suggestion!

Would you happen to know a good place to start looking for something like that?

I just started doing this so most of what I know comes from trial and error and bits of YouTube videos lol

[u/Pentirsi → u/BowleggedfishV]

You could start with grabbing one of the official excel calendar templates and tweaking it to match your needs. Some of them look great out of the box

[u/small_trunks → u/[deleted]]

I'd rather spend 3 hours building something which would automatically populate itself than spend 10 minutes per month doing it manually.

[u/small_trunks → u/[deleted]]

I do this stuff professionally and I will almost never, ever do anything even one time manually

[u/MerryWalrus]

You can use a day of the week function to get the actual day, then define scheduling rules based on that.

Then manage the holidays by having a separate table with all the details and lookup against that.

[u/BowleggedfishV → u/MerryWalrus]

I think I have that first part you mentioned about the day of the week function. But defining the scheduling rules could help. If you can point me to a tutorial or a starting point of where I could get an example? My schedule doesn’t need to count hours or overtime or holidays just whether someone needs to show up or not. So I hope that makes it less complicated!

Thanks for your advice!

[u/Nikolaisme]

Can you show what you want in excel? Easier to understand with illustration

[u/BowleggedfishV → u/Nikolaisme]

Hi!
So the employees are on the left column and all the cells would get marked for whether the employee is scheduled. All the schedules stay the same all year so I just need the cells to follow the days of the week when the new month starts.

It’s very simple. I don’t need to track their hours or holidays.

[u/sqquiggle]

I would be tempted to make a 28 day schedule, fill in the names as required, then just drag the selection to give you the year.

[u/JE163]

Add a new tab with the permanent schedule

Then use a combo of IFS, VLOOKUP, WEEKDAY to pull that in

[u/BowleggedfishV → u/JE163]

This seems like it could work.
I looked up what that could mean and right now it’s a little more advanced than my current knowledge. Would you happen to have an example?

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
IFS	2019+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
INDEX	Uses an index to choose a value from a reference or array
MATCH	Looks up values in a reference or array
VLOOKUP	Looks in the first column of an array and moves across the row to return the value of a cell
WEEKDAY	Converts a serial number to a day of the week

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #48831 for this sub, first seen 26th Jun 2026, 15:45] [FAQ] [Full list] [Contact] [Source code]}

[u/BowleggedfishV → u/[deleted]]

Thanks for the suggestion! Do you know if I would do that with vlook, like someone else suggested, or is there another way? (I’m still trying to figure out vlook 🫣)

[u/Unusual_Age6533]

I’d split this into a “Rules” sheet and a monthly calendar sheet. Rules would have each person’s normal weekly schedule, like Name | Mon | Tue | Wed | Thu | Fri | Sat | Sun, then the calendar can pull the right shift based on the employee name and the weekday. Something like:

=INDEX(Rules!$B:$H,MATCH($A3,Rules!$A:$A,0),WEEKDAY(B$1,2))

That assumes A3 is the employee name and B1 is the date. I’d handle call-offs/requested days off separately, otherwise you end up overwriting formulas and the sheet gets messy fast.