13. Add and Subtract Times Part 1

 
Subtitles Enabled

Sign up for a free trial to continue watching this lesson.

Free trial

Overview

It's more difficult to perform calculations with times than with dates, but a few tricks can help make it a simple. Find out these tricks in this lesson.

Lesson Notes

Keyboard shortcuts

ALT +I, C: Insert column
F2: Jump back into formula
CTRL + Page Up/Down: Move to adjacent sheet
ALT + I, R: Insert a row
F4: Anchor cells

Transcript

Adding times is slightly more complicated than adding dates in Excel.

This is because each date has a value of 1, while each hour has a value of 1.24th.

Let's say our startup would like to send an email automatically 7 hours after a user has signed up.

The email would welcome the user to the website and contain some information showing how to use it.

To measure the impact of this email, we'd like to calculate at what time this email was sent.

So I'll start by creating a new column, and I'll call it Email sent at, and I'll rename the first column to be Signed up at.

To add 7 hours to the sign up date, we might be tempted to simply take the date and add 7.

But when we do this, you can see that Excel interprets the 7 as 7 days and returns the 8 of January, which is definitely not what we want.

Instead, we must add 7.24ths.

So I'll jump back into the formula and divide the 7 by 24.

This now records the correct time at which the email was sent.

While this method works Okay for round numbers, such as 7 hours, you can imagine that the math could get quite complicated if we were adding hours and minutes to a specific date.

What's more, I don't like hard coding values such as 7.24ths into time formulas because it's more difficult for other users to understand.

Instead, we'll find another way to add 7 hours to the Sign up date value.

We can in fact add a string that Excel interprets as a time, which will serve the same function.

So instead of writing 7.24ths I'll jump back inside the formula, open double quotes and write 7:00 and close the double quotes.

When I press enter, you can see that Excel interprets this as 7 hours, which is now more understandable for other users who may be using the data set in the future.

If it's likely that the company will change the amount of time from 7 hours in the future, then we might be better off creating a variable in our results page that will determine the amount of time between sign up and the sending of the email.

So I'll create some new rows with Alt + I R and I'll write Email delay duration in the cell.

And in the cell to be referenced, I'll simply write 7:00, which Excel interprets as a time.

I'll then go back to my cell and then press F2 to jump into the formula, and select this new cell.

And I'll anchor it with F4.

And now I can autofill for the remaining cells.

If we decide to change the Email sent at in the future, we can then simply change this, say to 10 hours, and the dates will update accordingly.

In the next lesson, we'll continue to focus on this topic and explore the ways in which the duration between two times can be measured.