Formula for IF/THEN EOM and/or 15th of Month based on Today's Date

Hi I am new and apologize if I’m in the wrong forum or if I’m not making sense.

I am trying to make a spreadsheet that will forecast how many vacation days a person will accrue on each paycheck, which happens on the 15th and last day of each month.

Where cell A1 will be today’s date, I would like the column underneath to display the next payday, and all following for say one year. I had previously searched and found a formula that will give me the next 15th of the month but didn’t realize that this will only work if the date is earlier than the 15th.

So if my data starts in cell A2, this is the formula I have: =DATE(YEAR(A1),MONTH(A1),IF(DAY(A1)=15,0,15))
And then in cell A3 I have: =EOMONTH(A2,1)
A4: =DATE(YEAR(A3),MONTH(A3),IF(DAY(A3)=15,0,15))
A5: =EOMONTH(A1,2)

And so on…

So if A1 is June 20th, how do I get the next cell to display June 30th AND be able to display July 15th IF the user puts in July 5th the next time?

Thanks.

Okay, I tried that and it gives me the previous EOM. I put in July 3rd in A1 and A2 with your formula gives me June 30th.

THANK YOU!

That works in cell A2, now in cell A3 I want the next date to be the following payday. I copied this formula and replaced A1 with A2 and it's giving me the same date.

A1 is June 11th
A2 with this formula gives me June 15th
A3 with this formula but only changing the A1 to A2 also gives me June 15th instead of June 30th.

Change the beginning of the formula to less than 15:

=IF(DAY(A1)<15, ... etc

Hope this helps.

Pete

I apologize I am still not getting the results I want.

This is what I'm looking for. Your formula works for the first cell and the revision from Pete for the second cell, but I don't know what to change in the formula to make A3, A4, etc. to be what I want AND be able to change if today's date were after the 15th when entering this. I appreciate the help very much.

A1 = June 11th

A2 = June 15th
A3 = June 30th
A4 = July 15th
A5 = July 31st
A6 = August 15th
A7 = August 31st
etc.

Put this formula in A1:

=TODAY()

or manually enter some starting date into that cell. Then put this formula in A2:

=IF(DAY(A1)<15,DATE(YEAR(A1),MONTH(A1),15),EOMONTH(A1,0))

then copy/paste this down as far as you need to. In copying the formula, the reference to A1 should change to A2 automatically in cell A3, then to A3 in cell A4, and so on - to copy, select cell A2, click on the <Copy> icon, then move the cursor to select the cells you want to copy into (e.g. A3:A20) then press <Enter>.

Hope this helps.

Pete

AH YES FINALLY! Thank you so much to everyone. Daddylonglegs you are the best, this formula in A2 finally worked. This is great!