Need to formula to show next payment due date, using multiple and random payment dates

[kware]

I am using Excel 2013 spreadsheet to track when payments are due to various vendors. Some vendors have 1 annual due date and some have 2-4 that do not follow an annual, bi-annual or quarterly pattern. For example, I need to make payment by the 28th of February and by the 15th of June for 1 vendor, every year. I want the ability to enter the date I mailed my last payment in 1 cell and have another cell in Excel that will tell me the date of my next payment due, in the format mm/dd/yyyy. I currently have my payment due dates cells formatted as mm/dd with a separate column for each due date.

For example, if I mailed a payment on 6/10/2018, I want the "Date Next Payment is Due" cell to calculate that the next payment due date is 2/28/2019. I have an small excerpt below that shows a couple different examples. Is there 1 formula I can use in this spreadsheet that will update the next payment due?

Thank you, in advance for your assistance

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1st Payment Due Date[/TD]
[TD]2nd Payment Due Date[/TD]
[TD]3rd Payment Due Date[/TD]
[TD]4th Payment Due date[/TD]
[TD]Date of last payment[/TD]
[TD]Date next payment is due[/TD]
[TD]comment[/TD]
[/TR]
[TR]
[TD]12/31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12/18/18[/TD]
[TD][/TD]
[TD]I want 6th column to show 12/31/2019[/TD]
[/TR]
[TR]
[TD]2/28[/TD]
[TD]6/15[/TD]
[TD][/TD]
[TD][/TD]
[TD]6/10/2018[/TD]
[TD][/TD]
[TD]I want 6th column to show 2/28/2019[/TD]
[/TR]
[TR]
[TD]1/25[/TD]
[TD]3/20[/TD]
[TD]6/15[/TD]
[TD]12/20[/TD]
[TD]6/10/2018[/TD]
[TD][/TD]
[TD]I want 6th column to show 1/25/2019[/TD]
[/TR]
</tbody>[/TABLE]

 

[michaeltsmith93]

Hi there. I'm working on a solution for you. Two questions:
1. Are you ever late on your payments?
2. Do you update the "last payment" column enough to where the oldest "last payment" value might be 12/06 from the previous year for the 3rd row of data?

 

[kware]

Hello

No, we are never late with payments. The sheet is just used to track pay dates for our records and to avoid duplicate payments or late payments. The 3rd row that you inquired about shows the correct value in column 5 because the next payment is not due until the 20th of this month. What I did do wrong on the 3rd row was what I typed in the 7th column. It should have read, "I want 6th column to show 12/20/2018". I hope that helps to clarify.

 

[kware]

Hi, I figured out a formula that populates the next due date in column 6, if I type in the Date Most Recent Taxes Paid to match the last actual payment due date. For example, I submitted the payment on 6/10/2018 to cover the payment that was due 6/15 so instead of typing 6/10 in column 5, I typed 6/15 and, using the formula I created, column 6 shows me the next payment is due 2/28. However, here is the problem with the formula I am using, even though I have the dates formatted to only use mm/dd, excel is still automatically populating the year and it can't determine the next payment due, unless I manually change the year in row 2 from 2018 to 2019. I can't find a work around for this. I think I now need to figure out how to get excel to automatically change the year on a stale date whenever the book is opened. For example, row 2 only SHOWS 2/28 but if you click on the cell then you will see year 2018. Is there a way to excel to change 2/28/2018 to 2/28/2019 whenever I open the spreadsheet since 2/28/2018 has already passed?
I am using the following formula in column 6 or column F, {=MIN(IF(E5<A5:D5,A5:D5))} and if I don't manually change the year in column A row 2 to 2019 then i get a result of 0-JAN.

 

[michaeltsmith93]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Hi there,
Sorry for the delay. I've figured out a solution. The important formula is horribly inelegant, but it accomplishes exactly what you want without having to change anything really. I've added a third row to your sheet that features 3 scheduled payments in order to reflect that possibility. I've just copied and pasted the formulas directly into here, cell by cell. The formula in Column F can be pasted into F2 and then AutoFilled down however far you want. Same with the formula in G2, which is necessary for the Column F formula to work. Columns A through D should be formatted as text, and you must use the format ("mm/dd") in these columns, making sure that you use zeros where appropriate (i.e., 06/10, not 6/10).

