SOLVED

New Contributor

# If date is a specific day, then add a number of days

Hi folks,

Trying to work out a formula for a weekly updating date field.

We have 6 regular meetings on different days of the week, and I want it to show the date for next week.

I have todays date updating (c1)

I have a formula to tell me what 'day' todays date is. (b1)

In each column for the separate meetings, how do I do

Meeting 1 - Mondays; Meeting 2, Tuesdays

"If B1 = monday, add '7' days (to C1), if B1 = Tuesday, add 8 days (to C1) and so on...."

Thanks!

8 Replies

# Re: If date is a specific day, then add a number of days

In B4 then copy right:

``=\$C1-WEEKDAY(\$C1,3) + COLUMNS(\$A:G)``

# Re: If date is a specific day, then add a number of days

Sorry I may not have explained it well.

Each meeting is on a specific  day of the week, so the date needs to change based on that day.

I don't think your formula quite captures that?

# Re: If date is a specific day, then add a number of days

Still unclear to me. Your picture shows a meeting on Fri that's on 31/07 THIS WEEK (the 31st of July isn't a Friday), then 2 meetings on Weds with 2 different date THIS WEEK...

Suggestion: upload another picture with the actual/accurate dates for THIS WEEK + the dates you expect for NEXT WEEK

# Re: If date is a specific day, then add a number of days

The dates were in there based on your formula.  Now it shows

B1 = todays day

C1 -  todays date

E1-J1 i've manually inputted dates for this week however would like these to manually update based on 'todays date'

E2-J1 i've manually inputted dates for NEXT week however would like these to manually update based on 'todays date'

If we can't do a 'this week /next week', perhaps we could could simply do 'the next meeting date (base doff todays date)'

i.e. if B1 = friday, and J1 = friday, then you simply add 7 days to todays date. ( i feel like it's an 'IFS' function but unsure how to combine them...

Thanks!

best response confirmed by lisab705 (New Contributor)
Solution

# Re: If date is a specific day, then add a number of days

in E1 and copy right until J1:

``=\$C1-WEEKDAY(\$C1,3)+MATCH(E3, {"Sun","Mon","Tue","Wed","Thu","Fri","Sat"}, 0)-2``

in E2 and copy right until J2:

``=E1+7``

Corresponding sample attached

# Re: If date is a specific day, then add a number of days

Marking solution (link at the bottom of each reply you get) isn't mandatory but helps those who search...

Forgot to mention, if you run Excel 2021 or 365 clear the content of E1:J2:

then in E1:

``````=LET(
ThisWeek, \$C1-WEEKDAY(\$C1,3)+XMATCH(E3:J3, {"Sun","Mon","Tue","Wed","Thu","Fri","Sat"})-2,
CHOOSE({1;2}, ThisWeek, ThisWeek+7)
)``````

# Re: If date is a specific day, then add a number of days

Amazing job - i would never have figured that out! - saves me a bit of time each week now! Thanks so much.

# Re: If date is a specific day, then add a number of days

Glad I could help & Thanks for providing feedback