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

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

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.

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

Hi st_judeu@yahoo.com

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.

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

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

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

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

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

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!!

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

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

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

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

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

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

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

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

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

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

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.`

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

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))))

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

@ 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. ...

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

@ 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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1