Average of Working Hours by Formula in Excel

Suppose you are going to count the average working hours of employees in the company based on different months. If a worksheet including working hours on different dates and months is prepared for you, do you know how can we calculate the average of working hours based on months?

In this article, through explaining the example below, we will introduce you though creating a formula with AVERAGEIFS function and EOMONTH function inside to return average working hours for different months. You can get the basic knowledge and usage of these two functions in this article.

EXAMPLE

Average of Working Hours by Formula 1

We can see that “Start” time and “End” time are recorded clearly for different dates from above table. Total hours are calculated correctly as “Start” minus “End”, the unit is hour. In “Date” column, dates belong to two months “May” and “June”. To calculate average of total hours, we need to calculate average separately for the two months respectively.

For F2 (May-2021) and F3 (Jun-2021), in fact we input date 05/01/2021 into F2 and 06/01/2021 into F3, and set custom format “mmm-yyyy” for the two cells, then they are displayed as “May-2021” and “Jun-2021”. Only in this way they can be processed properly in the following operation.

We want to create only one formula that can be used for returning average working hours based on different months properly, in case of range reference auto adjusted when copying the formula, we use named ranges “Dates” and “Hours” to represent range references (A2:A9) and (D2:D9) in this instance.

In this article, to approach our goal, we will apply AVERAGEIFS function with EOMONTH function inside.

ANALYSIS

a. To calculate average, first we can think about average related functions like AVERAGE, AVERAGEIF, AVERAGEIFS etc.

b. As dates in “Date” belong to two months “May” and “June”, to calculate the average of total hours for one of them, we need to filter dates from “Date” column and make sure they belong to month “May” properly (or “June”); if dates meet our condition, total hours in “Total” will be kept and come to average calculation.

c. To filter date by condition “belong to May”, dates are required to meet below conditions:

1) Date >= 05/01/2021. // date should be greater than or equal to the first date in May.

2) Date <= 05/31/2021. // date should be less than or equal to the last date in May.

As we are going to calculate total hours from “Total” column based on filtered dates, and there are two conditions to filter date from given named range “Date”, so we can select AVERAGEIFS function to calculate average with multiple conditions to solve our problem in this instance.

d. To return the last date of a month, we can apply Excel EOMONTH function. Depends on the given date in F2, it can return the last date of the same month.

Above all, we can create formula =AVERAGEIFS(Hours,Dates,”>=”&F2,Dates,”<=”&EOMONTH(F2,0)) to solve this problem.

FORMULA

Input formula =AVERAGEIFS(Hours,Dates,”>=”&F2,Dates,”<=”&EOMONTH(F2,0)) into G2 and press Enter, verify that 8.50 is returned and shown in G2 properly. As (8+9+8+9)/4=8.5. The return value is correct.

Average of Working Hours by Formula 1

Notice: actually, the returned value may be a number with more than two decimal places (based on your settings), you can click “Decrease Decimal” in “Number” section under Home tab to shorten decimal places and keep only two.

Drag the handle down to copy formula to G4, verify that 9.13 is returned properly.

Average of Working Hours by Formula 1

FUNCTION INTRODUCTION

a. AVERGAEIFS function can be seen as AVERAGE+IFS. It returns the average of a set of numbers in range based on one or more given conditions or criteria.

Syntax:

=AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)

 Example.

Average of Working Hours by Formula 1

b. EOMONTH function returns the last date of month based on the given date.

Syntax:

 =EOMONTH(start_date, months)

 Comment: Argument months is a serial number; n months represents n months before or after the input start date. For example, if A1 is 05/20/2021, we enter =EOMONTH(A2, 1), it returns 06/30/2021 (set cell in proper date format). If we enter =EOMONTH(A2, -1) (negative value in months), it returns 04/30/2021. If we enter =EOMONTH(A2, 0), it returns 05/31/2021.

Example.

EOMONTH function example

EXPLANATION

=AVERAGEIFS(Hours,Dates,">="&F2,Dates,"<="&EOMONTH(F2,0))

// average range is named rangeHours(D2:D9)

// criteria range1 and criteria range2 are the same named range “Dates” (A2:A9)

// criteria 1 is “>=”&F2 (F2: date 5/1/2021)

// criteria 2 is “<=”&EOMONTH(F2,0)

a. The first criteria is “>=”&F2, it is combined with logical operator “greater than or equal to” (“>=”) and cell reference F2, they are concentrated by “&”. Value in F2 is “5/1/2021”, but when running the formula, it is converted to number “44317” which represents the date 5/1/2021.

Average of Working Hours by Formula 1

b. The second criteria is “<=”&EOMONTH(F2,0). EOMONTH function returns the last day of one month which is decided by the entered date. For F2 date is 5/1/2021, the second argument months is 0, so EOMONTH() returns the last day of May, it is 05/31/2021. Date 05/31/2021 is displayed as 44347 if change format from date to general format.

Average of Working Hours by Formula 1

c. Then filter dates from “Dates” by the two conditions.

Average of Working Hours by Formula 1

d. After filtering dates by providing conditions, then calculate average of hours refer to the filtered dates.

Average of Working Hours by Formula 1

e. After running above steps, formula returns average value “8.5” properly. In fact, you can select formula in formula bar, and press F9, you can find the formula returns 8.5 directly.

Average of Working Hours by Formula 1

Related Functions


  • Excel AVERAGE function
    The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
  • Excel AVERAGEIF function
    The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….
  • Excel AVERAGEIFS function
    The Excel AVERAGEAIFS function returns the average of all numbers in a range of cells that meet multiple criteria.The syntax of the AVERAGEIFS function is as below:= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)….
Related Posts

Extract Unique Items From A List
Extract Unique Items From A List In Excel1

This post will guide you how to extract unique itmes from a given list in Microsoft Excel. How to create a newly formula to get unique values from a range cells in Excel. The unique list of items is the ...

Extract substring In Excel
Extract substring In Excel1

This post will guide you how to use Excel's MID function is a quick and easy way to extract pieces from your text. Use the Excel formula to extract a substring with MID. Note: If you want to extract just ...

How to Use 3D SUM Multiple Worksheets
3D sum multiple worksheets1

To sum a range of numbers is straightforward for most Excel users, but do you know how to establish a 3D reference to total the same range of numerous sheets. In this post, I will present the steps for this ...

Extract all the matches with helper Column
how to extract all matches with helper column1

With Excel's powerful functions IF, INDEX, and MATCH, we can find exactly what you're looking for with a few clicks of the mouse. This step-by-step tutorial will show how easy it is to extract data using these tools and more! ...

If Cell is This Value or That Value

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of logical test. If you want to see if a cell is A or B, and if one of ...

If Value is Greater Than A Certain Value
If Value is Greater Than A Certain Value 1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the logical test result. If you want to see if a value in one cell is greater than a specific value, ...

If Cell is Not Blank
If Cell is Not Blank 6

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

If Cell is Blank
If Cell is Blank_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

If Cell Equals Certain Text String
If cell equals certain text_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if cell equals a certain text string like “Win”, you ...

If Cell Contains Either Text1 or Text2
If cell contains text1 or text2_1

IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to see if cell contains certain substring1 like “abc” or substring2 like “def”, and returns true ...

Sidebar