How to Calculate Time in Excel
(TIME Function)

You must have used Excel to play around with numbers and currency and maybe dates too, but did you try working around with time in Excel?

If not, you are missing a powerful tool that Excel can make for you to handle time-related calculations 🔨

The best part about using Excel for this job is that it guarantees accuracy and precision like none other. Ready to streamline your time calculations and related workflows in Microsoft Excel?

Get your hands on our free practice workbook for this tutorial and let’s go reading.

Table of Contents

The TIME function

The TIME function is a powerful function that comes from the DATE/TIME section of Excel’s Functions library 🕖

It is used to calculate the time out of a given value of hour, minute, and second.

The best use of the TIME function is when it is nested in other functions to calculate the proper time.

Syntax

The syntax of the TIME function is as follows:

You must not have seen any Excel syntax being this simple and synchronized ever before. All three arguments of the TIME function are required arguments and here’s what they require.

HOUR – the hour argument accepts arguments in the form of numbers from 0 to 32767. If you write 0, it will return the hour as 12:00 AM, for 13 it would return 01:00 PM, and so on.

This sorts out the result for the numbers 0 to 23. However, for numbers greater than 23, the TIME function will divide the number by 24 and return the remainder as the hour. If you write 64, it will return the result as 4:00 PM (64/24 = 2 and 16 remainder) ⌛

MINUTE – the minute argument also accepts arguments in the form of numbers from 0 to 32767.

For numbers greater than 59, the number is converted into hours and then minutes. If you write the TIME function as TIME (0,128,0), it will be TIME(2,8,0) yielding the time as 02:08 AM. As 2 hours and minutes total up to 128 minutes.

SECOND – the second argument accepts arguments in the form of numbers from 0 to 32767.

For numbers greater than 59, the number is converted into hours, minutes, and then seconds. If you write the TIME function as TIME (0,0,128), it will be TIME(0,2,8) yielding the time as 12:02 AM. As 2 minutes and 8 seconds total up to 128 seconds.

Supplying numbers bigger than 32767 will cause the TIME function to return the #NUM! Error.

Using the TIME function to calculate time

The TIME function is quite easy. Let’s see it working through practical examples.

Here are a few numbers out of which I want to compute the time.

We have the value for hours, minutes, and seconds. Let’s calculate the time out of it.

Step 1) Write the TIME function as follows:

Step 2) Refer to the value of the hour as the first argument.

Step 3) Refer to the value of minutes as the second argument.

Step 4) Refer to the value of seconds as the third argument.

Step 5) Press enter.

Excel calculates the time as 12:59 PM.

But the seconds are missing. It’s only about the formatting of the time i.e., it is not formatted to show the seconds.

Step 6) To adjust the format of the time, press Ctrl key + 1 to launch the Format Cells dialog box.

Step 7) From the pane on the left, select Time formats.

Step 8) Select the format of time as desired. I have selected the one that shows seconds too.

And Tada! The format of time has changed to now include seconds as well.

Step 9) Drag this formula down the whole list to calculate the time for all numbers.

Makes time calculation in Excel quite easy 🚀

Add or Subtract time in Excel

The TIME function might not be useful enough if you do not use it to add and subtract time in Excel 🎯

Using the TIME function to add or subtract time in Excel is important since Excel treats TIME as a decimal number. The way it is displayed is only the formatting – but on the backend, Excel stores time as a decimal number that’s somewhere between 0.0 (representing 0:00:00 or 12:00:00 AM) to 0.99988426 (representing 11:59:59 PM).

Check this out – apparently, we have time in Excel that’s 5:43 AM.

However, just if you change the format for this cell from Time to General, the time changes into a decimal number.

This is how Excel stores it 💁‍♂️

Adding Time in Excel

Here I have a list of times in Excel and then some hours that I want to add to it.

If I simply add the hours to the time, this is what happens:

Nothing changes apparently. This is because Excel treats one day (24 hours) as equal to 1. So as you add 12 to time, Excel adds 12 days (24 hours *12) to it.

How would Excel know if these are hours 🤯

Step 1) To tell this to Excel, nest the hours in the TIME function as follows:

This time Excel correctly adds 12 hours to 12:59 AM.

Step 2) Drag this formula down the list to add up the hours from the remaining list.

If not just hours, but we also have minutes in Excel to be added to a given time (like below), what is to be done then?

Step 3) Write up an addition formula using the TIME function as below.

Excel adds 12 hours and 59 minutes to 12:59 to return the time to 1:58 AM ⌚

Step 4) Drag this formula down the list to add up the hours and minutes from the remaining list.

Subtracting Time in Excel

I have two lists of times and I want to find the difference (elapsed time) between both time stamps.

To find the time elapsed between both these time stamps.

Step 1) Write a simple subtraction operation through the following formula.

We get back the difference between both as a decimal number. Just needs some formatting 🧾

Step 2) Press Control + 1 to launch the Format Cells dialog box.

Step 3) Go to Custom Formats from the pane on the left and select the format “hh:mm:ss”.

This will show the difference between both, the time stamps in the form of hours:minutes:seconds elapsed.

Step 4) Press enter to see the result.

12:59 tells that 12 hours and 59 minutes have elapsed between the first and the second time pass.

Step 5) Drag this formula down the list.

Rest assured – but there’s some irregularity in between. What are those hashes?

If the result is negative, like in Row 3, if we deduct 1:13 PM (0.551) from 12:00 AM (0.000347) we get a negative -0.5506 💡

When this decimal number is formatted as time, Excel returns a hash series like the above instead of the correct result.

However, this can be fixed in multiple ways. One of which is to wrap the above formula in an IF function that forces the negative result to be posted properly with a negative sign.

To do this:

Step 6) Write the IF function as below.

The IF function works to see:

• If the result of the time difference is greater than (hence positive).
• If yes, it simply returns the result.
• If not, it first passes the result through an absolute function that changes the negative value into a positive value. This positive value goes through the TEXT function that brings it into HH:MM:SS format with a preceding negative sign 🧐

The results of the IF function look as follows:

All the other time differences remain the same, and the negative time difference is correctly returned as a negative difference.

Conclusion

Frankly, I love the date and time functions of Excel. They just ease out the complex formats of date and time and the accuracy issues that come along.

There are just so many of them out there, and each of the date and time functions has an indispensable utility of its own. So, if you are in your time and date function acing journey, don’t miss out on the following MS Excel tutorials of mine.

• Excel NOW Function Guide: Get Current Date and Time (2024)
• Convert Time Into Decimals in Excel (Hours/Minutes/Seconds)
• How to Remove Time From Date in Excel (2024 Guide)