Management tasks would be impossible without considering time. We need time to calculate an employee’s age, how many years they’ve been working for the company, how many hours an employee has worked in a day, time off and leave balances, workforce required for a project, and so on.
Luckily, Excel provides plenty of functions that work with time, whether you need to count years, days, or hours. You only need to get your head around it once, and it will serve you well for the rest of your management career. Here is everything you need to know about calculating time in Excel.
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:
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).
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 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.
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?
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.
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.
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.
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.
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:
Formula | Purpose | Description |
=B2-A2 | Time difference between dates | Computes the difference between two dates in cells B2 and A2 and provides a result in days |
=(B3-A3)/365 | Time difference between dates | Computes the difference between two dates in cells B3 and A3 and provides a result in years |
=(B4-A4)/12 | Time difference between dates | Computes the difference between two dates in cells B4 and A4 and provides a result in months |
=DATEDIF(TODAY(), A2, “y”) | Time difference between today’s date and a given date | Computes the difference between today’s date and the date in cell A2 and provides a result in years |
=A2+B2 | Add time | Computes the sum of two time values |
=TIME(HOUR(B5),MINUTE(B5),0)-TIME(HOUR(A5),MINUTE(A5),0) | Time difference between two times | Computes the time difference between two times |
=TEXT(B2-A2, "h:mm") | Time difference between two times | Computes the time difference between two times and provides a result in hours and minutes |
=TEXT(B2-A2, "y") | Time difference between two dates | Computes the time difference between two dates and provides a result in years |
=INT((TODAY()-A6)/365) | Time difference between today’s date and a given date | Computes the difference between today’s date and the date in cell A2 and provides a result in years by rounding to the highest integer lower than the result value |
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.