Output dates based on current date/quarter/week

[InnovationSt]

Hi, I checked the other posts to the forum, but the tools were not specific enough.
I'm attempting to display a due date based on the current date that coincides with the next quarter or week.


Specifically, I want to know:


- How I can find the Wednesday before the next month end
- How I can find the Wednesday after the next month end


- How I can find the Wednesday before the 1st week of the next quarter
- How I can find the Wednesday after the 1st week of the next quarter


- How I can find the Wednesday before and after the nth week of the next quarter


I can't seem to be able to pinpoint the day based on the quarter and tying it to the Wednesdays before and after have proven difficult.
Thank you in advance for the assistance.

 

[RasGhul]

Hi,

Can you use this?

Book1
	A	B
1	TODAY	11-09-18
2	Wednesday before the next month end	Wednesday, October 31, 2018
3	Wednesday after the next month end	Wednesday, November 07, 2018
4	Wednesday before the 1st week of the next quarter	Wednesday, September 26, 2018
5	Wednesday after the 1st week of the next quarter	Wednesday, October 10, 2018
6	Wednesday before 9th week of the next quarter	Wednesday, November 21, 2018
7	Wednesday after 9th week of the next quarter	Wednesday, December 05, 2018
Sheet1

Cell Formulas
Range	Formula
B1	=TODAY()
B2	=DATE(YEAR(EOMONTH(TODAY(),1)),MONTH(EOMONTH(TODAY(),1)),DAY(EOMONTH(TODAY(),1))-WEEKDAY(EOMONTH(TODAY(),1))+4)
B3	=DATE(YEAR(EOMONTH(TODAY(),1)),MONTH(EOMONTH(TODAY(),1)),(DAY(EOMONTH(TODAY(),1))+7)-WEEKDAY(EOMONTH(TODAY(),1))+4)
B4	=VLOOKUP(MONTH(MONTH(TODAY())&0),Sheet2!$A$2:$E$5,5,0)
B5	=VLOOKUP(MONTH(MONTH(TODAY())&0)+1,Sheet2!$A$2:$E$5,3,0)+7
B6	=VLOOKUP(MONTH(MONTH(TODAY())&0)+1,Sheet2!$A$2:$E$5,3,0)+49
B7	=VLOOKUP(MONTH(MONTH(TODAY())&0)+1,Sheet2!$A$2:$E$5,3,0)+63

Book1
	A	B	C	D	E
1	QUARTER	START	WED	END	WED
2	1	01-01-18	Wednesday, January 03, 2018	31-03-18	Wednesday, March 28, 2018
3	2	01-04-18	Wednesday, April 04, 2018	30-06-18	Wednesday, June 27, 2018
4	3	01-07-18	Wednesday, July 04, 2018	30-09-18	Wednesday, September 26, 2018
5	4	01-10-18	Wednesday, October 03, 2018	31-12-18	Wednesday, December 26, 2018
Sheet2

Cell Formulas
Range	Formula
B2	=DATE(YEAR(TODAY()),1,1)
B3	=EOMONTH(B2,2)+1
B4	=EOMONTH(B3,2)+1
B5	=EOMONTH(B4,2)+1
C2	=DATE(YEAR(B2),MONTH(B2),DAY(B2)-WEEKDAY(B2)+4)
C3	=DATE(YEAR(B3),MONTH(B3),DAY(B3)-WEEKDAY(B3)+4)
C4	=DATE(YEAR(B4),MONTH(B4),DAY(B4)-WEEKDAY(B4)+4)
C5	=DATE(YEAR(B5),MONTH(B5),DAY(B5)-WEEKDAY(B5)+4)
D2	=EOMONTH(B2,2)
D3	=EOMONTH(B3,2)
D4	=EOMONTH(B4,2)
D5	=EOMONTH(B5,2)
E2	=IF(MONTH(D2)>6,DATE(YEAR(D2),MONTH(D2),(DAY(D2))-WEEKDAY(D2)+4)-7,DATE(YEAR(D2),MONTH(D2),(DAY(D2))-WEEKDAY(D2)+4))
E3	=IF(MONTH(D3)>6,DATE(YEAR(D3),MONTH(D3),(DAY(D3))-WEEKDAY(D3)+4)-7,DATE(YEAR(D3),MONTH(D3),(DAY(D3))-WEEKDAY(D3)+4))
E4	=IF(MONTH(D4)>6,DATE(YEAR(D4),MONTH(D4),(DAY(D4))-WEEKDAY(D4)+4)-7,DATE(YEAR(D4),MONTH(D4),(DAY(D4))-WEEKDAY(D4)+4))
E5	=IF(MONTH(D5)>6,DATE(YEAR(D5),MONTH(D5),(DAY(D5))-WEEKDAY(D5)+4)-7,DATE(YEAR(D5),MONTH(D5),(DAY(D5))-WEEKDAY(D5)+4))