Calculating the Number of Hours per Month Allowing for Daylight-Saving Shifts in Microsoft Excel 2010

In this article, we will learn how to calculate the number of hours per month, allowing for daylight-saving shifts in Microsoft Excel 2010.

To calculate the number of hours per month, allowing for daylight-saving shifts, we will use a combination of DAY, EOMONTH & DATE functions to get the output.

DAY: Returns the day of the month, which is a number from 1 to 31

Syntax: =DAY(serial_number)

serial_number: It refers to the day of the month that you are trying to find.

EOMONTH: This function is used to calculate the last day of the month. EOMONTH can be used to calculate maturity dates or due dates that occur on the last day of the month.

Returns the serial number of the last day of the month before or after a specified number of months.

Syntax: =EOMONTH(start_date,months)

DATE: Returns the number that represents the date in Microsoft Excel date-time code.

Syntax: =DATE(year,month,day)

year: The year argument can include one to four digits. By default, Excel uses the 1900 date system.

month: The second argument representing the month of the year from 1 to 12 (January to December)

day: The third argument representing the day of the month from 1 to 31.

Let us take an example:

• Column A contains month number starting from 1 to 12.
• We need to calculate the number of hours that constitute each month (serial number) listed in column A.
• One hour should be subtracted to April due to the shift to daylight saving time.
• Similarly, one hour should be added from October’s total due to the shift back to standard time.

• To calculate hours in excel; we can simply calculate the number of days first then multiply by 24.
• In month of April we have 30 days i.e. 30*24= 720 Hours
• In month of October we have 31 days i.e. 31*24= 744 Hours.

With below formula we will be able to subtract 1 hour from April month & add to October month.

• To calculate hours in cell B2 the formula would be
  =DAY(EOMONTH(DATE(2014,A2,1),0))*24-(A2=4)+(A2=10)

• Copying down the formula from cell B2 to range B3:B13, we will get the desired result.

You will notice the number of hours in month of April have been reduced by 1 & for month of October, 1 hour has been added to total work hours.