Month

  • Thread starter Thread starter lightbulb
  • Start date Start date
L

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!
 
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
 
I am working in Excel 2003


מיכ×ל (מיקי) ×בידן said:
try this: =EDATE(A1,-1)
[You must have Analysis Toolpak add-in installed in older versions than
"2007"]
Micky


lightbulb said:
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!
Click to expand...
Click to expand...
 
So...!?
'Tools' > 'Add-Ins > check the 'Analysis Toolpak" > 'OK'
Micky


lightbulb said:
I am working in Excel 2003


מיכ×ל (מיקי) ×בידן said:
try this: =EDATE(A1,-1)
[You must have Analysis Toolpak add-in installed in older versions than
"2007"]
Micky


lightbulb said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Thanks!

In these cases I want Feb. 28, and June 30....it has to be in the month
before...
 
=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
 
Back
Top