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

jag108

jag108

Active Member
Joined
May 14, 2002
Messages
433
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: 40

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
T202007a.xlsm
DEFG
2707-Jul-20Sun 02-Aug-20
3
2a
Cell Formulas
RangeFormula
F2F2=TODAY()
G2G2=IF(EOMONTH(F2,-1)+7-WEEKDAY(EOMONTH(F2,-1),2)>F2,EOMONTH(F2,-1)+7-WEEKDAY(EOMONTH(F2,-1),2),EOMONTH(F2,0)+7-WEEKDAY(EOMONTH(F2,0),2))
 
Upvote 0
@jag108

  • I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

  • Also, investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

  • Since your Current Date column is just a Date, not including Time, then I suggest that you use =TODAY() which gives the date, rather than =NOW() which gives date and time.

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

jag108 2020-07-08 1.xlsm
DEFGH
1DayOccurrenceCurrent DateNNext Due Date
2718/07/2020Sun 2/08/2020
3718/07/2020Sun 2/08/2020
4718/07/2020Sun 2/08/2020
5718/07/2020Sun 2/08/2020
6418/07/2020Thu 6/08/2020
7418/07/2020Thu 6/08/2020
8418/07/2020Thu 6/08/2020
9748/07/2020Sun 26/07/2020
10748/07/2020Sun 26/07/2020
11748/07/2020Sun 26/07/2020
Sheet1
Cell Formulas
RangeFormula
F2:F11F2=TODAY()
H2:H11H2=INDEX(FILTER(SEQUENCE(31,,[@[Current Date]]+1),(WEEKDAY(SEQUENCE(31,,[@[Current Date]]+1),2)=[@Day])*(ABS([@Occurrence]*7-3-DAY(SEQUENCE(31,,[@[Current Date]]+1)))<4)),1)
 
Upvote 0
26Aug19.xlsx
CDEFG
1DayOccurrenceCurrent DateDue Date
21st Sunday718-Jul-202-Aug-20
31st Tues218-Jul-204-Aug-20
41st Thurs418-Jul-206-Aug-20
54th Sunday748-Jul-2026-Jul-20
63rd Friday538-Jul-2017-Jul-20
72nd Saturday628-Jul-2011-Jul-20
84th Wed348-Jul-2022-Jul-20
91st Wed318-Jul-205-Aug-20
104th Mon148-Jul-2027-Jul-20
Sheet51
Cell Formulas
RangeFormula
G2:G10G2=INT((EOMONTH(F2,--(INT((F2-DAY(F2)+7-MOD(D2+1,7))/7)*7+MOD(D2+1,7)+(E2-1)*7<F2)-1)+7-MOD(D2+1,7))/7)*7+MOD(D2+1,7)+(E2-1)*7
 
Upvote 0
Peter_SSs said:
@jag108

  • I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

  • Also, investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

  • Since your Current Date column is just a Date, not including Time, then I suggest that you use =TODAY() which gives the date, rather than =NOW() which gives date and time.

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

jag108 2020-07-08 1.xlsm
DEFGH
1DayOccurrenceCurrent DateNNext Due Date
2718/07/2020Sun 2/08/2020
3718/07/2020Sun 2/08/2020
4718/07/2020Sun 2/08/2020
5718/07/2020Sun 2/08/2020
6418/07/2020Thu 6/08/2020
7418/07/2020Thu 6/08/2020
8418/07/2020Thu 6/08/2020
9748/07/2020Sun 26/07/2020
10748/07/2020Sun 26/07/2020
11748/07/2020Sun 26/07/2020
Sheet1
Cell Formulas
RangeFormula
F2:F11F2=TODAY()
H2:H11H2=INDEX(FILTER(SEQUENCE(31,,[@[Current Date]]+1),(WEEKDAY(SEQUENCE(31,,[@[Current Date]]+1),2)=[@Day])*(ABS([@Occurrence]*7-3-DAY(SEQUENCE(31,,[@[Current Date]]+1)))<4)),1)
Click to expand...
Dave Patton said:
T202007a.xlsm
DEFG
2707-Jul-20Sun 02-Aug-20
3
2a
Cell Formulas
RangeFormula
F2F2=TODAY()
G2G2=IF(EOMONTH(F2,-1)+7-WEEKDAY(EOMONTH(F2,-1),2)>F2,EOMONTH(F2,-1)+7-WEEKDAY(EOMONTH(F2,-1),2),EOMONTH(F2,0)+7-WEEKDAY(EOMONTH(F2,0),2))
Click to expand...

