CODEWITHSUNDEEP
excel-formula
BruceWayne
BruceWayne

Reputation: 23283

Get last day of month - returning a couple days off

I have a cell (W3) with February in the cell. I would like to find the last day of February with a formula.

I have tried this formula: =DATE(2016,MONTH(1&W$3),EOMONTH(MONTH(1&W$3),0))

However, it returns 3/2/2016, instead of 2/29/2016, how come? If I have December as the text, it correctly returns 12/31/2016. November does the same error, if I have November, then it's returning 12/1/2016.

I think it's with how I'm using the Month()? But I'm not sure why it's acting oddly. Thanks for any tips!

Upvotes: 0

Views: 111

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19712

This should work: =EOMONTH(DATEVALUE("1 " & W3),0)

That gives the end of month value - not sure why your formula doesn't though. =MONTH(1&W$3) correctly returns 2....
actually that would turn your EOMONTH formula into =EOMONTH(2,0) which returns 31 which is 2nd March if there's 29 days in February.

The 2 should be a date rather than number - so date is converted to 02/01/1900.

Upvotes: 4

Related Questions