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

jag108

Active Member
Joined
May 14, 2002
Messages
429
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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-2amTo4amFirst Sun
7​
1​
08/07/2020​
05/07/2020​

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

APG-Prod-FourthSun-0amTo2amFourth Sun
7​
4​
08/07/2020​
26/07/2020​


Thanks Everyone.
 

Attachments

  • ExcelFormula.jpg
    ExcelFormula.jpg
    108.1 KB · Views: 8

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,383
Office Version
  1. 365
Platform
  1. Windows
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. :)
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Watch MrExcel Video

Forum statistics

Threads
1,127,151
Messages
5,623,058
Members
415,949
Latest member
mcrandall99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top