One click to batch convert multiple time to hours/minutes/seconds without multiplying 24/1440/86400

• Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
• More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
• Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
• Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
• Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
• Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
• More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

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:

Step 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.

Step 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:

Step 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.

Actually, we can apply an array formula to average timestamps of days directly in Excel. You can do as follow:

Step 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

Step 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:

Average by year/month/date in Excel

Average per day/month/quarter/hour with pivot table in Excel

Weekly average in Excel