# formula for recurring Nth day of the month

1. ## formula for recurring Nth day of the month

I have certain date in certain cell. I need a formula that can automatically change the month when the month changes. E.g. I have entered 20th Apr. 2017 in a cell and I want that this 20th Apr. 2017 becomes 20th May, 2017 when May starts. The formula should not have to refer to any kind of reference cell/range. It should be only a formula that I could put in a cell for Nth day of the month.

Thanks  Register To Reply

2. ## Re: formula for recurring Nth day of the month

Try this:

=DATE(YEAR(TODAY()),MONTH(TODAY()),20)  Register To Reply

3. ## Re: formula for recurring Nth day of the month  Register To Reply

4. ## Re: formula for recurring Nth day of the month

One way:

=--TEXT(TODAY(),"yyyy/mm/\2\0")  Register To Reply

5. ## Re: formula for recurring Nth day of the month

Dear all,

Thank you so much for your guidance.

Have a nice day.

Thanks  Register To Reply

6. ## Re: formula for recurring Nth day of the month

What would be the formula a) if I want to change the date Yearly instead monthly and b) if I want to change the date after every 15 days from a given date, say 11th Apr. 2017?

Thanks  Register To Reply

7. ## Re: formula for recurring Nth day of the month

The formula I gave you can be adapted. Let's say you always want 20 April and just change the year, use this:

=DATE(YEAR(TODAY()),4,20)

If you want to change the date every 15 days, then maybe this:

=DATE(YEAR(TODAY()),MONTH(TODAY()),A1)

where A1 contains the number (e.g. 15) that you want to change the date to.

It's all quite flexible - open a new workbook and have a play with various options. Don't be afraid!   Register To Reply

8. ## Re: formula for recurring Nth day of the month

Thank you so much Ali   Register To Reply

9. ## Re: formula for recurring Nth day of the month

You're welcome!   Register To Reply