Formula to find first sunday in month, greater than current date

[jag108]

Hi There Everyone,

I have a spreadsheet with the dates that a server is supposed to be auto patched, this is defined by an AD group. I would like to identify this date so we can validate all services are running as they should to stop any outages, this will be done by setting a meeting request in Outlook based on the date returned.

I have a sheet with the name of the server, the patching date (first Monday), then I have a field with Now(), so we can calculate the next date using the Now() as the starting point.

I also have the day in a column i.e. 7 = Sunday, 4 = Wednesday etc.

=F2-DAY(F2)+1+(E2)*7-WEEKDAY(F2-DAY(F2)+D2) - Formula in H2

D2 = The day number, 7 Sunday
F2 = Now()
E2 = The occurrence i.e 1 = first day, 2 = second day etc

My problem is, if the first Sunday has already passed, it does not pick this up, it puts in a passed date.
I would like this formula to be able to identify the the date calculate has actually passed, so then present the next correct date.

So for the example below i would like to see 02/08/2020 not 05/07/2020.

APG-Prod-FirstSun-2amTo4am

First Sun

7​

1​

08/07/2020​

05/07/2020​

But for the scenario below keep the result as 26/07/2020.

APG-Prod-FourthSun-0amTo2am

Fourth Sun

7​

4​

08/07/2020​

26/07/2020​

Thanks Everyone.

 

[Peter_SSs]

But according to sources on the Internet, the filter and sequence functions are not present in all versions of Office 365,

Exactly, that's why I said

In relation to Excel versions, if you have Excel 365 with FILTER & SEQUENCE functions, ..

Microsoft's plan was to release the new functions to all Excel 365 subscribers this July. I haven't seen any update on that so hopefully you should get them soon & they may come in handy in the future.