For example, you have recorded the login timestamps of every time a specific user accessed a website in Excel as following screen shot show, and now you want to average these timestamps for predicting the most possible time this users will access the website in future, how could you get it done? Here I will introduce two ways to average timestamps of days in Excel.
Average timestamps of days with a help column in Excel
We can calculate the time of every day in a help column, and then average the time in the help column easily in Excel. You can do as follows:
1. Besides original Timestamps column, type the word “Time” into the Cell B1, enter =A2-INT(A2) into Cell B2, and then drag its Fill Handle to the Range B2:B6. See screen shot:
Note: In the formula of =A2-INT(A2), A2 is the first timestamp in Timestamps column, and you can change it based on your needs.
2. Keep selecting the time cells in the help column, and then format them as time with clicking the arrow in Number Format box > Time on Home tab. See screen shot:
3. Select the blank cell below help column, in our case select Cell B7, enter the formula =AVERAGE(B2:B6) into it and press the Enter key.
Now you will get the average time of these timestamps of days at once.
One click to batch convert multiple time to hours/minutes/seconds without multiplying 24/1440/86400
For example you have a time of 2:23:42, you can convert this time to hours by multiplying 24 (or multiplying 1440 to minutes, multiplying 86400 to seconds; in other words, we can apply the formula =F2*24 for converting the time to hours, =F2*1440 to minutes, =F2*86400 to seconds), and then change the formula cell to Number format. But, with Kutools for Excel ’s Convert Time utility, you can one click to batch convert multiple time data to hours/minutes/seconds only in Excel. Full Feature Free Trial 30-day!
Average timestamps of day with an array formula in Excel
Actually, we can apply an array formula to average timestamps of days directly in Excel. You can do as follow:
1. Select any blank cell, says Cell B4, enter the formula =AVERAGE(--TEXT(A2:A6-INT(A2:A6),"h:mm")), and then press the Ctrl + Shift + Enter keys simultaneously.
Formula is too complicated to remember? Save the formula as an Auto Text entry for reusing with only one click in future! Read more…Free trial
2. Then format the average result to time format with selecting the Cell B4 and clicking the arrow in Number Format box > Time on Home tab. See screen shot:
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.
I have used =INT(B5-A5)&" Days "&HOUR(MOD(B5-A5,1))&" Hour "&MINUTE(MOD(B5-A5,1))&" Minutes" to find the days, hours and minutes between 2 dates. Now I need to find the average of all the days, hours and minutes. I'm having a hard time coming up with an AVERAGE formula that will work with my previous formula. Please help! A B C D E 1 Logged date Resolved date Resolution Time (Resolved date – Logged date) Average Resolution Time 2 20-12-15 16:09 01-06-16 16:42 164 days, 0 hours, 33 minutes and 0 seconds 3 23-12-15 11:56 01-05-16 16:50 130 days, 4 hours, 54 minutes and 0 seconds 4 28-12-15 12:25 01-05-16 16:47 125 days, 4 hours, 22 minutes and 0 seconds 5 30-12-15 10:06 01-02-16 17:37 33 days, 7 hours, 31 minutes and 0 seconds 6 30-12-15 10:06 01-04-16 17:06 93 days, 7 hours, 0 minutes and 0 seconds
in Number Format box > Time on Home tab. See screen shot:
