Cell displays last date of next month.

**markswan20** · 12-10-2009, 07:19 AM

Hi everyone,

I have entered =TODAY() in Cell C21 and i need the date in E46 to display the last day of next month.

Is there a way of making this do this automatically?

Cheers Mark

**zbor** · 12-10-2009, 07:31 AM

This will display last date of next month:

=DATE(YEAR(C21)+INT((MONTH(C21)+1)/MONTH(C21));MONTH(C21)+1; )

This will display last day as number:

=WEEKDAY(DATE(YEAR(C21)+INT((MONTH(C21)+1)/MONTH(C21));MONTH(C21)+1; );2)

If you format cell as dddd you'll get text (monday, tuesday...)

Replace ; with , if needed

**dominicb** · 12-10-2009, 07:33 AM

Originally Posted by markswan20

I have entered =TODAY() in Cell C21 and i need the date in E46 to display the last day of next month.

How about :

=DATE(YEAR(C21),MONTH(C21)+1+1,0)

HTH

DominicB

**teylyn** · 12-10-2009, 07:34 AM

Try

E46 =DATE(YEAR(TODAY()),MONTH(TODAY())+2,0)

hth

**markswan20** · 12-10-2009, 07:50 AM

Thanks for all your fast responses you all make it look easy :-)

Thank You

**zbor** · 12-10-2009, 07:51 AM

It won't work for DECEMBER

That's why I had to complicate my formula

**daddylonglegs** · 12-10-2009, 08:16 AM

Originally Posted by zbor

It won't work for DECEMBER

Which one?

This formula works fine

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

gives me 31st Jan 2010 as expected......

Edit: if you have Excel 2007 or don't mind using Analysis ToolPak functions in earlier versions you can use EOMONTH function, i.e.

=EOMONTH(C21,1)