APG_CLAS.xlsm
BCDEFH
20APG-Prod-FirstSun-2amTo4amFirst Sun7109/07/202005/07/2020
21APG-Prod-FirstSun-2amTo4amFirst Sun7109/07/202005/07/2020
22APG-Prod-FirstSun-2amTo4amFirst Sun7109/07/202005/07/2020
23APG-Prod-FirstSun-2amTo4amFirst Sun7109/07/202005/07/2020
24APG-Prod-FirstSun-2amTo4amFirst Sun7109/07/202005/07/2020
25APG-Prod-FirstWed-2amTo3amFirst Wed4109/07/202005/07/2020
26APG-Prod-FirstWed-2amTo3amFirst Wed4109/07/202005/07/2020
27APG-Prod-FirstWed-2amTo3amFirst Wed4109/07/202005/07/2020
28APG-Prod-FourthSun-0amTo2amFourth Sun7409/07/202026/07/2020
29APG-Prod-FourthSun-2amTo4amFourth Sun7409/07/202026/07/2020
30APG-Prod-FourthSun-2amTo4amFourth Sun7409/07/202026/07/2020
Sheet1
Cell Formulas
RangeFormula
F20:F30F20=TODAY()
H28:H30,H20:H23H20=F20-DAY(F20)+1+(E20)*7-WEEKDAY(F20-DAY(F20)+D20)
H24:H26H24=F24-DAY(F24)+1+(E24)*7-WEEKDAY(F24-DAY(F24)+7)
H27H27=F27-DAY(F27)+1+(E27)*7-WEEKDAY(F27-DAY(F27)+D28)


Thank you for your reply. I do not understand hoe this formula works to be able to adjust it to find the correct dates, as it is close but only works for the first Sunday, it is not taking into account the possibility of the first Friday, or the third Wednesday etc.
That is why I had the two columns with the Day we need and the occurance in the month.
Column D is for the day of week we are looking for i.e Sunday, Wednesday etc, and Column E is the occurance i.e. first, third etc.

thank you for your time on this.
 
Upvote 0
Your post with formula copied down and repeated information deleted is shown below.
Please advise what is correct and what you require. Column H and I have new formulas.
What are the actual rules for the dates that you want?
If this suggestion doesn't help, provide more information and relevant examples complete with expected results.
You initially stated "
So for the example below i would like to see 02/08/2020 not 05/07/2020

T202007a.xlsm
BCDEFGHI
2APG-Prod-FirstSun-2amTo4amFirst Sun71Wed 08-Jul-20Sun 05-Jul-20Sun 05-Jul-20Sun 02-Aug-20
3APG-Prod-FirstWed-2amTo3amFirst Wed31Wed 08-Jul-20Thu 02-Jul-20Wed 01-Jul-20Wed 05-Aug-20
4APG-Prod-FourthSun-0amTo2amFourth Sun74Wed 08-Jul-20Sun 26-Jul-20Sun 26-Jul-20Sun 23-Aug-20
5
2a_
Cell Formulas
RangeFormula
F2:F4F2=TODAY()
G2,G4G2=$F$2-DAY($F$2)+1+(E2)*7-WEEKDAY($F$2-DAY($F$2)+D2)
H2:H4H2=WORKDAY.INTL(F2-DAY(F2),E2,REPLACE("1111111",D2,1,0))
I2:I4I2=WORKDAY.INTL(EOMONTH(F2,0),E2,REPLACE("1111111",D2,1,0))
G3G3=$F$2-DAY($F$2)+1+E3*7-WEEKDAY($F$2-DAY($F$2)+D3)
 
