How to Calculate Time in Excel?

How to Calculate Time Difference in Excel (Elapsed Time)?

How much time has passed between two dates is information you use for many purposes (e.g., calculating age, time with the company, time spent working on a project, overtime, worked hours, etc.). It’s probably the most used formula in the book. However, there are two different situations you need to consider:

• The time difference between two dates (over 24 hours difference)
• The time difference between two times of the same day (often, under 24 hours difference)

You should also establish the unit measure for the result from the beginning. For example, you can measure the time difference between two dates in years (e.g., age), months (e.g., time spent working on a project), or days (e.g., leave balance). The time difference between two times on the same day is usually measured in hours and minutes, but you can include seconds if they are relevant to your measurement (e.g., time between two computer program runs).

Calculating the Time Difference between Two Dates

The first thing you need to do is format the cells storing the two dates as Date values. Select them, right-click, and choose Format Cells… from the drop-down menu. Then, select Date from the Category panel and one of the types from the Type list. Press OK, and you will be good to go.

 Enter the dates you want to subtract into the A2 and B2 cells and add the formula “=B2-A2” in the C2 cell. Excel will compute the difference in days between the two dates. However, if you need the difference in months or years, adjust the formula accordingly. The formula for getting the result in months is “=(B2-A2)/12,” and the formula for getting the result in years is “=(B2-A2)/365”.

The values in the Result cells are stored as numbers. You can format them to show just two decimals or none at all. 2.054794521 years is not very relevant data and can mess up your calculation. So, select the Result cells, right-click, and choose Format Cells… from the drop-down menu. Then, select Number from the Category panel and set Decimal places to 0. Excel will round the value if you want to stick to the highest integer value (e.g., 62.5 months to be 62, not 63) and use the function INT().

Calculating Age on the Fly

Calculating age is a particular case of calculating the time difference between two dates. However, it is so used in people management and HR processes that it needs a shortcut. When calculating the age of your employees, you need the result in years. Computing the result in days and then converting to years is not the optimal option. 

Excel 365 provides the function DATEDIF(), which computes the difference between two dates and gives the result in years (“y”), years and months (“ym”), or years, months, and days (“ymd”). Given that the employee’s date of birth is in cell A2, the formula is “=DATEDIF(TODAY(), A2, “y”)”.

However, if you are using older versions of Excel, you don’t have the function DATEDIF(). In this case, you can use the function YEAR() to compute the difference between the current year and the birth year of the employee and find out what age the employee will be this year. The formula is “=YEAR(TODAY())-YEAR(A2)”. 

If you need the exact age of an employee at the current date, you can use the formula “=INT((TODAY()-A2)/365)”. It calculates exactly how many days there are between the current date and the date of birth, converts the result into years, and rounds to the largest integer, more minor than the result.

You can calculate an employee's age at any time by replacing the current date (TODAY() function) with a date of your choice. Select the cells, right-click, and choose Format Cells… from the drop-down menu. Then, select Time from the Category panel and one of the types from the Type list. Press OK, and you will be good to go.

Then, it would be best to format the Result cells to store hours, hours, and minutes, or hours, minutes, and seconds. Select them, right-click, and choose Format Cells… from the drop-down menu.

Calculating Hours Elapsed Between Two Times

As before, the first thing you need to do to calculate the time difference between two times is to format the cells to store time values. Then, select Custom from the Category panel and h:mm:ss from the Type list. If you only want hours, replace h:mm:ss with h. If you want hours and minutes as an outcome, replace it with h:mm. And so on.

 However, if your Result cells hold only hours, minutes, or seconds, the values must be smaller than 24 hours, 60 minutes, and 60 seconds, respectively. If you want to store values higher than those limits, you need to use [h], [mm], and [ss] types instead.

In both cases, the formula is a simple subtraction.

Related: How to make an Excel timesheet?

Types of Time Calculations in Excel: Add & Subtract

Although time difference is the most popular requirement for time management, it’s not the only one. You often must add or subtract a certain amount to a given date or time. For example, an employee works on a project at 8:00 AM and works 8 hours.

Format the cells that hold the added value as h for adding hours under 24 hours, [mm] for adding minutes under 60 minutes, [h] for adding more than 24 hours, and [mm] for adding more than 60 minutes. You can also simultaneously combine and add hours, minutes, and seconds (h:mm:ss or [h]:mm:ss).

 The formula for adding time is a simple add operation, while for subtracting time is a subtract operation. If everything happens within 24 hours, you can format the Result cells to show only the time. But if you pass the 24-hour limit, you must also format them to show the date.

Using the TIME Function

The TIME(hour, minute, second) function creates a time value formatted as h:mm AM/PM from three parameters representing hours, minutes, and seconds. It helps you calculate time differences without worrying about the date, just focusing on time. For example, you can use it to calculate employees working hours or overtime.

You can also create a time value from text using the function TIMEVALUE(“text”). Both TIME() and TIMEVALUE() help you avoid using Excel formatting to deal with time by allowing you to use only numeric values or text.

Using the TEXT Function

The TEXT(value, format) function formats a value using the format you give as a parameter. Instead of formatting the Result cells when calculating time differences, you can use the TEXT() function and do everything in a single step. For example, you can use the function to format the difference between two dates as years or days or between two times as hours and minutes.

NOW() and TODAY()

You don’t always have to check your watch or calendar when using Excel. Instead, use the functions NOW() and TODAY() to get the exact time and date. Keep in mind that the results vary over time. If you want to lock in a specific date or time, copy the result of the functions NOW() and TODAY() as values in separate cells.

Formulas for Calculating Time in Excel

All the formulas used for this article are available in the free downloadable Excel file. The file includes examples you can use in your personnel files, timesheets, and any other time management Excel document. Here is a summary of the formulas we’ve used:

Tip: Want to improve your Excel know-how? Discover the top 20 most popular excel formulas. For more complex formulas like how many days left we do share the Excel formula also.

Conclusion

Managing time efficiently is crucial for your business. There is no room for mistakes. Find and use the tools that work for you and automate the processes as much as possible. Using Excel formulas and functions is the best starting point. Develop a workflow that will soon become second nature to ensure you avoid errors and time-consuming tasks.