15. Analyse Data Within Time Intervals
We'd often like to perform COUNT and SUM calculations within certain time intervals. Find out how to do so in this lesson
Separate time from DateTime
- Create new column
- Subtract INT(DateTime) from DateTime
- Format the remaining time value in the desired format
=INT: Rounds a number down to the nearest integer
CTRL + SHIFT + #: Convert to General format
CTRL + SHIFT + ': Convert to Time format
ALT + E, S, T:Paste Formats
F4: Anchor cells
CTRL + →: Move to end of data region
CTRL + SHIFT + →: Select all cells within data region
SHIFT + →: Select adjacent cell
CTRL + Page Up/Page Down: Move to next sheet
To gain more insight into our customer base, our management team would like to examine what time of day our customers are most likely to sign up.
This could provide some interesting insights on customer behavior and impact our marketing campaigns.
In the results page I've created a list of eight, three hour time periods.
I'd like to see how many sign-ups occur during each of these periods.
To do this we must first separate the time from our timestamp back in the data set.
If you recall from the previous lesson you might remember that dates are stored as whole numbers and times as fractions of a day.
If I press Ctrl + Shift + # to return to the general format you can see the date part of the value and the time part.
To separate time I must simply take the WHOLE NUMBER and subtract it from the existing SIGNED UP AT value.
To do this I'll use the INT function.
I'll first write equals and then accept the SIGNED UP AT value and then MINUS INT and open a bracket.
When I pass this value into INT it simply removes all of the characters after the decimal point.
And so subtracting it from B2 will leave me with just the time value.
If I format this value as time with Ctrl + Shift + single quote, you can see we've extracted the time successfully from the timestamp.
I'll then autofill for the remaining values.
I'll also paste formats to convert my first value back into the timestamp, Alt + E S T.
I can now use a COUNTIF statement to check if our time is greater than the lower band and smaller than the upper band.
So I'll return to our results page and write equals COUNTIFS and the first input will be the criteria range.
So I'll go back to my data set and select the Sign up time.
And I'll anchor this with F4 because I'll be copying and pasting for seven more values in the future.
The next value is going to be the criteria.
And the first criteria is that the time is greater than or equal to midnight.
So I'll open brackets, write greater than or equal to, close brackets, write an ampersand sign and then select my lower band.
I'll write a comma and now select the criteria range for my second criteria, which will actually be the same range as the first criteria, the Sign up time.
So Ctrl + Page Up to return to the data set and select the sign up time range.
And F4 to anchor.
I'll then write a comma and fill out my last criteria which will be double quotes, less than, close double quotes, ampersand sign and the upper band.
I'll then close the bracket and press enter.
And this tells me that 30 sign ups happened between midnight and 3 AM for every day during the month of January.
I'll then autofill by double clicking in the bottom right hand corner.
The results in this table lead me to believe that the company has users from all over the world in many different time zones.
Because it would be very unlikely that more individuals would sign up between three and six in the morning than three and six in the afternoon.
As a result we would need to know the location or time zone of each user to accurately gauge the time of day that they are most likely to sign up.
Often when working on analyses like this you'll find that the results require some intuitive knowledge of the user base and may point to some additional information that you'll need to collect.