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

[lisab705]

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!

[#2]

Hi @lisab705 

In B4 then copy right:

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

[#3]

@L z. 

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?

[#4]

@lisab705 

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

[#5]

@L z. 

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!

[#6 · Accepted solution]

@lisab705 

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

[#7]

@lisab705 

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)
)

[#8]

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

[#9]

Glad I could help & Thanks for providing feedback