Month

[lightbulb]

I have a column of dates, and I want a formula that will subtract one month
from that date. I can't simply subtract 30 from the date b/c sometimes that
will still be in the same month (i.e. - July 31 - 30 days= July 1)...and I
can't simply subtract 31, because seometimes that will be 2 months earlier
(i.e.- July 1 - 31=May 31). Any help?

Thanks!

 

[Fred Smith]

The previous month is:
=date(year(a1),month(a1)-1,day(a1))

Regards,
Fred

 

[Pete_UK]

Try this:

=DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))

Mind you, this will still cause some problems if you start with, say,
30th March, as 30th February does not exist. Similarly 31st July will
try to give 31st June, which will then become 1st July. What answers
do you want to see in these cases?

Hope this helps.

Pete

 

[מיכ×ל (מיקי) ×בידן]

try this: =EDATE(A1,-1)
[You must have Analysis Toolpak add-in installed in older versions than
"2007"]
Micky

 

[lightbulb]

I am working in Excel 2003

try this: =EDATE(A1,-1)
[You must have Analysis Toolpak add-in installed in older versions than
"2007"]
Micky

I have a column of dates, and I want a formula that will subtract one month
from that date. I can't simply subtract 30 from the date b/c sometimes that
will still be in the same month (i.e. - July 31 - 30 days= July 1)...and I
can't simply subtract 31, because seometimes that will be 2 months earlier
(i.e.- July 1 - 31=May 31). Any help?

Thanks!

 

[מיכ×ל (מיקי) ×בידן]

So...!?
'Tools' > 'Add-Ins > check the 'Analysis Toolpak" > 'OK'
Micky

I am working in Excel 2003

try this: =EDATE(A1,-1)
[You must have Analysis Toolpak add-in installed in older versions than
"2007"]
Micky

I have a column of dates, and I want a formula that will subtract one month
from that date. I can't simply subtract 30 from the date b/c sometimes that
will still be in the same month (i.e. - July 31 - 30 days= July 1)...and I
can't simply subtract 31, because seometimes that will be 2 months earlier
(i.e.- July 1 - 31=May 31). Any help?

Thanks!

 

[lightbulb]

Thanks!

In these cases I want Feb. 28, and June 30....it has to be in the month
before...

 

[Harald Staff]

=IF(DAY(A1)>DAY(DATE(YEAR(A1),MONTH(A1),0)),DATE(YEAR(A1),MONTH(A1),0),DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)))

HTH. Best wishes Harald