Sign in or start a free trial to avail of this feature.
9. Add and Subtract Dates
To analyse to time-series data effectively, you need to know how to add and subtract date values. Here I show you how this can be used to find # sign ups in the last 7 days for our startup.
COUNTIF: Counts number of cells in a range that meet 1 criterion
DAY: Returns the day value from a date
CTRL + Page Up/Page Down: Move to next sheet
ALT + I, C: Insert column
CTRL + →: Move to end of data region
CTRL + SHIFT + →: Select all cells within data region
Once we have our dates stored as values we can perform some useful time series analyses by simply adding and subtracting dates.
It turns out that our startup would like to analyze how many users have signed up in the last week, the last 14 and then the last month.
Before we attack this problem, let's first see what happens when we add and subtract date values.
So off camera, I've included two samples dates on the left, there in the date format and on the right, the exact same values are shown in the general format.
If I subtract one date from the other taking the 8th of May 2012 and subtracting the 24th of January, Excel simply returns the number of days as a number.
And it does this by subtracting the values stored in the right hand side.
If I add a number to a date, Excel is simply adding the number 105 to the number 41073 and then converting it back into a date which in this case is the 21 of August 2012.
So as you can see, performing addition and subtraction on dates in Excel is very easy.
Let's now apply this learning to our problem.
To find the number of sign ups in the last 7 days, we simply need to count the number of sign ups that are greater than the 31 of January 2012, minus 7.
To do this, I'll use a COUNTIF function.
So I'll write equals COUNTIF, open the bracket, and first select a range, which is simply going to be the column of dates.
And now I'll need to include the criteria.
The criteria needs to be included as a string, so I'll write doubles quotes and then greater than.
Close the doubles quotes, and then ampersand sign and select the cell, which is the 31 of January 2012, minus the cell D5, which corresponds to 7.
I'll then close the bracket and press enter.
And this tells me that 61 users have signed up in the last 7 days.
Let's now see how many signed up in the last 14 days.
So again I'll write equals COUNTIF, I'll open a bracket and select the range, which will the column of dates.
I'll then write a comma, and for my criteria I'll open double quotes and select greater than, then the ampersand sign, and then select the date minus the number of days, which in this case is 14.
I'll close the bracket and press enter.
And as you can see, 139 users signed up in the last two weeks.
Our final task is to find the number of users that have signed up in the last month.
Unfortunately, the number of days we subtract from our end date for this metric will vary depending on the month that we're in.
While I could had quote the number of 31 days for January, I prefer to write a formula that automatically calculates the number of days in each month, be it February, March or April.
The function I'm going to use to complete this task is called day.
And the day function simply accepts a date and then returns the day value of that date.
So I'll write equals day, open a bracket, and then accept our date as the argument.
And when I close the bracket, you can see that the function returns 31 for the 31 of January 2012.
Let's say that the month in question was February, then I'd write the 28 of February and the number would change accordingly.
As you can imagine, Excel also has a month function and a year function, which accept the date as an argument and return the month and the year of that date.
As an exercise, try and complete the COUNTIF equation for the number of sing ups in the last month in the last remaining yellow cell.
I'll leave my answer in the after file below the video.