Forum Discussion

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

Formulas and Functions

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
SAMPLE_lisab705.xlsx16 KB

Silver Contributor

Jul 25, 2022

In B4 then copy right:

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

SAMPLE_lisab705.xlsx16 KB

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

Resources