+ Reply to Thread
Results 1 to 13 of 13

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

  1. #1
    Forum Contributor
    Join Date
    05-17-2010
    Location
    Nigeria, Owerri
    MS-Off Ver
    Excel 2007 and 2013
    Posts
    247

    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. #2
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    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: copy to clipboard
    Please Login or Register  to view this content.


    Better of using a VLOOKUP with a little table. See attached.
    Attached Files Attached Files
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,115

    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
    Last edited by Pete_UK; 02-06-2013 at 02:35 PM.

  4. #4
    Forum Contributor
    Join Date
    05-17-2010
    Location
    Nigeria, Owerri
    MS-Off Ver
    Excel 2007 and 2013
    Posts
    247

    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. #5
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    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. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    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))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  7. #7
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

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

    See if this helps
    Formula: copy to clipboard
    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: copy to clipboard
    Please Login or Register  to view this content.


    This doesn't involve the Analysis ToolPak add-in. with 2003 and earlier
    Attached Files Attached Files
    Last edited by Marcol; 02-08-2013 at 10:17 AM.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,115

    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. #9
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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)
    Audere est facere

  10. #10
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    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: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by Marcol; 02-08-2013 at 01:36 PM.

  11. #11
    Registered User
    Join Date
    03-06-2011
    Location
    chennai
    MS-Off Ver
    Excel 2016
    Posts
    46

    Post 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))))
    Last edited by lts_ram; 02-08-2013 at 01:27 PM. Reason: for more clarity

  12. #12
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    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. ...
    Last edited by Marcol; 02-08-2013 at 02:42 PM.

  13. #13
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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