# Is there a way to generate number of days in a month?

The title kind of says it all but just to give you the use case, I have a spreadsheet where I track monthly advertisement revenue and want to end up with a daily revenue column.

Here's a simplified example of what I have today:

``````MONTH   | TOTAL REVENUE
-----------------------
2012-04 | \$456.78
2012-03 | \$345.67
2012-02 | \$234.56
2012-01 | \$123.45
``````

Here's a simplified example of what I'd like:

``````MONTH   | TOTAL REVENUE | DAYS IN MONTH
---------------------------------------
2012-04 | \$456.78       | 30
2012-03 | \$345.67       | 31
2012-02 | \$234.56       | 29
2012-01 | \$123.45       | 31
``````

Obviously I'd like this in the form of a formula so I don't need to do any kind of manual tweaking. Is there some kind of formula that can take in `2012-04` and spit out `30`?

I use this formula:

``````=day(eomonth(today(),0))
``````

You'll have to replace `today()` with a date or a cell containing a date in the month for which you want the total number of days.

• You're missing a last parenthesis, but this is definitely the best answer. +1 Commented Nov 6, 2014 at 4:10

I stumbled across the `EOMONTH` function which is exactly what I needed, I end up doing a bunch of substring stuff but it seems to work which is all that is important to me.

Here's an example that will get you the number of days in field `A2` assuming it's in `YYYY-MM` format

``````=LEFT(RIGHT(EOMONTH(RIGHT(A2,2)&"/01/"&LEFT(A2,4),0),7),2)
``````

Parsing a Date with `Left` and `Right` might be unreliable (for example, in case the data format changes).

You could simplify the formula by using the `DAY` function instead:

``````=DAY( EOMONTH(RIGHT(A2,2)&"/01/"&LEFT(A2,4),0) )
``````

Also the way you compose the date inside the `EOMONTH` call could be replaced, using the `DATE(<year>, <month>, <day>)` function to make it a little more reliable.

So, the final formula would become:

``````=DAY( EOMONTH( DATE(LEFT(B51,4), RIGHT(B51,2), 1) ,0) )
``````

Let's say that that you have a date in cell `A1` with value

``````29 December 2015
``````

And you want to know how many days there are in the month of December in that year.

Use the `"months"` argument in the `EOMONTH` function to evaluate the number of days in the current month (using the value `0`), and the number of days in the previous month (using the value `-1`). This will give you `EOMONTH` values for both December and November.

Then simply subtract the two values from one another

``````=EOMONTH(A1,0)-EOMONTH(A1,-1)
``````

and voila! You get the number of days in December.

``````31
``````

This formula will also work for calculating the days in February in both leap years and non-leap years.

• Scratch that, I actually prefer the answer by @ian-melchior Commented Dec 30, 2015 at 2:28