Calculate Average Response Time Per Month In Microsoft Excel

Assume that you have been assigned a task of calculating the average response time per month in MS Excel, then; if you are new to MS Excel, doing this task manually might be your first attempt which would not only make you tired, but you won’t complete your work on time.

But fortunately, there is a more innovative way by which you can do this cumbersome task in a matter of seconds, so kindly read this article till the end and let’s dive into it,

General Formula:

Use the formula below to calculate the average time per month in Excel.

=AVERAGEIFS(response,date_value,">="&E2,date_value,"<="&EOMONTH(E2,0))

Average response time per month1

Explanations for Syntax:

  • AVERAGEIFS: This Function returns the average of a set of input values that meet multiple criteria. Learn more about the AVERAGEIFS Function.
  • EOMONTH: In Excel, the EOMONTH Function can be used to return the last day of the month after adding or subtracting a specified number of months from a date.
  • Response: The response is calculated by subtracting the date opened from the date closed.
  • Date_value: These are the input dates from your worksheet.
  • Criteria (E2): It specifies the month used to calculate the average.

 Explanation

To average response times by month, use a formula based on the AVERAGEIFS and EOMONTH Functions.

In the example, the formula in F2 is mentioned below:

=AVERAGEIFS(response,date_value,">="&E2,date_value,"<="&EOMONTH(E2,0))

Average response time per month1

This formula employs the named ranges “ date_value ” (A2:A7) and “ response ” (C2:C7). Column C’s response are in minutes and are calculated by subtracting the date opened from the date closed.

The AVERAGEIFS function is intended to average ranges based on multiple criteria; that’s why we would configure the AVERAGEIFS process to average durations by month based on two criteria:

1st criteria are in which the matching dates are either greater than or equal to the first day of the month, and the 2nd  criteria are in which the matching dates are either less than or equal to the last day of the month.

In column E, we just type month names, such as: 04,05,07.

This makes it easier to create the AVERAGEIFS function’s criteria we need by using the values in the F column. To match that either the dates greater than or equal to the four of the month, we use:

">="&E2

Average response time per month1

To match dates that are less than or equal to the last day of the month, we use:

"<="&EOMONTH(E2,0)

Average response time per month1

By providing zero for the month’s argument, we can get the EOMONTH to return on the last day of the same month.

When the building criteria are based on a cell reference, oncatenation with an ampersand (&) is required.

So we know that using the formula mentioned above, you can obtain the average response times by month. This tutorial will learn how to calculate the average monthly response time in a Microsoft Excel workbook. As we know, the AVERAGEIFS Function is intended to average ranges based on multiple criteria. In the following example, we configure AVERAGEIFS to average durations by month using two criteria: first, we match dates greater than or equal to the first day of the month. Second, by matching dates less than or equal to the last day of the month. So, using this formula, you can obtain the average response time per month in the workbook in Microsoft Excel.

Pivot Table

When you need to summarise or average data by year, month, quarter, and so on, then a pivot table is an excellent solution because pivot tables automatically group dates.

Related Functions

  • 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],…)….

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],…)….

How to Sum by Month in Excel

If sum data by month in a table, we need to use a formula based on SUMIFS function. We can provide a set of conditions or criteria in SUMIFS function to sum data. To sum data by month, we need to add date range as criteria.

Example:


See below screenshot, if we want to sum total amount by month, how can we do?

Solution:


In C2, enter the formula =SUMIFS($B$2:$B$10,$A$2:$A$10,”>=”&A2,$A$2:$A$10,”<=”&EOMONTH(A2,0)).

Let’s see how this formula works:

For SUMIFS function, =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …), in this instance, $B$2:$B$10 is the sum range, $A$2:$A$10 is the criteria range, criteria 1 is >=A2 (10/1/2020), criteria 2 is <=EOMONTH(A2,0), it is the last date of October (the last date in month of A2), to get the last date in month of A2, we use EOMONTH function here.

For EOMONTH function, =EOMONTH(start_date, months), it returns the last date of a month for a specific date. In this instance, EOMONTH(A2,0), with zero as months value, it returns the last day of a month provided by date in A2. As A2 is 10/1/2020, so this function returns 10/31/2020 at last. In fact, it equals to =SUMIFS($B$2:$B$10,$A$2:$A$10,”>=10/1/2020″,$A$2:$A$10,”<=10/31/2020″). If you enter EOMONTH(A2,1), it will return the last date of next month based on date in A2, for example, A2 is 10/1/2020, then EOMONTH(A2,1) returns 11/30/2020. If you enter EOMONTH(A2,-2), it will return the last day of month, two months before the month in A2, in this case it is 8/31/2020.

Result:


Click Enter to get return value.

Notes:

1. The formula also works if date format is other than mm/dd/yyyy. For example, we change date in November to different date format in table. Then formula is updated to =SUMIFS($B$2:$B$10,$A$2:$A$10,”>=”&A5,$A$2:$A$10,”<=”&EOMONTH(A5,0)).

It still works.

2. If we just want to sum data between a period, we can update formula to =SUMIFS(sum range, criteria range1,”>=”&start date, criteria range2,”<=”&end date). For example, to get amount between date 12/10/2020 and 12/12/2020. Enter the formula =SUMIFS($B$2:$B$14,$A$2:$A$14,”>=”&A11, $A$2:$A$14,”<=”&A13).

Related Functions


  • Excel SUMIFS Function
    The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…