Automatic fill of a cell with with days in the month

[nkitchen31]

I have a drop down list in cell F1 with all the months. In G1, I would like it to automatically display the number of days in the selected month. Of course it depends on the current year (due to leap year, etc).

Thank you in advance!
Nick

[RobertMika]

Are your months in Text or Date format?

[nkitchen31]

Text, January, February, etc

Thank you.

[martindwilson]

one way with text months jan/feb or january,february...........=EOMONTH(VALUE("1/"&F1&YEAR(TODAY())),0) format cell as custom dd

[nkitchen31]

one way with text months jan/feb or january,february...........=EOMONTH(VALUE("1/"&F1&YEAR(TODAY())),0) format cell as custom dd

Beautiful.. that is perfect! So the year will continually change because of "YEAR(TODAY)" right? Awesome. Thank you!

[RobertMika]

Slightly quicker
=EOMONTH(--("1/"&F1&YEAR(TODAY())),0)

[Tony Valko]

Here's another one...

=DAY(EOMONTH("1/"&F1&YEAR(NOW()),0))

Format as General or Number

[Tony Valko]

So the year will continually change because of "YEAR(TODAY)" right?

Yes.

You can substitute NOW() for TODAY() and it will still work.

[nkitchen31]

Thank you for all the replies… Now…

Is it possible now to protect just that one cell with the formula in it so somebody doesn't accidentally change it who doesn't know better?

I went to protect worksheet in the Tools menu, but then when I chance the month in the drop down list, it says the sheet is protected and it won't change the value.

Thanks again.

[nkitchen31]

Nevermind, I got it .. it was still "Locked".

[Tony Valko]

I went to protect worksheet in the Tools menu...

What version of Excel are you using?

Your profile says Excel 2010 but it doesn't have a Tools menu.

[Tony Valko]

Good deal. Thanks for the feedback!

[nkitchen31]

Tony.. done!

Okay, so now that I formatted that as "dd", I cannot reference that cell in the formula for another cell because it uses the value "41,350" or something instead of 31 … Any ideas how to change that?

Thanks.

[martindwilson]

=DAY(EOMONTH("1/"&F1&YEAR(NOW()),0)) which tony suggested

[Haseeb Avarakkan]

I think we don't need YEAR(TODAY()) here. If we omit that excel will consider that is from current year.

=DAY(EOMONTH(1&F1,0))