Calculating the Number of Hours per Month

I want to create the list of hours for each month in current year.

And second formula - taking into account the summer / winter time, i.e. subtract one hour in March and add one hour in September.

Solution

We will use DAY, EOMONTH, and DATE functions as shown in the following formula:

=DAY(EOMONTH(DATE($E$1,A2,1),0))*24-(A2=3)+(A2=9)

Explanation

DATE function returns the date of the first day of the month specified in cell A2, for the year 2016.

EOMONTH function returns the last day of the month per that date (the month specified in cell A2).

DAY function returns the number of days in the month entered in cell A2 and then multiplied by 24 to calculate the total number of hours for the month in cell A2.

The expression A2=3 returns "1" (TRUE) if the month number in cell A2 equals 3 (March) and "0" (FALSE) if it doesn’t. Similarly, the expression A2=9 returns "1" (TRUE) if the month number in cell A2 equals 9 (September) and "0" (FALSE) if it isn’t.