Now() minus 1 month = ?

[Tom Wagner]

Hello - I'm trying to find out how to display a date of 1 month prior to current month year. If today is Oct-04, I would like to display Sept-04 in a cell.
Thanks for any help.
Tom

 

[Juan Pablo González]

If A2 houses the date that you have, try this

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

 

[PaddyD]

also,

edate(a2,-1)

...edate() requires that the analysis toolpak is activated (tools | addins)

 

[walt1122]

Hi Tom, Juan probably has a better handle on it but I got =EOMONTH(NOW(),-1). Not sure if you have the End Of Month add in.

Walt

 

[Tom Wagner]

Thank you Juan & Walt. That was very helpful.

I used:

A2=now()
B2=DATE(YEAR(A2), MONTH(A2)-1, DAY(A2))

And received the desired results.

 

[PaddyD]

"Thank you Juan & Walt. That was very helpful."

Glad to know I wasn't

 

[bosco_yip]

Book1
	A	B	C	D
1	Date	PaddyD's	JPG's
2		Formula	Formula
3	27/03/2004	27/02/2004	27/02/2004
4	28/03/2004	28/02/2004	28/02/2004
5	29/03/2004	29/02/2004	29/02/2004
6	30/03/2004	29/02/2004	01/03/2004
7	31/03/2004	29/02/2004	02/03/2004
8	01/04/2004	01/03/2004	01/03/2004
9	02/04/2004	02/03/2004	02/03/2004
10	03/04/2004	03/03/2004	03/03/2004
Sheet1

Just wanted to show the formula result used by PaddyD and JPG

Content of which is self explanation

Regards

 

[walt1122]

Hi PattyD, I'll thank you even if Tom didn't get a chance too. I like your edate. That's another new one on me. Works better than my EOM for keeping the day and date. I was just interested in going back the one month. Did the =EDATE(NOW(),-1) and got 9/20/2004. nice! And Juan, liked yours too. If A2 houses the date that you have, try this

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

have to remember these

thanks

Walt

 

[Tom Wagner]

Just had a chance to log back on.

These are great techiques. Thank you all! I have learned a lot.

 

[tusharm]

LOL!

Do you realize how often that happens to me...? {vbg}

"Thank you Juan & Walt. That was very helpful."

Glad to know I wasn't