Upvote 0
I like the fact that "Amit Tandon" has used only a single formula - I have modified it to have only one occurrence of present date and it worked flawlessly, well done!
 
Upvote 0
Peter_SSs said:
@jag108

  • I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

  • Also, investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

  • Since your Current Date column is just a Date, not including Time, then I suggest that you use =TODAY() which gives the date, rather than =NOW() which gives date and time.

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

jag108 2020-07-08 1.xlsm
DEFGH
1DayOccurrenceCurrent DateNNext Due Date
2718/07/2020Sun 2/08/2020
3718/07/2020Sun 2/08/2020
4718/07/2020Sun 2/08/2020
5718/07/2020Sun 2/08/2020
6418/07/2020Thu 6/08/2020
7418/07/2020Thu 6/08/2020
8418/07/2020Thu 6/08/2020
9748/07/2020Sun 26/07/2020
10748/07/2020Sun 26/07/2020
11748/07/2020Sun 26/07/2020
Sheet1
Cell Formulas
RangeFormula
F2:F11F2=TODAY()
H2:H11H2=INDEX(FILTER(SEQUENCE(31,,[@[Current Date]]+1),(WEEKDAY(SEQUENCE(31,,[@[Current Date]]+1),2)=[@Day])*(ABS([@Occurrence]*7-3-DAY(SEQUENCE(31,,[@[Current Date]]+1)))<4)),1)
Click to expand...


Hi There Peter_Sss,

I tried to get this to work, but the table references would not resolve. If you could kindly walk me through the diferent calculation steps to help me understand what needs to change to get this to work, thanks.
 
Upvote 0
jag108 said:
Hi There Peter_Sss,

I tried to get this to work, but the table references would not resolve. If you could kindly walk me through the diferent calculation steps to help me understand what needs to change to get this to work, thanks.
Click to expand...
I take it then that your version of Excel 365 does have the FILTER & SEQUENCE functions? (otherwise you would have a #NAME? error in the formula cells)

The table references in the formula have to exactly match the headings in the relevant columns in your table. I used the headings as best I could read them from your image in post #1. In case I did not get them exactly right ..
Current Date in my formula should be the heading from column F in your image
Day in my formula should be the heading from column D in your image
Occurrence in my formula should be the heading from column E in your image
 
Upvote 0
Peter_SSs said:
I take it then that your version of Excel 365 does have the FILTER & SEQUENCE functions? (otherwise you would have a #NAME? error in the formula cells)

The table references in the formula have to exactly match the headings in the relevant columns in your table. I used the headings as best I could read them from your image in post #1. In case I did not get them exactly right ..
Current Date in my formula should be the heading from column F in your image
Day in my formula should be the heading from column D in your image
Occurrence in my formula should be the heading from column E in your image
Click to expand...

Thanks for the definitions.
But according to sources on the Internet, the filter and sequence functions are not present in all versions of Office 365, and only then seems to be a Beta function, so I obviously have been overlooked for these functions.

But now worries, I appreciate your help on this, thank you. But one other MrExcel user has resolved my issue.
 

Attachments

  • office_version.jpg
    office_version.jpg
    134.1 KB · Views: 11
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
lookupifs?? lookup value so long as date value is greater than specified date
Replies
5
Views
120
Peter_SSs
Peter_SSs
K
  • Question Question
Copy data from rows with the dates (Monday to Sunday)
Replies
1
Views
71
K_Stevs1
K
Sandeep Warrier
  • Question Question
Calculate No. of Working Days based on Shift Pattern
Replies
2
Views
155
Tetra201
Tetra201
john Smith72
  • Solved
count days between dates for each month by drop list
Replies
9
Views
380
Tetra201
Tetra201
D
  • Solved
IF, IF(And(countif)) formula not working correctly
Replies
5
Views
488
SanjayGMusafir
SanjayGMusafir

Forum statistics

Threads
1,224,829
Messages
6,181,222
Members
453,024
Latest member
Wingit77

Share this page

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
Back
Top