Changing number of days in a month based on month chosen from a dropdown list.

**[email protected]** · 02-06-2013, 01:26 PM

Good day friends.
Please I have a drop down list that contains the months of the year from Jan-Dec. I also have another cell say A1 where I am to indicate the number of days in the month chosen from the drop down list. What I want is if I choose Jan, March, May, July, August, October and Dec, A1 will show 31 as the number of days. If I choose April, June, September and November, A1 changes to 30. Finally, if I choose February, A1 changes to 28 or 29 as the case may be. Please I need help to sort this out.
Thanks.

**Kevin UK** · 02-06-2013, 01:38 PM

Hi [email protected]

One way, if you have data validation say in Cell D1. In A1:

Formula:

Please Login or Register  to view this content.

Better of using a VLOOKUP with a little table. See attached.

**Pete_UK** · 02-06-2013, 01:39 PM

Which cell is your drop-down list in? Suppose it is D1, then you can put this formula in A1:

=DAY(DATE(YEAR(TODAY()),MATCH(D1,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0)+1,0))

Hope this helps.

Pete

**[email protected]** · 02-08-2013, 06:53 AM

The Vlookup and the one suggested by Pete_UK work very well. The one that does not work well is the lookup function. See the summary of errors below:
Feb #N/A
Apr #N/A
Jul 28
Aug #N/A
Nov 31
Dec #N/A

**Kevin UK** · 02-08-2013, 07:55 AM

Crystal ball seems to come to mind! You didn't upload a worksheet.
If you look at the worksheet I posted does that not work, for months as Jan, Feb etc. Not as your original post "Jan, March etc"
So how you have your sheet set up! Only you know that!!

**martindwilson** · 02-08-2013, 08:47 AM

with dropdown of jan,feb.... in c1 then in a1 =DAY(EOMONTH(DATEVALUE("1/"&$C$1&"/"&YEAR(TODAY())),0))

**Marcol** · 02-08-2013, 08:57 AM

See if this helps

Formula:

Please Login or Register  to view this content.

Where D2 = Month as string
E2 contains the year, this is needed to handle leap years.

Or, if you are only interested in the current year, adapt the formula as Martin suggests

Formula:

Please Login or Register  to view this content.

This doesn't involve the Analysis ToolPak add-in. with 2003 and earlier

**Pete_UK** · 02-08-2013, 09:47 AM

With the LOOKUP formula (Kevin's), you need to put the months in alphabetical order, with a corresponding shift of the days, i.e.:

=LOOKUP(D1,{"Apr","Aug","Dec","Feb","Jan","Jul","Jun","Mar","May","Nov","Oct","Sep"},{30,31,31,28,31,31,30,31,31,30,31,30})

although this would always give 28 days for February and not adjust for leap years.

Hope this helps.

Pete

**daddylonglegs** · 02-08-2013, 11:09 AM

With dropdown in D1 try this formula for number of days in the month

=42-DAY((1&D1)+41)

**Marcol** · 02-08-2013, 11:52 AM

Nice one DLL, are you related to Dr Who? ...

If not tied to current year, where E1 contains the required year, DLLs' formula becomes.

Formula:

Please Login or Register  to view this content.

**lts_ram** · 02-08-2013, 01:21 PM

hi,

try using functions if, and, or & mod.suffix year value to feb month & assuming cell ref: b1 contains name of the month from the drop down list

=IF(OR(B1="JAN",B1="MAR",B1="MAY",B1="JUL",B1="AUG",B1="OCT",B1="DEC"),31,IF(OR(B1="APR",B1="JUN",B1="SEP",B1="NOV"),30,IF(AND(MONTH(B1)=2,MOD(YEAR(B1),4)=0),29,IF(MONTH(B1)=2,28))))

**Marcol** · 02-08-2013, 02:07 PM

@ lts_ram

This post was accepted while the "tech team" were fannying about again, and is a duplicate of the next.

When is this nonsense going to stop?

The Forum crashed 3 times while trying this post.

It has been practically unuseable for more than 24hrs now. ...

**Marcol** · 02-08-2013, 02:26 PM

@ lts_ram

Did you try your suggestion for "Feb"?
It seems to return a #VALUE! error.
Why go that way anyhow ...

DLLs' formula does with 17 characters,what your formula fails to do with 181.

Everybody is here to learn and help, you are no exception and very welcome, but you should really read the thread, and test your work before posting.

Changing number of days in a month based on month chosen from a dropdown list.

LinkBack

Thread Tools

Rate This Thread

Display

Changing number of days in a month based on month chosen from a dropdown list.

Re: Changing number of days in a month based on month chosen from a dropdown list.

Re: Changing number of days in a month based on month chosen from a dropdown list.

Re: Changing number of days in a month based on month chosen from a dropdown list.

Re: Changing number of days in a month based on month chosen from a dropdown list.

Re: Changing number of days in a month based on month chosen from a dropdown list.

Re: Changing number of days in a month based on month chosen from a dropdown list.

Re: Changing number of days in a month based on month chosen from a dropdown list.

Re: Changing number of days in a month based on month chosen from a dropdown list.

Re: Changing number of days in a month based on month chosen from a dropdown list.

Re: Changing number of days in a month based on month chosen from a dropdown list.

Re: Changing number of days in a month based on month chosen from a dropdown list.

Re: Changing number of days in a month based on month chosen from a dropdown list.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions