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]

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]

Nice. I forgot I knew about EDATE.

IFYP :heynow:

[JMO]

IFYP :heynow:

:lol:
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.

[Phil]

I tried EDATE but there weren't any women's profiles I liked. In general, I don't ask my dates Excel questions because it sounds too dorky.

[Maphisto's Sidekick]

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.


To avoid the day 29/30/31 issue, use =date(year(now()),month(now())-1,1)

[willhslade]

=TODAY()-DAY(TODAY())

Returns the last day of the last month.

Handy.

I use this in a VBA loop to move the month forward and backward in ACCESS.

Will

[spencerhs5]

If you have Analysis toolpak or Excel 2007 check out the function eomonth()

you might like what it has to offer. basicly if today is the middle of the month =eomonth(now(),0) would give you the last day of the month or =eomonth(now(),-1) would give the last day of the previous month

you never have to worry about leap year or anything like that!

[campbell]

=TODAY()-DAY(TODAY())

Returns the last day of the last month.

Handy.

I use this in a VBA loop to move the month forward and backward in ACCESS.

Will

That's a nifty trick.

[Actuary321]

To avoid the day 29/30/31 issue, use =date(year(now()),month(now())-1,1)I like will's trick.

To get last month I would use =date(year(now()),month(now()),1)-1