Calculating A Date One Month In The Past

[james.waller]

Hi,
I am seeking a formula for cell B1 that will calculate the date one month prior to the date in cell A1. This becomes tricky because obviously not all months have the same number of days (February has 28, whilst April, June, September, November all have 30). If the date in cell A1 was the 31st March, many formulas will return the date 31st February in cell B1 (a non-existent date). In this instance I would like cell B1 to display 28th February.

Many thanks in advance for any help received,
James

 

[c_m_s_jr]

=date(year(a1),month(a1)-1,day(a1))

 

[c_m_s_jr]

Actually that won't work:

Trying EDATE(A1,-1)

If you have a version prior to 2007 you need to install the Analysis TookPak

 

[james.waller]

Your initial suggestion did not address the 28th February problem. If the date in cell A1 reads the 31st March then the result in B1 is the 31st February (displayed as 3rd March).

However, after installing the toolpack (I'm using Excel 2000), your second solution worked perfectly. If I e-mail the spreadsheet to someone without the toolpack installed will this affect their ability to view the result?

 

[c_m_s_jr]

I can't recall if it will or not. I believe if someone does not have the toolpak installed it will not recalculate unless a change is made to the cell or cells that are impacted by the formula.

 

[james.waller]

I just checked, your advice is correct. The formula will continue to work with toolpack uninstalled until someone changes the contents of a data cell feeding the formula.

Thanks for all your help,
James