20 Days into Prior Month Formula

[itsmekarak]

Hello everyone,

I'm looking for a formula that will give me a date of 20 days into the last month and base it on the computer systems date. For example, today is November 3rd. I would like October 20th to be the date that is returned.

I've been using =DATE(YEAR(TODAY()),MONTH(TODAY()),-1) but that only shows the last day of the previous month.

I can't use a basic =A1-14 days to get the date because I need this formula to work at any point in the month. So even on November 30th, I need to enter the formula and see October 20th as the result.

Any help you can give is greatly appreciated!

 

[wideboydixon]

Try this:

=EOMONTH(TODAY(),-2)+20

WBD

 

[dssJones]

Hello everyone,

I'm looking for a formula that will give me a date of 20 days into the last month and base it on the computer systems date. For example, today is November 3rd. I would like October 20th to be the date that is returned.

I've been using =DATE(YEAR(TODAY()),MONTH(TODAY()),-1) but that only shows the last day of the previous month.

I can't use a basic =A1-14 days to get the date because I need this formula to work at any point in the month. So even on November 30th, I need to enter the formula and see October 20th as the result.

Any help you can give is greatly appreciated!

If I understood your question correctly, then you are on the right track!
You can get the 20th day of THIS month with:
=DATE(YEAR(TODAY()), MONTH(TODAY()), 20)

To get the 20th date of Last Month then just do this
EDATE(DATE(YEAR(TODAY()), MONTH(TODAY()), 20), -1)

Change the -1 to -2 to get the 20th of 2 months ago, and so on...

 

[itsmekarak]

Fantastic! Exactly what I was looking for. Thank you kindly for your help!