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

Break ties with helper COUNTIF and column

Suppose you got a task to adjust the values that contain the ties; what would be your first attempt to break the ties of the given value? If you are wondering about doing this task manually, let me add that ...

Calculate Total Cost with Excel VLOOKUP Function

In today's article we will show you how to calculate the total cost for a given weight using the Excel VLOOKUP function. This function will help us to find the appropriate unit price for that weight and then we can ...

Excel Formulas To Calculate The Bond Valuation

Assume that you've been assigned a task of calculating bond valuation; so if you are new to Ms Excel or do not have much experience with it, then I am pretty sure about it that doing this task manually might ...

Calculate Cumulative Totals with Excel SUM Function

Today, through a simple example, we will show you how to use one of the most common-used Mathematical functions in excel, the SUM function, to add up the sum. In our daily life, we keep an account of what we ...

BMI Calculation Formula In Ms Excel

You might have come across a task in which you were assigned to make BMI calculations of the supplied numbers, and you may be looking for an efficient approach to accomplish this process rather than doing BMI calculations manually, by ...

Calculate The Period of Loan or Investment in Excel

In our daily life, most of us will invest or take a loan, so we need to master some simple financial functions to calculate the period needed for loans or investments. If you work as an accountant, you need to ...

Working Time Calculation Based on Timesheets

In the office, a special machine record the time when you start working (clock in office) and when you finish working (clock out of office). We can calculate the total working time by subtracting the end working time from the ...

Sort/Rank Numeric Values with Duplicate Values Exist

Excel built-in RANK function can sort a set of values. If there are duplicate numbers, then the rank number is also duplicated. See the following example: There are two numbers “100” in range A2:A9, they are both the third largest ...

Calculate Days Open in Excel

If you want to know how to Calculate days in Excel, there are some formulas that you can use to do so. For example, you can use the DAYS function in Excel to find the number of days between two ...

Calculate Grades With VLookup in Excel

Why Should You Calculate Grades With VLookup Excel? If you're looking for a simple way to find out a student's grades, VLOOKUP Excel can do the trick. This function uses a lookup table to find the values and sort them ...

Sidebar