I’m trying to set up a smart schedule in Google Sheets. My goal is to make the cells change automatically based on certain rules. Here’s what I want to do:
For rows 8 to 12 (Employee 1 to 5), I need the cells with ‘A’ or ‘A-10’ to change:
If it’s between the 1st and 15th of the month, they should become ‘Aa’ or ‘Aa-10’
If it’s from the 16th onwards, they should turn into ‘Ap’ or ‘Ap-10’
The same thing should happen for ‘R’ and ‘I’ shifts too.
I’ve tried a few things but can’t figure out how to make it work. Does anyone know how to set this up? It would save me a ton of time if I could get this working. Thanks for any help!
I’ve tackled similar challenges in my work scheduling sheets. One approach that’s worked well for me is using custom formatting rules combined with conditional formulas. Here’s what I’d suggest:
First, set up a helper column that calculates the current day of the month. Then, use conditional formatting to apply different colors or styles based on the date range.
For the actual cell content, you could use a formula like this:
Repeat this for ‘R’ and ‘I’ shifts as well. The beauty of this method is that it keeps your original data intact while displaying the modified version. It’s been a real time-saver in my experience.
Remember to adjust cell references as needed. Hope this helps!
I’ve implemented a similar system in my work schedule spreadsheet. The key is using a combination of IF statements and DATE functions. Here’s a general formula structure you could adapt:
You’d need to adjust cell references and potentially add more conditions. Remember to apply this to the relevant range (A8:A12 in your case). For automatic updates, you might need to set up a time-driven trigger using Google Apps Script to refresh the sheet daily.