Jul 24 2022 04:48 PM
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!
Jul 24 2022 09:55 PM
Jul 25 2022 10:33 PM
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?
Jul 25 2022 11:29 PM
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
Jul 25 2022 11:40 PM
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!
Jul 26 2022 02:10 AM
Solution
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
Jul 26 2022 09:38 AM
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)
)
Jul 26 2022 04:22 PM
Jul 26 2022 08:44 PM
Jul 26 2022 02:10 AM
Solution
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