# 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

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

Try this:

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

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

One way:

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

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

Dear all,

Thank you so much for your guidance.

Have a nice day.

Thanks

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

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!

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

Thank you so much Ali

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

You're welcome!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1