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

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

    • lisab705's avatar
      lisab705
      Copper Contributor
      Jul 26, 2022

      Lorenzo 

      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?

       

       

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Jul 26, 2022

        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

Resources