Increment day count based on month (30/31 days) and incorporate leap years

  • Hi there

    Please see attachment, I'm trying to automatically populate columns with the correct order of months, ensure they hit their 30/31 day max day count and for Feb (depending upon a leap year or not), reach 28 or 29 days. I'm a beginner, so struggling with how to add all of these formulae together! Basically:

    If month in column A is Jan, Mar, May, Jul, Aug, Oct or Dec then don't let the count/number in column B be >31 (eg: months with 31 days).

    If months in column A is Apr, Jun, Sep or Nov, then don't let the count/number in column B be >30 (eg: months with 30 days).

    If the number in D2 is 1, 5 or 9 (based on the Chinese calendar) AND if month in column A is Feb, don't the number in column B be >29 (eg: a leap year), otherwise don't let it be > 28

    Any ideas?!!!

    Many thanks:)test_140620.xlsx

  • You don't need macros to achieve this.

    The trick to working with dates in Excel is to actually use dates, e.g. don't write FEB, use the date 1/1/20 and format as ddd. Apply this to the whole column of dates in A.

    Select the range of cells adjacent to A and use Data Validation to prevent a user entering numbers > than days in the month.

    1. Open the Data validation dialog
    2. In Settings choose
    • Allow: Whole Number
    • Data: less than
    • Custom: =DAY(EOMONTH(A7,0))+1

    3. Set up a custom message to inform the user