I am trying to make a vacation schedule starting the Monday before Memorial Day. I currently have a If statement that works but didn't know if I could make it shorter. I have tried an Index/Match and a Vlookup but I'm not getting the desired results as show in the sample. I am on a Mac in my profile but will be using this on PC with MS Office 365. I try for non array formula's just in case someone in the office tries to mess with them and don't know the Ctl+Shift+Enter. Any help would be greatly appreciated. Thank you.
J6 is the result that I am looking for.
J6 is the result that I am looking for.
| Prime Time Vacation.xlsx | ||||||
|---|---|---|---|---|---|---|
| J | K | L | M | |||
| 5 | Current Year | Memorial Day | ||||
| 6 | 19 May 2025 | 2025 | 26 May 2025 | |||
| 7 | 25 May 2026 | |||||
| 8 | 7/17/05 | 31 May 2027 | ||||
| 9 | 29 May 2028 | |||||
| 10 | 2025 | 28 May 2029 | ||||
| 11 | 27 May 2030 | |||||
| 12 | 26 May 2031 | |||||
| 13 | 31 May 2032 | |||||
| 14 | 30 May 2033 | |||||
| 15 | 29 May 2034 | |||||
| 16 | 28 May 2035 | |||||
Prime Time | ||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J6 | J6 | =IF(YEAR(L6)=YEAR(M6),M6-7,IF(YEAR(L6)=YEAR(M7),M7-7,IF(YEAR(L6)=YEAR(M8),M8-7,IF(YEAR(L6)=YEAR(M9),M9-7,IF(YEAR(L6)=YEAR(M10),M10-7,IF(YEAR(L6)=YEAR(M11),M11-7,IF(YEAR(L6)=YEAR(M12),M12-7,IF(YEAR(L6)=YEAR(M13),M13-7,IF(YEAR(L6)=YEAR(M14),M14-7,""))))))))) |
| J8 | J8 | =INDEX(YEAR(M6:M16),MATCH(YEAR(L6),YEAR(M6:M16),0)) |
| J10 | J10 | =VLOOKUP(YEAR(L6),YEAR(M6:M16),1,0) |
