Formula for weekdays of date in another cell

[Jaxi]

I have tried to find the answer but maybe I’m just not wording my query well enough to find the answer, apologies if this has already been asked.

Scenario: spreadsheet filled with birthdates, we decorate person’s office/desk the entire week of birthday, with a cupcake the day of.

There is already a formula that will adjust the year based on another cell (a1) in same spreadsheet.

Decorating days are divided between office personnel.

Looking for a formula that will enter the workdays surrounding a birthdate and adjust/change/shift as the year changes.

Example: Jane A’s birthday is 2/7/24, which is a Wednesday this year, so the formula will enter the dates for Monday, Tuesday, Thursday, and Friday in other cells of the column of personnel (Tom would be column B, Jane B. would be column C, Tony would be column D, etc.)

I need Monday the 5th to fill in under Tom (cell B2), I need Tuesday the 6th to fill in under Jane B (cell C2), I need Thursday the 8th to fill in under Tony (cell D2), etc.

However, when the year changes to next year, Jane A’s birthday will fall on Friday, so I need the dates under Tom, Jane B. & Tony to shift to Monday the 3rd, Tuesday the 4th, Wednesday the 5th, etc.

JANE A​	TOM​	JANE B​	TONY​	OP​
Birthday is on Wednesday, 7 FEB 2024 Formula =date(a1,2,7)+choose(weekday(date(a1,2,7)),1,0,0,0,0,0,-1)	Needs to display Monday, 5 FEB 2024	Needs to display Tuesday, 6 FEB 2024	Needs to display Thursday, 8 FEB 2024	Needs to display Friday, 9 FEB 2024
Birthday is on FRIDAY, 7 FEB 2025	Needs to display Monday, 3 FEB 2025	Needs to display Tuesday, 4 FEB 2025	Needs to display WEDNESDAY, 5 FEB 2025	Needs to display THURSDAY, 6 FEB 2025

Hopefully this makes sense. When the year changes the day of the week changes and the decorating person’s date will need to change.

A kind of perpetual of schedule. That’s a rudimentary example and hopefully it’s enough to explain visually what I’m looking for, if it can be done.

 

[awoohaw]

What is the normal rule for assigning people to work? What is the rule for the birthday person to work that week? Is it only restricted to the week of the birthday person? What if birthday is on a Saturday or Sunday (which week is affected)?

What is displayed in the cells for Jane A's Birthday?

 

[Jaxi]

What is the normal rule for assigning people to work? What is the rule for the birthday person to work that week? Is it only restricted to the week of the birthday person? What if birthday is on a Saturday or Sunday (which week is affected)?

What is displayed in the cells for Jane A's Birthday?

It’s an office setting, Monday thru Friday, 9-5. There are only five of us in the office, and the day of the birthday - or the Friday if the birthday falls on a weekend - we all go to lunch that day and birthday person gets a cupcake.

“Decorations” responsibility changes depending on the weekday of the birthday.

Using the table above, on Monday Tom puts confetti all over Jane A’s desk. On Tuesday Jane B adds streamers, etc.

There isn’t any set assignment other than Tom handles Monday, Jane B handles Tuesday, normally Jane A handles Wednesday but that’s her birthday so it’s the office lunch, etc.

For next year Tony’s day would shift from Thursday to Wednesday because Jane A normally handles Wednesday but since her birthday falls on the Friday next year, Tony’s & my days shift back one.

Or if the birthday falls on a Monday/Tuesday the responsibility shifts forward 1 or 2 days.

 

[awoohaw]

Well, my suggestion would be to add (7*52) to the calendar, and not 365/6. Compare the Weeknumber of that date to the weeknumber of the birthday. You may need to check the settings of the WEEKNUM function to fine tune it.

 

[Jaxi]

Well, my suggestion would be to add (7*52) to the calendar, and not 365/6. Compare the Weeknumber of that date to the weeknumber of the birthday. You may need to check the settings of the WEEKNUM function to fine tune it.

Thank you! I appreciate the quick response. I’ll give that a try.