Bi-Weekly Date Calculation

[KGee]

I am trying to fix formulas I use to calculate a bi-weekly date. The periods are 14 days long and end on a Saturday. The week1 and week2 columns will always be populated for each month. The week3 column should only be populated if the date falls within the current month. The week1 formula should reference the week3 formula from the previous month/row, and when there is no date in week3, it should reference the week2 date from the previous month/row. The week2 formula can always look at week1 from the same month/row.

Below is a sample of the desired output and the formulas I currently have. I need an if statement for week1 and week3 that I can't manage to sort out.

MONTH	WEEK1	WEEK2	WEEK3
2020.JAN	2020.JAN.11	2020.JAN.25
2020.FEB	2020.FEB.08	2020.FEB.22
2020.MAR	2020.MAR.07	2020.MAR.21
2020.APR	2020.APR.04	2020.APR.18
2020.MAY	2020.MAY.02	2020.MAY.16	2020.MAY.30
2020.JUN	2020.JUN.13	2020.JUN.27
2020.JUL	2020.JUL.11	2020.JUL.25

WEEK1=UPPER(TEXT(DATE(LEFT(Y2,4),TEXT(MONTH(DATEVALUE(MID(Y2,6,3)&" 1")),"00"),RIGHT(Y2,2))+14,"YYYY.MMM.DD"))
WEEK2=UPPER(TEXT(DATE(LEFT(W3,4),TEXT(MONTH(DATEVALUE(MID(W3,6,3)&" 1")),"00"),RIGHT(W3,2))+14,"YYYY.MMM.DD"))

 

[Dave Patton]

Custom format the dates to your preference.

T202002a.xlsm
	C	D	E	F
2	MONTH	WEEK1	WEEK2	WEEK3
3	2020.01	Sat Jan 11 2020	Sat Jan 25 2020	0
4	2020.02	Sat Feb 08 2020	Sat Feb 22 2020	0
5	2020.03	Sat Mar 07 2020	Sat Mar 21 2020	0
6	2020.04	Sat Apr 04 2020	Sat Apr 18 2020	0
7	2020.05	Sat May 02 2020	Sat May 16 2020	Sat May 30 2020
8	2020.06	Sat Jun 13 2020	Sat Jun 27 2020	0
9	2020.07
4b

Cell Formulas
Range	Formula
D3	D3	=WORKDAY.INTL(C3+7-1,1,"1111101")
E3:E8	E3	=D3+14
F3:F8	F3	=IF(MONTH(E3+14)>MONTH(C3),0,E3+14)
D4:D8	D4	=IF(F3=0,E3+14,F3+14)

 

[Dave Patton]

or
=IF(MONTH(E3+14)>MONTH(C3),"",E3+14)
=IF(F3="",E3+14,F3+14)

 

[KGee]

Thanks Dave. I was able to sort it out in the format I needed.

WEEK1=IF(Y45="",UPPER(TEXT(DATE(LEFT(X45,4),TEXT(MONTH(DATEVALUE(MID(X45,6,3)&" 1")),"00"),RIGHT(X45,2))+14,"YYYY.MMM.DD")),UPPER(TEXT(DATE(LEFT(Y45,4),TEXT(MONTH(DATEVALUE(MID(Y45,6,3)&" 1")),"00"),RIGHT(Y45,2))+14,"YYYY.MMM.DD")))
WEEK2=UPPER(TEXT(DATE(LEFT(W46,4),TEXT(MONTH(DATEVALUE(MID(W46,6,3)&" 1")),"00"),RIGHT(W46,2))+14,"YYYY.MMM.DD"))
WEEK3=IF(DATE(LEFT(X46,4),TEXT(MONTH(DATEVALUE(MID(X46,6,3)&" 1")),"00"),RIGHT(X46,2))+14>EOMONTH(DATEVALUE(RIGHT(A46,3)&LEFT(A46,4)),0),"",UPPER(TEXT(DATE(LEFT(X46,4),TEXT(MONTH(DATEVALUE(MID(X46,6,3)&" 1")),"00"),RIGHT(X46,2))+14,"YYYY.MMM.DD")))