Calculate number of days in a month

Ameya Athalye

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?

Ameya Athalye

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

MCorbin

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

@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

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