Adding Time in Excel

There's a function for that

If you need to add time to an existing time then you need to learn about the TIME function.

Most people don’t know that the proper way to enter time is using the colon. So 0:30 equals 30 minutes.

Often we work with time as if it was a decimal. For example entering 30 into a cell and then want to use that cell’s value to add 30 minutes to an existing time.

What is Time?

In Excel the answer to this is that time is a fraction. A fraction of a day to be exact. So 0.25 is 6am (6 hours) 0.5 is noon (or 12 hours) and 0.75 is 6pm (or 18 hours).

To convert time to a decimal you typically multiply the fraction by 24 to convert it to decimal hours.

If a cell contains 30 and you want to treat that as 30 minutes you can use the TIME function.

In the image below column A has the start time. Column B has a column of numbers that represent minutes. Column C has a formula that add the minutes to the start time.

The formula in cell C2, which has been copied down, is.

=A2+TIME(0,B2,0)

The TIME function has three arguments. In sequence they are hours, minutes and seconds. You can use zero (as above) or leave an argument blank as shown in the following formula, which will also work.

=A2+TIME(,B2,)

I prefer the first formula where you have the zeroes in as place holders for the arguments that you are not using.

The TIME function makes it easy to use decimal numbers and convert them to hours and minutes.

As you can see from row 5, you can exceed the 60 minute limit and the calculation will still work.

The image below shows that you can also deduct time.

 

When adding, or deducting, time the TIME function simplifies the process.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.