Basically, it's first checking to see which of the four formulas to use based on the number of payments (Column G), then it checks to see when that payment was made according to Column E. Then it checks that date against the dates in Columns A through D. It uses DATE to pull the year from whichever appropriate cell in Column J, and the month and day are pulled as text from Columns A through D using LEFT and RIGHT. As I said, horribly inelegant, yet effective.

Cell F2: =IF($G2=1,IF($E2<DATE($J$1,LEFT($A2,2),RIGHT($A2,2)),DATE($J$2,LEFT($A2,2),RIGHT($A2,2)),IF(AND($E2>DATE($J$1,LEFT($A2,2),RIGHT($A2,2)),$E2<NOW()),DATE($J$4,LEFT($A2,2),RIGHT($A2,2)),"Invalid date")),IF($G2=2,IF($E2<DATE($J$2,LEFT($A2,2),RIGHT($A2,2)),DATE($J$2,LEFT($B2,2),RIGHT($B2,2)),IF(AND($E2>DATE($J$2,LEFT($A2,2),RIGHT($A2,2)),$E2<DATE($J$2,LEFT($B2,2),RIGHT($B2,2))),DATE($J$3,LEFT($A2,2),RIGHT($A2,2)),IF(AND($E2>DATE($J$2,LEFT($B2,2),RIGHT($B2,2)),$E2<NOW()),DATE($J$3,LEFT($B2,2),RIGHT($B2,2)),"Invalid date"))),IF($G2=3,IF($E2<DATE($J$1,LEFT($C2,2),RIGHT($C2,2)),DATE($J$2,LEFT($A2,2),RIGHT($A2,2)),IF(AND($E2>DATE($J$1,LEFT($C2,2),RIGHT($C2,2)),$E2<DATE($J$2,LEFT($A2,2),RIGHT($A2,2))),DATE($J$2,LEFT($B2,2),RIGHT($B2,2)),IF(AND($E2>DATE($J$2,LEFT($A2,2),RIGHT($A2,2)),$E2<DATE($J$2,LEFT($B2,2),RIGHT($B2,2))),DATE($J$2,LEFT($C2,2),RIGHT($C2,2)),IF(AND($E2>DATE($J$2,LEFT($B2,2),RIGHT($B2,2)),$E2<DATE($J$2,LEFT($C2,2),RIGHT($C2,2))),DATE($J$3,LEFT($A2,2),RIGHT($A2,2)),IF(AND($E2>DATE($J$2,LEFT($C2,2),RIGHT($C2,2)),$E2<NOW()),DATE($J$3,LEFT($B2,2),RIGHT($B2,2)),"Invalid date"))))),IF($G2=4,IF($E2<DATE($J$1,LEFT($D2,2),RIGHT($D2,2)),DATE($J$2,LEFT($A2,2),RIGHT($A2,2)),IF(AND($E2>DATE($J$1,LEFT($D2,2),RIGHT($D2,2)),$E2<DATE($J$2,LEFT($A2,2),RIGHT($A2,2))),DATE($J$2,LEFT($B2,2),RIGHT($B2,2)),IF(AND($E2>DATE($J$2,LEFT($A2,2),RIGHT($A2,2)),$E2<DATE($J$2,LEFT($B2,2),RIGHT($B2,2))),DATE($J$2,LEFT($C2,2),RIGHT($C2,2)),IF(AND($E2>DATE($J$2,LEFT($B2,2),RIGHT($B2,2)),$E2<DATE($J$2,LEFT($C2,2),RIGHT($C2,2))),DATE($J$2,LEFT($D2,2),RIGHT($D2,2)),IF(AND($E2>DATE($J$2,LEFT($C2,2),RIGHT($C2,2)),$E2<NOW()),DATE($J$3,LEFT($A2,2),RIGHT($A2,2)),"Invalid date"))))),"Invalid # of payments"))))

