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.
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 |
Code:
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"))