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

lisab705's avatar
lisab705
Copper Contributor
Jul 26, 2022

Lorenzo 

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!

 

 

 

Lorenzo's avatar
Lorenzo
Silver Contributor
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

Resources