Calculate number of days in a month

I'm trying to build a custom calendar in a sheet (Calendar app or view doesn't work in our use case), I want to calculate total number of days in a given month, but so far the day, date formulae need two set of dates. I know one way of simply declaring the values in a column and use it that way but I was wondering if anyone had this issue and if they've solved it?

Best Answer

Answers

  • MCorbin
    MCorbin Overachievers Alumni

    There isn't a formula that will automatically give you the last day of the month, which would help in the calculation.

    I have a support table where I keep a bunch of date related values that I use as lookups in sheets to help with things like this:

    For your formula.... I would first lookup the last day of the month using an Index/Match formula

    So, if you have a column with your month start date in it:

    You can create a formula to find the month end: =DATE(YEAR([Month Start]@row), MONTH([Month Start]@row), INDEX({Last Day}, MATCH(MONTH([Month Start]@row), {Month Number}, 0)))

    Then the # of days in the month is a simple: =[Month End]@row - [Month Start]@row

    If your date column has any date in the month, but you still want to calculate how many days are in that month, you could create a little more complicated formula for # Days:

    =DATE(YEAR(Date@row), MONTH(Date@row), INDEX({Last Day}, MATCH(MONTH(Date@row), {Month Number}, 0))) - DATE(YEAR(Date@row), MONTH(Date@row), 1)


    (this one takes the month and year from your date column, but inserts a 1 for the start date to always give you the 1st of the month, and does the lookup to get the last date of the month)

  • Ameya Athalye
    Ameya Athalye ✭✭✭✭✭
    Answer ✓

    @MCorbin Thank you, this is really helpful. I'd started down the same path, so it's good to know I'm on the right track. The screenshot you shared is very useful. Thank you.

  • Ameya Athalye
    Ameya Athalye ✭✭✭✭✭

    @MCorbin I was able to build a custom calendar. Thanks for your help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!