Hi:
Here's the output I'm looking for:
- User types in mm/dd/yy into a box (based on client information)
- If day = 1-12 then show 16th of that month
ex: 6/1 shows 6/16; 6/2 shows 6/16; and so on to 6/12 shows 6/16
nice to have: if 16th is a sat/sun then show the next Monday date
- If day = 13-28 (or 3 days before month end in case of Feb) then show 1st of next month
ex: 6/13 shows 7/1; 6/14 shows 7/1; etc.
- if day = 29-31 (or last 3 days of month) then show 16th of next month
ex: 6/29 shows 7/16; 6/30 shows 7/16; then following to July would go back to bullet one: 7/1 would show 7/16
FOR ALL 3 SCENARIOS: If in December and date is 13th or later then year has to flip as well. If this is not possible, I can restrict to just mm/dd but would like to have year if possible.
I've got this formula which works except for the 3rd bullet (last 3 days) and cannot figure out how to do a 3rd else statement or fix it.
=DATE(YEAR(A1),IF(DAY(A1)<13,MONTH(A1),MONTH(A1)+1),IF(DAY(A1)<13,15,1))
Help please?
Bookmarks