Forum Discussion

lisab705's avatar
lisab705
Copper Contributor
Jul 24, 2022
Solved

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 d...
excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Jul 26, 2022

    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

Lorenzo's avatar
Lorenzo
Silver Contributor
Jul 26, 2022

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

 

lisab705's avatar
lisab705
Copper Contributor
Jul 26, 2022
Amazing job - i would never have figured that out! - saves me a bit of time each week now! Thanks so much.
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jul 27, 2022
    Glad I could help & Thanks for providing feedback

Resources