Excel is a useful program that allows us to perform a variety of numerical calculations such as multiplication, division, addition, subtraction, average, count, and so on. However, when it concerns activities based on time and date, it might be a little perplexing. This article will show you how to determine the average time in Excel for any timing you provide.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Suitable Examples to Average Time in Excel
We’ll show you three examples of how to calculate the average time from a data collection of specific times in the sections below. To do so, we’ll use the AVERAGE function and the AVERAGEIF function. The following is an example data set.
1. Use the AVERAGE Function to Get Average Time in Excel
The first general method to get average time is to use the AVERAGE function. Follow the steps below to do so.
Step 1:
- Select the cells (C5:C10).
Step 2:
- Choose the Time format from the Number
Step 3:
- In cell E5, type the formula.
=AVERAGE(C5:C10)
- Therefore, you will get the average time.
Read More: Running Average: How to Calculate Using Excel’s Average(…) Function
2. Apply Condition to Calculate Average Time in Excel
Excel may also be used to conduct conditional operations. We’ll apply a set of criteria and calculate the average time. Follow the steps below to do a conditional average time calculation.
Step 1:
- Firstly, select the cells.
Step 2:
- Press Ctrl + 1 to open the Format Cells dialog box.
- Click on the Custom
- Select the time format h:mm:ss AM/PM.
- Finally, press Enter.
Step 3:
- To find the average time excluding 3:48:00 PM, type the following formula.
=AVERAGEIF(C5:C10,"<>3:48:00 PM",C5:C10)
As a consequence, as seen in the figure below, the average time will be displayed.
Read More: How to Calculate 7 Day Moving Average in Excel (4 Ways)
Similar Readings
- Calculate Average of Text in Excel (2 Ways)
- Moving Average for Dynamic Range in Excel (3 Examples)
- How to Exclude a Cell in Excel AVERAGE Formula (4 Methods)
- [Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)
3. Calculate Average Time from Timestamp of Day
For example, say you’ve logged the entry timestamps of every time a certain user entered a website in Excel, and now you want to average the timestamps to forecast the most likely time this person would access the website in the future. So, to calculate the average timestamp, follow the instruction below.
Step 1:
- Simply type the following formula.
=AVERAGE(C5:C10)
As a result, you can see that the average is presented, but it is not the same as the overall average time. The dates were not in the same interval; therefore, it differs.
Read More: How to Calculate Average of Text in Excel (2 Ways)
Conclusion
To sum up, I hope this post has given you a thorough understanding of how to calculate average time in Excel. You should learn all of these processes and apply them to your dataset. Examine the practice booklet and put your newfound abilities to the test. Because of your invaluable support, we’re inspired to continue creating lessons like this.
Please do not hesitate to contact us if you have any queries. Please feel free to offer your thoughts in the comments area below.
The ExcelDemy Team is always available to answer your questions.
Stay with us & keep learning.
Related Articles
- How to Calculate VLOOKUP AVERAGE in Excel (6 Quick Ways)
- Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)
- How to Generate Moving Average in Excel Chart (4 Methods)
- Calculate Exponential Moving Average in Excel
- How to Calculate Average Percentage of Marks in Excel (Top 4 Methods)
- Excel Formula to Find Displaced Moving Average (With Easy Steps)
- How to Calculate Average and Standard Deviation in Excel