Sign in or start a free trial to avail of this feature.
14. Add and Subtract Times Part 2
In this lesson, we'll find out how often a new user signs up by calculating the distance between two times in hours and minutes.
Finding the duration between two times
- Finding the duration between two times is straightforward, unless the duration exceeds 24 hours
- To solve this problem, put square brackets around 'hh' in the custom time format
CTRL + SHIFT + ' : Convert to time format
CTRL + SHIFT + →: Select all cells within data region
CTRL + →: Move to the last cell in the data region
CTRL + Page Up/Page Down: Move to next sheet
CTRL + 1: Open format cells dialog box
ALT + E, S, T: Paste formats
Our management team would like to measure how often users signed up on average in the month of January.
To do this, we'll calculate the duration and time between consecutive sign ups and then calculate the average of this range.
In the data set I've created a new column called Time to previous sign up.
And starting with the second sign up, its formula will be equals the current sign up minus the previous sign up.
And this gives us an answer of 01:35.
Now I'll need to change the format slightly with Ctrl + Shift + single quote that moves the value into the time format.
I can now autofill for the remaining cells.
We can then go back to our results page and write equals average, open a bracket, select the range, close the bracket and press enter.
And this tells us that a user signs up on average every two hours and 28 minutes.
This seems relatively straightforward, but unfortunately it's not the whole story.
For this data range, you can see that the distance between times never exceeds 24 hours.
But watch what happens when I add another value to the bottom of the data set that's a couple of days away from the previous sign up.
Let's say the 3rd of February 2012 and 4 PM.
I want to now copy the formula, you can see that the answer is 64 hours.
One alternative to this format would be to include the number of days, the number of hours and then the number of minutes in our output format.
To do this, we'll go back into the format cells dialog box and this time we'll change our Custom type to simply dd for day, remove the brackets and then press OK.
And this displays the distance between dates as 2 days, 16 hours and 0 minutes.
I actually prefer viewing it in the total number of hours, so I'll switch back with Ctrl + Z.
So when you're measuring the time duration between dates, always make sure that you've a format in place that can either display the number of days, hours and minutes, or preferably, the total number of hours beyond a 24 hour limit.