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!
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!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Today, we have a question from Jonathan in Mission Viejo, California.
If you have a question for the netcast, please give us a call 8665810221 or Skype or even off us an email.
And we'll get to your question on a future podcast.
Here's Jonathan's question, Hi Bill, this is Jonathan in Mission Viejo, and I have a question on sorting spreadsheet that we get from offender.
The question is, I've got a column called D.
The contents of the cell is the date and time.
Only the date shows up.
If it's formatted as a date only sometimes shows up.
If it's format it is time.
But in the formula bar you can see both the date and the time what I want to do is strip out the date.
So that I can sort by the time.
The one of the how many occurrences happen by the hour, and I'm unable to figure out how to get rid of the day.
Now, thanks for your help.
Okay! Great call.
So, Jonathan sent along an email with a sample of the data set and sure enough you can see here in Column E.
They have it Call Date.
That's only showing the date, but when you look in the formula bar, you can see that it actually has the date and the time.
Okay!
Now, there's two ways to solve this problem in both of them involve inserting a new column, where we're going to get just the time information.
So if I insert a column...
My first solution is a little bit tricky because it Involves a knowledge of how Excel stores dates and times.
Dates are stores as the number of days since January 1st,1900.
So, to go from Tuesday to Wednesday is basically adding one every day is equal to 1.
And times are stored as a decimal portion of the day, so 6:am is stored as 0.25, noon is store to 0.5 etc.
So, one way to solve this problem is simply to take the date and time that's in cell E2.
and subtract the integer portion of that.
I'm going to use the INT function and we will end up with a decimal portion of the day.
Now, I'm gonna have to use format cells to go in and say that this is now a time Showed us 1:30 pm and you'll see that when I copy this formula down, we now have just the time portion.
And if I converted this to values, you see in the formula bar that we really just have the time.
It's not that we've formatted the date and time to just show the time.
We really truly just have the time.
Let me do that CTRL+C and here is V and now in the formula bar, you'll see that we have just the times there.
Now Jonathan said that he was interested in looking at information by hour.
So, a much better way to do this might be to use the HOUR function.
If we type =HOUR and use E2, will have to format cells to go back and show this as a number now instead of a time.
I want to show it as an integer.
and you'll see that we have that this occurred at the 17th hour.
So, in other words five o'clock If I copy that down, you'll see that we've now grouped it into 17, 18, 19 10, 14 and so on.
Those are the actual hours within the day and so that might be a better way to do the analysis because everything is already naturally now grouped by hour.
So, great call from Jonathan.
Two solutions there to either use the date minus the integer portion of the date or to use the HOUR function, to convert those dates and times into something as truly times.
If you have a question for the podcast, please give us a call.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I am Bill Jelen.
Today, we have a question from Jonathan in Mission Viejo, California.
If you have a question for the netcast, please give us a call 8665810221 or Skype or even off us an email.
And we'll get to your question on a future podcast.
Here's Jonathan's question, Hi Bill, this is Jonathan in Mission Viejo, and I have a question on sorting spreadsheet that we get from offender.
The question is, I've got a column called D.
The contents of the cell is the date and time.
Only the date shows up.
If it's formatted as a date only sometimes shows up.
If it's format it is time.
But in the formula bar you can see both the date and the time what I want to do is strip out the date.
So that I can sort by the time.
The one of the how many occurrences happen by the hour, and I'm unable to figure out how to get rid of the day.
Now, thanks for your help.
Okay! Great call.
So, Jonathan sent along an email with a sample of the data set and sure enough you can see here in Column E.
They have it Call Date.
That's only showing the date, but when you look in the formula bar, you can see that it actually has the date and the time.
Okay!
Now, there's two ways to solve this problem in both of them involve inserting a new column, where we're going to get just the time information.
So if I insert a column...
My first solution is a little bit tricky because it Involves a knowledge of how Excel stores dates and times.
Dates are stores as the number of days since January 1st,1900.
So, to go from Tuesday to Wednesday is basically adding one every day is equal to 1.
And times are stored as a decimal portion of the day, so 6:am is stored as 0.25, noon is store to 0.5 etc.
So, one way to solve this problem is simply to take the date and time that's in cell E2.
and subtract the integer portion of that.
I'm going to use the INT function and we will end up with a decimal portion of the day.
Now, I'm gonna have to use format cells to go in and say that this is now a time Showed us 1:30 pm and you'll see that when I copy this formula down, we now have just the time portion.
And if I converted this to values, you see in the formula bar that we really just have the time.
It's not that we've formatted the date and time to just show the time.
We really truly just have the time.
Let me do that CTRL+C and here is V and now in the formula bar, you'll see that we have just the times there.
Now Jonathan said that he was interested in looking at information by hour.
So, a much better way to do this might be to use the HOUR function.
If we type =HOUR and use E2, will have to format cells to go back and show this as a number now instead of a time.
I want to show it as an integer.
and you'll see that we have that this occurred at the 17th hour.
So, in other words five o'clock If I copy that down, you'll see that we've now grouped it into 17, 18, 19 10, 14 and so on.
Those are the actual hours within the day and so that might be a better way to do the analysis because everything is already naturally now grouped by hour.
So, great call from Jonathan.
Two solutions there to either use the date minus the integer portion of the date or to use the HOUR function, to convert those dates and times into something as truly times.
If you have a question for the podcast, please give us a call.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
