Formula to display the last day of the previous month

**avidcat** · 01-22-2010, 09:42 AM

I am using the following formula to show the first day of previous 6 month cycle.

=DATE(YEAR(TODAY()),MONTH(TODAY())-6,1)

That formula would display: 07/01/2009

I want to have another formula show the last day of the previous month.

Basically it would display: 12/31/2009

I appreciate any help with this.

thanks,
Nick

**DonkeyOte** · 01-22-2010, 09:45 AM

A few options

XL2007 and beyond (or with ATP activated)

=EOMONTH(TODAY(),-1)

Pre XL2007

=DATE(YEAR(TODAY()),MONTH(TODAY()),0)

or better yet simply

=TODAY()-DAY(TODAY())

**JimmyA** · 01-22-2010, 09:48 AM

Hi There

Does this help?

Please Login or Register  to view this content.

Cheers

Jim

Sorry- seen a much easier solution arrived before me!!!

**avidcat** · 01-22-2010, 09:52 AM

Very cool, I am using 2007.

I am trying to learn more excel.

=EOMONTH(TODAY(),-1)

EOMONTH : is for end of month
TODAY : what does the TODAY value represent in the formula?
-1 : for the previous month

**JimmyA** · 01-22-2010, 09:56 AM

the Today() is telling it which month you want the end date of, putting -1 then tells it to look at the end of the previous month and not the current.

Jim

**DonkeyOte** · 01-22-2010, 09:56 AM

avidcat, never be afraid to use the power of F1 in XL... typing in EOMONTH, TODAY etc ...

Or if you prefer highlight the appropriate function you're interested in within the formula bar view and press the fx button to the left.