<date($j$1,left($a2,2),right($a2,2)),date($j$2,left($a2,2),right($a2,2)),if(and($e2 style="font-family: Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif;"><now()),date($j$4,left($a2,2),right($a2,2)),"invalid date")),if($g2="2,IF($E2<DATE($J$2,LEFT($A2,2),RIGHT($A2,2)),DATE($J$2,LEFT($B2,2),RIGHT($B2,2)),IF(AND($E2"><date($j$2,left($b2,2),right($b2,2))),date($j$3,left($a2,2),right($a2,2)),if(and($e2><now()),date($j$3,left($b2,2),right($b2,2)),"invalid date"))),if($g2="3,IF($E2<DATE($J$1,LEFT($C2,2),RIGHT($C2,2)),DATE($J$2,LEFT($A2,2),RIGHT($A2,2)),IF(AND($E2"><date($j$2,left($a2,2),right($a2,2))),date($j$2,left($b2,2),right($b2,2)),if(and($e2><date($j$2,left($b2,2),right($b2,2))),date($j$2,left($c2,2),right($c2,2)),if(and($e2><date($j$2,left($c2,2),right($c2,2))),date($j$3,left($a2,2),right($a2,2)),if(and($e2><now()),date($j$3,left($b2,2),right($b2,2)),"invalid date"))))),if($g2="4,IF($E2<DATE($J$1,LEFT($D2,2),RIGHT($D2,2)),DATE($J$2,LEFT($A2,2),RIGHT($A2,2)),IF(AND($E2"><date($j$2,left($a2,2),right($a2,2))),date($j$2,left($b2,2),right($b2,2)),if(and($e2><date($j$2,left($b2,2),right($b2,2))),date($j$2,left($c2,2),right($c2,2)),if(and($e2><date($j$2,left($c2,2),right($c2,2))),date($j$2,left($d2,2),right($d2,2)),if(and($e2><now()),date($j$3,left($a2,2),right($a2,2)),"invalid date"))))),"invalid="" #="" of="" payments"))))<date($j$1,left($a2,2),right($a2,2)),date($j$2,left($a2,2),right($a2,2)),if(and($e2=""><now()),date($j$4,left($a2,2),right($a2,2)),"invalid date")),if($g2="2,IF($E2<DATE($J$2,LEFT($A2,2),RIGHT($A2,2)),DATE($J$2,LEFT($B2,2),RIGHT($B2,2)),IF(AND($E2"><date($j$2,left($b2,2),right($b2,2))),date($j$3,left($a2,2),right($a2,2)),if(and($e2><now()),date($j$3,left($b2,2),right($b2,2)),"invalid date"))),if($g2="3,IF($E2<DATE($J$1,LEFT($C2,2),RIGHT($C2,2)),DATE($J$2,LEFT($A2,2),RIGHT($A2,2)),IF(AND($E2"><date($j$2,left($a2,2),right($a2,2))),date($j$2,left($b2,2),right($b2,2)),if(and($e2><date($j$2,left($b2,2),right($b2,2))),date($j$2,left($c2,2),right($c2,2)),if(and($e2><date($j$2,left($c2,2),right($c2,2))),date($j$3,left($a2,2),right($a2,2)),if(and($e2><now()),date($j$3,left($b2,2),right($b2,2)),"invalid date"))))),if($g2="4,IF($E2<DATE($J$1,LEFT($D2,2),RIGHT($D2,2)),DATE($J$2,LEFT($A2,2),RIGHT($A2,2)),IF(AND($E2"><date($j$2,left($a2,2),right($a2,2))),date($j$2,left($b2,2),right($b2,2)),if(and($e2><date($j$2,left($b2,2),right($b2,2))),date($j$2,left($c2,2),right($c2,2)),if(and($e2><date($j$2,left($c2,2),right($c2,2))),date($j$2,left($d2,2),right($d2,2)),if(and($e2><now()),date($j$3,left($a2,2),right($a2,2)),"invalid date"))))),"invalid="" #="" of="" payments"))))
<date($j$1,left($a2,2),right($a2,2)),date($j$2,left($a2,2),right($a2,2)),if(and($e2><now()),date($j$4,left($a2,2),right($a2,2)),"invalid date")),if($g2="2,IF($E2<DATE($J$2,LEFT($A2,2),RIGHT($A2,2)),DATE($J$2,LEFT($B2,2),RIGHT($B2,2)),IF(AND($E2"><date($j$2,left($b2,2),right($b2,2))),date($j$3,left($a2,2),right($a2,2)),if(and($e2><now()),date($j$3,left($b2,2),right($b2,2)),"invalid date"))),if($g2="3,IF($E2<DATE($J$1,LEFT($C2,2),RIGHT($C2,2)),DATE($J$2,LEFT($A2,2),RIGHT($A2,2)),IF(AND($E2"><date($j$2,left($a2,2),right($a2,2))),date($j$2,left($b2,2),right($b2,2)),if(and($e2><date($j$2,left($b2,2),right($b2,2))),date($j$2,left($c2,2),right($c2,2)),if(and($e2><date($j$2,left($c2,2),right($c2,2))),date($j$3,left($a2,2),right($a2,2)),if(and($e2><now()),date($j$3,left($b2,2),right($b2,2)),"invalid date"))))),if($g2="4,IF($E2<DATE($J$1,LEFT($D2,2),RIGHT($D2,2)),DATE($J$2,LEFT($A2,2),RIGHT($A2,2)),IF(AND($E2"><date($j$2,left($a2,2),right($a2,2))),date($j$2,left($b2,2),right($b2,2)),if(and($e2><date($j$2,left($b2,2),right($b2,2))),date($j$2,left($c2,2),right($c2,2)),if(and($e2><date($j$2,left($c2,2),right($c2,2))),date($j$2,left($d2,2),right($d2,2)),if(and($e2><now()),date($j$3,left($a2,2),right($a2,2)),"invalid date"))))),"invalid="" #="" of="" payments"))))
<date($j$1,left($a2,2),right($a2,2)),date($j$2,left($a2,2),right($a2,2)),if(and($e2><now()),date($j$4,left($a2,2),right($a2,2)),"invalid date")),if($g2="2,IF($E2<DATE($J$2,LEFT($A2,2),RIGHT($A2,2)),DATE($J$2,LEFT($B2,2),RIGHT($B2,2)),IF(AND($E2"><date($j$2,left($b2,2),right($b2,2))),date($j$3,left($a2,2),right($a2,2)),if(and($e2><now()),date($j$3,left($b2,2),right($b2,2)),"invalid date"))),if($g2="3,IF($E2<DATE($J$1,LEFT($C2,2),RIGHT($C2,2)),DATE($J$2,LEFT($A2,2),RIGHT($A2,2)),IF(AND($E2"><date($j$2,left($a2,2),right($a2,2))),date($j$2,left($b2,2),right($b2,2)),if(and($e2><date($j$2,left($b2,2),right($b2,2))),date($j$2,left($c2,2),right($c2,2)),if(and($e2><date($j$2,left($c2,2),right($c2,2))),date($j$3,left($a2,2),right($a2,2)),if(and($e2><now()),date($j$3,left($b2,2),right($b2,2)),"invalid date"))))),if($g2="4,IF($E2<DATE($J$1,LEFT($D2,2),RIGHT($D2,2)),DATE($J$2,LEFT($A2,2),RIGHT($A2,2)),IF(AND($E2"><date($j$2,left($a2,2),right($a2,2))),date($j$2,left($b2,2),right($b2,2)),if(and($e2><date($j$2,left($b2,2),right($b2,2))),date($j$2,left($c2,2),right($c2,2)),if(and($e2><date($j$2,left($c2,2),right($c2,2))),date($j$2,left($d2,2),right($d2,2)),if(and($e2><now()),date($j$3,left($a2,2),right($a2,2)),"invalid date"))))),"invalid="" #="" of="" payments"))))
<date($j$1,left($a2,2),right($a2,2)),date($j$2,left($a2,2),right($a2,2)),if(and($e2><now()),date($j$4,left($a2,2),right($a2,2)),"invalid date")),if($g2="2,IF($E2<DATE($J$2,LEFT($A2,2),RIGHT($A2,2)),DATE($J$2,LEFT($B2,2),RIGHT($B2,2)),IF(AND($E2"><date($j$2,left($b2,2),right($b2,2))),date($j$3,left($a2,2),right($a2,2)),if(and($e2><now()),date($j$3,left($b2,2),right($b2,2)),"invalid date"))),if($g2="3,IF($E2<DATE($J$1,LEFT($C2,2),RIGHT($C2,2)),DATE($J$2,LEFT($A2,2),RIGHT($A2,2)),IF(AND($E2"><date($j$2,left($a2,2),right($a2,2))),date($j$2,left($b2,2),right($b2,2)),if(and($e2><date($j$2,left($b2,2),right($b2,2))),date($j$2,left($c2,2),right($c2,2)),if(and($e2><date($j$2,left($c2,2),right($c2,2))),date($j$3,left($a2,2),right($a2,2)),if(and($e2><now()),date($j$3,left($b2,2),right($b2,2)),"invalid date"))))),if($g2="4,IF($E2<DATE($J$1,LEFT($D2,2),RIGHT($D2,2)),DATE($J$2,LEFT($A2,2),RIGHT($A2,2)),IF(AND($E2"><date($j$2,left($a2,2),right($a2,2))),date($j$2,left($b2,2),right($b2,2)),if(and($e2><date($j$2,left($b2,2),right($b2,2))),date($j$2,left($c2,2),right($c2,2)),if(and($e2><date($j$2,left($c2,2),right($c2,2))),date($j$2,left($d2,2),right($d2,2)),if(and($e2><now()),date($j$3,left($a2,2),right($a2,2)),"invalid date"))))),"invalid="" #="" of="" payments"))))
Cell G1: Number of Payments scheduled
Cell G2: =COUNTA($A2:$D2)
Cell I1: Last Year
Cell I2: Current year
Cell I3: Next year
Cell I4: Two years
Cell J1: =YEAR(EDATE(L1,-12))
Cell J2: =YEAR(L1)
Cell J3: =YEAR(EDATE(L1,12))
Cell J4: =YEAR(EDATE(L1,24))
Cell K1: Today
Cell L1: =NOW()

