Calculating date intervals

1. Calculating date intervals

Great job so far! Those dates and times are really starting to sparkle.

2. Recap...

Previously, we cleaned our dates and times by extracting components using some handy built-in functions. This allowed us to extract only the data required for the analysis. In this lesson, we'll look at other ways of working with and preparing dates and times, such as calculating durations and intervals. Let's get to it!

3. When is now?

When analyzing date and time data, we are often interested in how something compares to the present date or time, such as people's ages or 100 meter sprint times. The problem with manually typing the present date or time into our spreadsheets formulas is that it's always changing! We need a way to return the present date and time that will update automatically.

4. TODAY()

The TODAY function returns today's date in month-day-year, calculated based upon the timezone settings in the spreadsheets application. Notice that TODAY doesn't take any arguments, but we still need opening and closing parentheses. The value displayed in the cell will update any time the spreadsheet is refreshed.

5. NOW()

The NOW function is very similar, but returns the date and time to the nearest second. Likewise, if the spreadsheet is refreshed, the cell will also update.

6. Date arithmetic

We can quickly find the interval between two dates by subtracting them in a formula. Let's return to the dates of our manned moon mission, and calculate the interval between landings.

7. Date arithmetic

The first interval value will be blank, as the Apollo 11 mission was the first. Subtracting the cell references for the first two missions

8. Date arithmetic

results in 121. This is because subtracting dates always returns the interval in days.

9. Date arithmetic

Copying the formula downwards, we can see the longest interval was between the second and third successful landings. This long interval was due to the unsuccessful Apollo 13 mission, which should have landed in 1970. In this case, having the interval in days is an appropriate scale, but if we were calculating people's ages, having our results in days wouldn't provide much insight.

10. DATEDIF()

This is where the DATEDIF function comes in handy. The function takes three arguments: the start date, the end date, which must be after the start date, and the unit to return, which takes a string representing a year, month, day, or others that we won't cover in this course. Returning to the moon landings, we can use DATEDIF to return the interval in months by specifying references to the start and end dates, and the string "M".

11. DATEDIF()

Similarly, we can return the interval as a year with the string, Y. Note that when calculating intervals, the results are chopped rather than rounded to the nearest unit, so an interval of 11 months would return zero years. We can also find the number of years between the landings and today's date

12. DATEDIF() and TODAY()

by inserting the TODAY function as the second argument to DATEDIF.

13. DATEDIF() and TODAY()

Because we used the TODAY function, these intervals will update as time passes.

14. Let's practice!

It's time to head on over to the exercises!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.