Month

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!
 
P

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
 
L

lightbulb

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!
 
×

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

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!
 
L

lightbulb

Thanks!

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top