Days in month

To get the number of days in a given month based on a date in the month, you can use a formula based on the EOMONTH and DAY functions. In the example shown, the formula in cell D5 is:

=DAY(EOMONTH(B5,0))

Since the date in cell B5 is 12-Jan-2024, and since there are 31 days in January, the result is 31. As the formula is copied down, it returns the total days for each month for each date in column B.

Explanation

In this example, the goal is to get the total number of days in a month based on any date in the month. This problem can be solved by combining the DAY function with the EOMONTH function.

The EOMONTH function

The EOMONTH function returns the last day of the month, a given number of months in the past or future. For example, with a start date of June 15, 2024, EOMONTH will return the following results with months set to -1,0, and 1:

=EOMONTH("15-Jun-2024",-1) // returns 31-May-2024
=EOMONTH("15-Jun-2024",0) // returns 30-Jun-2024
=EOMONTH("15-Jun-2024",1) // returns 31-Jul-2024

Note that the start date remains the same, but the month varies. Negative months cause EOMONTH to move back in time, and positive months move forward, but the result is always the end of the month. Providing months as zero (0) will move EOMONTH to the end of the "current" month, relative to the date provided.

The DAY function

The DAY function returns the day component of any date, for example:

=DAY("15-Jun-2019") // returns 15
=DAY("7-Aug-2021") // returns 7
=DAY("23-Nov-2023") // returns 23

Note that in each case DAY returns the day value for the date.

Combining DAY and EOMONTH

Since, by definition, the last day of a month is equal to the number of days in the month, we can use DAY with EOMONTH to calculate the number of days in a month. In the worksheet shown, the formula in cell D5 looks like this:

=DAY(EOMONTH(B5,0))

Using DAY and EOMONTH to calculate total days in any month

Working from the inside out, the EOMONTH function returns the last day of the month using the date in B5 as a starting point. Notice that the months argument is provided as zero so that we stay in the same month. Next, the shifted date is returned to the DAY function, which returns the day part of the date as a final result. Since the last day in January is the 31st, the final result is 31:

=DAY(EOMONTH("12-Jan-2024",0))
=DAY("31-Jan-2024")
=31

As the formula is copied down, the process is repeated for each date in column B.

Days in the Current Month

To get the number of days in the current month (today's month), you can use the same approach but replace the date reference with the TODAY function:

=DAY(EOMONTH(TODAY(),0))

This formula will automatically update each day to show the total number of days in whatever month it currently is. For example, if today is June 21, 2025, the formula will return 30 (since June has 30 days). If you open the same worksheet in July, it will automatically return 31. The formula works exactly the same way as the main example:

=DAY(EOMONTH(TODAY(),0))
=DAY(EOMONTH("21-Jun-2025",0))  // TODAY() returns current date
=DAY("30-Jun-2025")             // EOMONTH finds last day of June
=30                             // DAY extracts the day number

This approach is useful in reports and dashboards that need to display current-month information. Since TODAY recalculates whenever the worksheet recalculates, this formula will always reflect the correct number of days for the current month.