Look Who it Was Random video

Excel - Analyzing Data by Hour - Two Solutions for Sorting Spreadsheet by Time - Episode 494

754 views · Published 21 April 2009 · 3:53 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Analyzing Data by Hour - Two Solutions for Sorting Spreadsheet by Time.

Welcome back to the MrExcel netcast! In today's episode, we have a question from Jonathan in Mission Viejo, California. If you have a question for the netcast, please give us a call at 8665810221, Skype us, or send us an email. We love hearing from our viewers and will do our best to answer your questions in a future podcast.

Jonathan's question is about sorting a spreadsheet that he received from a colleague. He has a column with both date and time information, but he only wants to sort by the time. After taking a look at the sample data set he provided, we have come up with two solutions to his problem.

The first solution involves using the INT function to strip out the date and leave only the time. This works because Excel stores dates as the number of days since January 1st, 1900 and times as a decimal portion of the day. By subtracting the integer portion of the date and time, we are left with just the time information. We then use the format cells option to display this as a time, and voila! We have a column with just the time information that can be easily sorted.

The second solution involves using the HOUR function to convert the date and time into just the hour information. This is a more efficient way to analyze the data, as it groups everything by hour naturally. By using the HOUR function, we can easily see how many occurrences happened in each hour. This is a great solution for Jonathan's specific needs.

Thank you, Jonathan, for your question and for providing a sample of your data set. We hope these solutions help you with your sorting needs. And to all our viewers, if you have a question for the podcast, please don't hesitate to reach out to us. We love hearing from you and will do our best to answer your questions in a future netcast. Thanks for tuning in, and we'll see you next time on the MrExcel netcast!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents:
(00:00) Introduction and question from Jonathan 
(00:20) How to submit a question for the netcast 
(00:30) Jonathan's question about sorting a spreadsheet 
(01:14) Bill's response and introduction to the data set 
(01:27) Two solutions for stripping out the date and sorting by time 
(02:34) Using the HOUR function for better analysis 
(03:31) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Analyzing data by hour in Excel
Converting date and time to just time in Excel
Converting time to values in Excel
Formatting cells in Excel
Formatting dates and times in Excel
Grouping data by hour in Excel
MrExcel netcast tutorials and tips
Sorting spreadsheet by time
Stripping out date from a cell in Excel
Subtracting integer portion of a date in Excel
Using HOUR function in Excel
Using INT function in Excel

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152370/



Jonathan from California has a column with date & time information, but would like to strip out just the times to do an analysis by hour. Episode 494 shows you two formulas that can be used to analyze the data by hour.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

More from this channel