Add and Subtract Hours

I was recently working on a workbook, and had to convert an event time from one time zone to another. Basically, I wanted to enter the time of day into a cell, and then I needed a formula to compute the event time in a different time zone. This post walks through the formula to add and subtract hours, which can be helpful when translating event times between time zones.

Objective

Before we get into the formula, let’s talk about our goal. The goal is to have an input cell that we can use to enter a time of day, such as 9:00 AM. Let’s say the entered time represents the event’s start time in the Central time zone. This is illustrated in the screenshot below.

Then, under the input cell, we’d like to have formulas that compute the event start time in four US time zones. This essentially boils down to adding and subtracting hours, as shown below.

Alright, let’s get to it.

Details

It feels intuitive to simply take the event start time, for example 9:00 AM, and just add or subtract the desired number of hours. But, when you do, it doesn’t seem to have any impact. For example, if the input cell C5 had a value of 9:00 AM, and you write a formula that adds one, something like =C5+1, the result (when formatted as a time) seems to return 9:00 AM.

Excel doesn’t appear to add anything. This is because of the way Excel stores dates and times. The date part is stored to the left of the decimal, and the time part is stored to the right of the decimal. This means that adding 1 is actually adding 1 day, not one hour. If the formula cell is formatted as a time, it doesn’t display the date part, so, the formula doesn’t appear to do anything.

So far so good? Good. With this in mind, let’s turn back to our objective. We can add an hour by adding the corresponding decimal value. We know there are 24 hours in one day, so, we can get the corresponding decimal value by dividing the number of hours by 24. That means, instead of adding 1, we actually add 1/24. To add two hours, it would be 2/24, and so on.

To make the formula easier to manage, I stored the number of hours to add or subtract in row 11. Then, I essentially added (or subtracted) that number divided by 24 to the input cell C5. This is illustrated in the screenshot below.

And, now you know the trick to adding and subtracting hours in Excel!

If you have any additional tips for working with times, please share by posting a comment below.

Resources

• Sample Excel file (zipped) – Updated: TimeZones_Updated
• Sample Excel file (zipped) – Original: TimeZones