Let me know if anything is buggy!</now()),date($j$3,left($a2,2),right($a2,2)),"invalid></date($j$2,left($c2,2),right($c2,2))),date($j$2,left($d2,2),right($d2,2)),if(and($e2></date($j$2,left($b2,2),right($b2,2))),date($j$2,left($c2,2),right($c2,2)),if(and($e2></date($j$2,left($a2,2),right($a2,2))),date($j$2,left($b2,2),right($b2,2)),if(and($e2></now()),date($j$3,left($b2,2),right($b2,2)),"invalid></date($j$2,left($c2,2),right($c2,2))),date($j$3,left($a2,2),right($a2,2)),if(and($e2></date($j$2,left($b2,2),right($b2,2))),date($j$2,left($c2,2),right($c2,2)),if(and($e2></date($j$2,left($a2,2),right($a2,2))),date($j$2,left($b2,2),right($b2,2)),if(and($e2></now()),date($j$3,left($b2,2),right($b2,2)),"invalid></date($j$2,left($b2,2),right($b2,2))),date($j$3,left($a2,2),right($a2,2)),if(and($e2></now()),date($j$4,left($a2,2),right($a2,2)),"invalid></date($j$1,left($a2,2),right($a2,2)),date($j$2,left($a2,2),right($a2,2)),if(and($e2></now()),date($j$3,left($a2,2),right($a2,2)),"invalid></date($j$2,left($c2,2),right($c2,2))),date($j$2,left($d2,2),right($d2,2)),if(and($e2></date($j$2,left($b2,2),right($b2,2))),date($j$2,left($c2,2),right($c2,2)),if(and($e2></date($j$2,left($a2,2),right($a2,2))),date($j$2,left($b2,2),right($b2,2)),if(and($e2></now()),date($j$3,left($b2,2),right($b2,2)),"invalid></date($j$2,left($c2,2),right($c2,2))),date($j$3,left($a2,2),right($a2,2)),if(and($e2></date($j$2,left($b2,2),right($b2,2))),date($j$2,left($c2,2),right($c2,2)),if(and($e2></date($j$2,left($a2,2),right($a2,2))),date($j$2,left($b2,2),right($b2,2)),if(and($e2></now()),date($j$3,left($b2,2),right($b2,2)),"invalid></date($j$2,left($b2,2),right($b2,2))),date($j$3,left($a2,2),right($a2,2)),if(and($e2></now()),date($j$4,left($a2,2),right($a2,2)),"invalid></date($j$1,left($a2,2),right($a2,2)),date($j$2,left($a2,2),right($a2,2)),if(and($e2></now()),date($j$3,left($a2,2),right($a2,2)),"invalid></date($j$2,left($c2,2),right($c2,2))),date($j$2,left($d2,2),right($d2,2)),if(and($e2></date($j$2,left($b2,2),right($b2,2))),date($j$2,left($c2,2),right($c2,2)),if(and($e2></date($j$2,left($a2,2),right($a2,2))),date($j$2,left($b2,2),right($b2,2)),if(and($e2></now()),date($j$3,left($b2,2),right($b2,2)),"invalid></date($j$2,left($c2,2),right($c2,2))),date($j$3,left($a2,2),right($a2,2)),if(and($e2></date($j$2,left($b2,2),right($b2,2))),date($j$2,left($c2,2),right($c2,2)),if(and($e2></date($j$2,left($a2,2),right($a2,2))),date($j$2,left($b2,2),right($b2,2)),if(and($e2></now()),date($j$3,left($b2,2),right($b2,2)),"invalid></date($j$2,left($b2,2),right($b2,2))),date($j$3,left($a2,2),right($a2,2)),if(and($e2></now()),date($j$4,left($a2,2),right($a2,2)),"invalid></date($j$1,left($a2,2),right($a2,2)),date($j$2,left($a2,2),right($a2,2)),if(and($e2></now()),date($j$3,left($a2,2),right($a2,2)),"invalid></date($j$2,left($c2,2),right($c2,2))),date($j$2,left($d2,2),right($d2,2)),if(and($e2></date($j$2,left($b2,2),right($b2,2))),date($j$2,left($c2,2),right($c2,2)),if(and($e2></date($j$2,left($a2,2),right($a2,2))),date($j$2,left($b2,2),right($b2,2)),if(and($e2></now()),date($j$3,left($b2,2),right($b2,2)),"invalid></date($j$2,left($c2,2),right($c2,2))),date($j$3,left($a2,2),right($a2,2)),if(and($e2></date($j$2,left($b2,2),right($b2,2))),date($j$2,left($c2,2),right($c2,2)),if(and($e2></date($j$2,left($a2,2),right($a2,2))),date($j$2,left($b2,2),right($b2,2)),if(and($e2></now()),date($j$3,left($b2,2),right($b2,2)),"invalid></date($j$2,left($b2,2),right($b2,2))),date($j$3,left($a2,2),right($a2,2)),if(and($e2></now()),date($j$4,left($a2,2),right($a2,2)),"invalid></now()),date($j$3,left($a2,2),right($a2,2)),"invalid></date($j$2,left($c2,2),right($c2,2))),date($j$2,left($d2,2),right($d2,2)),if(and($e2></date($j$2,left($b2,2),right($b2,2))),date($j$2,left($c2,2),right($c2,2)),if(and($e2></date($j$2,left($a2,2),right($a2,2))),date($j$2,left($b2,2),right($b2,2)),if(and($e2></now()),date($j$3,left($b2,2),right($b2,2)),"invalid></date($j$2,left($c2,2),right($c2,2))),date($j$3,left($a2,2),right($a2,2)),if(and($e2></date($j$2,left($b2,2),right($b2,2))),date($j$2,left($c2,2),right($c2,2)),if(and($e2></date($j$2,left($a2,2),right($a2,2))),date($j$2,left($b2,2),right($b2,2)),if(and($e2></now()),date($j$3,left($b2,2),right($b2,2)),"invalid></date($j$2,left($b2,2),right($b2,2))),date($j$3,left($a2,2),right($a2,2)),if(and($e2></now()),date($j$4,left($a2,2),right($a2,2)),"invalid></date($j$1,left($a2,2),right($a2,2)),date($j$2,left($a2,2),right($a2,2)),if(and($e2>[/TD]
[/TR]
</tbody>[/TABLE]

 

[michaeltsmith93]

Sorry for the huge wall of crap. MrExcel is freaking out. The F2 formula stops at "Invalid # of payments")))).

Here are the other cell values:

Cell G1: Number of Payments scheduled
Cell G2: =COUNTA($A2:$D2)
Cell I1: Last Year
Cell I2: Current year
Cell I3: Next year
Cell I4: Two years
Cell J1: =YEAR(EDATE(L1,-12))
Cell J2: =YEAR(L1)
Cell J3: =YEAR(EDATE(L1,12))
Cell J4: =YEAR(EDATE(L1,24))
Cell K1: Today
Cell L1: =NOW()