CODEWITHSUNDEEP
excel
prino
prino

Reputation: 39

Find first date after today that matches a day-number and weekday

Given any date, how do I find the first month and year in the future where the 31st of that month falls on a Wednesday, in other words, for today, 11 November 2020, I would like to see (31) March 2021. [Updated: Returned month should be March 2021]

Upvotes: 0

Views: 87

Answers (2)

chris neilsen
chris neilsen

Reputation: 53136

If you have Excel 365 then

=LET(DayInMonth, DATE(YEAR($A$2),MONTH($A$2)+SEQUENCE(336,,0,1),$B$2),
     DOW,        WEEKDAY(DayInMonth),
     IsMatch,    (DAY(DayInMonth)=$B$2)*(DOW=$C$2)*(DayInMonth>=$A$2),
    XLOOKUP(1,IsMatch,DayInMonth,,0,1))

How it works:

  1. Parameters are on the sheet (you could hard code these into the formula if you wish) A2 is the start date (could be formula =TODAY()) B2 is the day number in the month C2 is the day of week code (1..7 = Sunday..Saturday)
  2. The calander repeats every 28 years. So only need to consider 12*28 = 336 months
  3. DayInMonth is the specified date in each of the next 336 months, including this month. Note: if a month does not have the specified day (eg 31 September does'nt exist)
  4. DOW is the day of Week code for those dates
  5. IsMatch is 1 if calculated DayInMonth matches specified DayInMonth (this excluded non-existant dates) AND Calculated DOW matches specified DOW AND calculated date is on or after start date
  6. XLOOKUP returns the date of the first 1 in the IsMatch array

Upvotes: 0

leal32b
leal32b

Reputation: 394

@prino,

You could make use of Excel's Iterative Calculation, setting Maximum Iterations to 1000.

In the example below, A1 is the given date and B1 receives the result (put the formula in B1):

=IF(B1=0,A1)+B1+IF(AND(WEEKDAY(B1,1)=4,DAY(B1)=31),0,1)

Upvotes: 0

Related Questions