Month
 

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!

Month
 

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

Regards,
Fred

"lightbulb" wrote in message
...
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!

Month
 

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

On Dec 22, 2:00*pm, lightbulb
wrote:
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!

Month
 

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


"lightbulb" wrote:

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!

Month
 

I am working in Excel 2003


"מיכאל (מיקי) אבידן" wrote:

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


"lightbulb" wrote:

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!

Month
 

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


"lightbulb" wrote:

I am working in Excel 2003


"מיכאל (מיקי) אבידן" wrote:

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


"lightbulb" wrote:

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!

Month
 

Thanks!

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

"Pete_UK" wrote:

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

On Dec 22, 2:00 pm, lightbulb
wrote:
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!

.

Month
 

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

HTH. Best wishes Harald

"lightbulb" wrote in message
...
Thanks!

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

"Pete_UK" wrote:

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

On Dec 22, 2:00 pm, lightbulb
wrote:
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!

.