Rounding up or down to a specific date

[ajh19742000]

In excel I have a spread sheet that I use to calculate payment dates for students doing standing orders. I enter the date the student enrolled and would like it to calculate the start date of the standing order. All standing orders are paid on the 15th of each month. For example if a student enrolled on the 5th September it must show the 15th October as the payment date, if enrolled on the 5th October must show 15th November. If they enrol earlier than the 5th it must show the 15th of the same month. Hope that makes sense! Any ideas?

[arthurbr]

Hi and welcome to the board

And if they enroll between the 15th end the end of the month, what happens?

[dilipandey]

Hi ajh,

See the attachment and let us know if this is what you are looking for.

Regards,
DILIPandey

<click on below 'star' if this helps>

[martindwilson]

with enrol date in a2
=IF(DAY(A2)<5,EOMONTH(A2,-1)+15,EOMONTH(A2,0)+15)

[ajh19742000]

Hi DILIPandey

Thats great and just what I'm looking for.
Many Thanks
Andy

[dilipandey]

you are welcome Andy..

Cheers

Regards,
DILIPandey

<click on below 'star' if this helps>

[daddylonglegs]

You could get the same result with this formula

=EOMONTH(A2-4,0)+15