Counting number of days in a calendar month according to year

Hi Lionnel,

The function EOMONTH (end of month) will help.

Column B is Date & Time formatted to show only the Month and Year (actually the 1st of the month, but the day is not displayed).

Formula in C2 (and Fill Down)

=EOMONTH(B2,0)

The end of the month 0 months later.

Formula in D2 (and Fill Down)

=DAY(C2)

February 2015 (non leap year) shows 28

February 2016 (leap year) shows 29

Regards,

Ian.

Hi Lionnel,

Replace what you have in column A with actual Dates, starting with January 1, 2015, then February 1, 2015, etc. The easiest way to do this is to enter the first two dates, then select both those cells and fill down, using the yellow Fill Handle dot that appears in the center of the bottom edge of the second cell when you hover the mouse in that area. Grab the dot with the mouse and drag down the column.

In column B, enter the number of days of activity each month.

In column C, enter the formula below into the row with the first date (C2 in my example), and Fill down to the bottom of the column.

C2: =B/DATEDIF(A,EOMONTH(A,0)+1,"D")

(Numbers will convert the / after B to a division sign (÷). Comments on the formula can be found below the table.

The table shows the results I got using numbers from your original post, plus 10s in cells above those numbers and 15s in cells below those numbers.

NOTES:

Column A contains Date/Time values, formatted to display only the year and month.

Column B contains numbers, with formatting set automatically.

Column C contains the formula shown above the table, with the result formatted to display the result as a percentage, with one place after the decimal.

C2:

The key functions in column C are:

• EOMONTH(start-date, month-offset)B/DATEDIF(A,EOMONTH(A,0)+1,"D")
  • start-date is the date in the same row of column
  • month-offset is zero, so that EOMONTH returns the date of the last day of the month in column A.
  • +1 adds 1 (day) to this, making the end-date passed back to DATEDIF the first day of the month after the date in this row of column A.
• DATEDIF(start-date,end-date,calculation method)
  B/DATEDIF(A,end-date,"D")
  • start-date is again the date in this row of column A
  • end-date is the first of the following month, as calculated above
  • calculation-method is "D" which tells DATEDIF to return the number of Days from start-date to end-date.
    This result is always the number of days in the month named in this row of column A in the year named in the same cell.
• The number in this row of column B is divided by the result returned by DATEDIF, and the result of that division is displayed in the cell containing the formula.

Regards,

Barry

Hi Ian,

I thought you might be posting about the time I was finishing up the calculations and beginning to write the descrition. Looks like that estimate was about right. 😁

I like your more efficient way of extracting the number of days. Using that method, the formula in column C of my table would be:

=B/DAY(EOMONTH(A,0))

Much cleaner than before!

And with the same results:

Regards,

Barry

Hi Barry,

Great minds think alike! However, I am a plodder (break the problem down to small steps and use a column for each step). I admire your ability to combine the steps into a single formula. Nice.

Regards,

Ian.