Excel question involving dates

[Wendy Crewson]

Happy Friday. I am having trouble recalling how I used to have Excel calculate the date. The fact pattern is ----> I put the "Now" function in one cell and then in another cell, I grabbed the month and year portion from the results of the Now function. All I want to do is display, for example, June 2008, in a designated cell automatically in real time. I would also like to be able to subtract 1 from the month if I am working on last month's data. Any thoughts? Thanks.

[schleprock]

Why not just format the NOW() cell to show "June 2008"?

There's a handy function called EDATE() that adds or subtracts months from the date in a target cell, but I think you need to add in the "Analysis ToolPak" to get it.

[Wendy Crewson]

Schlep -- I tried experimenting with the formats to create the June 2008 version, but none of the available ones work. Did you have a specific one in mind or were you thinking that it could be programmed?

[Former Actuarial]

use now() and change the cell format to a custom number of mmmm yyyy. (after you enter the function, click format/cells/number/custom and type in "mmmm yyyy" without the quotes)

for subtracting a month, use "=DATE(YEAR(NOW()),MONTH(NOW())-1,DAY(NOW()))" without the quotes

[thing]

=TEXT(TODAY(),"MMMM YYYY")
=TEXT(EDATE(TODAY(),-1),"MMMM YYYY")

[Old Timer]

Originally Posted by Former Actuarial

use now() and change the cell format to a custom number of mmmm yyyy. (after you enter the function, click format/cells/number/custom and type in "mmmm yyyy" without the quotes)

for subtracting a month, use "=DATE(YEAR(NOW()),MONTH(NOW())-1,DAY(NOW()))" without the quotes

If you use this approach, watch out for the 31st of a month and some of February. EDATE is the preferred solution and yes, you do need to use the Analysis ToolPak add-in. Also, I'd recommend the custom formatting route as well so you don't need an extra formula.

[JMO]

Nice. I never knew about EDATE.

[GadgetGeek]

Originally Posted by JMO

Nice. I forgot I knew about EDATE.

IFYP

[JMO]

Originally Posted by GadgetGeek

IFYP

You mean it was in Lotus 1-2-3? (I really did know everything in the version available back BI before internets)

[Wendy Crewson]

It's nice to have you guys as a resource. Have a great weekend.