ExcelInsider Team
Calculating the time difference is a common task, especially in professional fields. From managing workers’ timeshifts, the duration they worked, and for any tasks, you need to know how to calculate time difference in Excel. Instead of manually counting hours on your fingers, the interactive functions of Excel can help you schedule better through accurate calculations.
Steps to calculate time difference in Excel with basic subtraction formula:
➤ Write the start and end times in different cells.
➤ Select the end time cell and give a subtract minus sign, and select the start time (also, you can write the cell number like B2-A2).
➤ Finally, format the cell to h:mm:s for correct display of time.
In this article, we will explore the various methods to calculate the time difference in Excel, including basic subtraction, TEXT, and MOD functions with specific formulas. Apart from that, you will also get the pinpoint idea about dealing with negative values, converting time to decimals, and handling all AM/PM formatting errors.
Subtracting Time Directly Using Basic Subtraction Formula
In this method, we can subtract the time difference directly and easily just by selecting the cells containing the start times and the end times. The dataset below shows the clock-in and clock-out times of the ten employees.
Steps:
➤ Place the cursor on the E2 cell and select the clock-out time (D2).
➤ Then put a subtract sign and select the clock-in time (C2).
➤ Pressing Enter will generate the total time in AM/PM format.
➤ To change it to h:mm:ss format, you need to press Ctrl + 1 , which shows a window of the format cells.
➤ From the window, choose the category Custom and the type h:mm:ss.
➤ This will generate time in an accurate format of hours, minutes, and seconds.
➤ Now, drag down the cells of the E column to generate the same for the others.
Note:
➥ This method only works when the times are formatted in Time format.
➥ Do not select the starting time before the ending time. This will give a negative result, and ###### will be shown in the cell.
Using TEXT Function to Calculate Time Difference
Another commonly used formula for calculating the time difference in Excel is the text function. It is easy to use and can come in handy when you want to calculate time based on only one attribute. For instance, you can manipulate it to calculate the time difference in hours only or in both hours and minutes.
Steps:
➤ To calculate the hour difference between two times, use the formula in the required cell-
=TEXT(D2-C2, “h”)
Here, the D2 and C2 are the ending and starting times, respectively, which you need to select after the parentheses and the minus sign.
➤ The same goes for when we want to calculate the time difference in both hours and minutes. You just need to change the last parameter to h:mm instead of h.
➤ To find the time in hours, minutes, and seconds, use the same text method, changing the last attribute to h:mm:ss.
➤ Drag the column by column to generate the same formula for all the cells in the same column.
Note:
This method is mainly used to display the result to the user, not for calculating. Thus, it is stored as text, and you can apply other arithmetic functions like sum, average, etc., here.
Calculating Time Difference in Decimal
In order to calculate time difference in Excel, one of the prominent approaches is to display it in decimals. Converting time to decimal is also a convenient way, as it makes the calculation easier. Also, the viewer could easily understand and interpret the time.
Steps:
➤ Subtract the initial time from the final time (D2-C2) and multiply it by 24.
➤ Drag the cursor in the column to apply it to the rest of the cells.
➤ Select column E and press Ctrl + 1 to open the Format Cell window.
➤ Format the result column to Numbers and set values to 2 decimal places. Keep the rest the same as by default.
Note:
Remember to give parentheses around the subtraction in the formula before multiplying to avoid errors.
Calculate Time Difference in Minutes
Many times in the payroll functions, we need to calculate the time difference in minutes. It is helpful mainly when the difference is small and especially within the same hour. However, to do that, we need to remember that Excel considers 1440 minutes to be 1 day (24 hours x 60 minutes).
Steps:
➤ Use a similar formula of subtraction between the final and the initial time (D2-C2) and multiply the whole by 1440.
➤ Drag the column to generate the same formula for all the other cells.
➤ Select the entire column and enter Ctrl + 1 to format cells.
➤ Choose the same options as Number and 2 decimal places as mentioned in the previous method.
Note:
➥ Remember to use brackets around the subtraction formula to get the proper result.
➥ You can also show no decimal places, as it only contains minutes and no seconds, by replacing decimal places with 0.
Calculating Time Difference in Seconds
Though you might think calculating time difference in seconds is a useless task, you might actually be wrong. While finding out the time for the network processing times, logging, or transaction processing time, people often need to know the time in seconds for precision. This task is also simple and easy, just remember that Excel counts 86400 seconds in 1 day (24 hours *60 minutes *60 seconds).
Steps:
➤ First, like before, select final and initial times and separate them with a minus sign to find the time difference.
➤ Keep this in brackets and multiply this by 86400.
➤ Drag the cursor in the row to generate the same for all the cells
➤ Select the entire column and enter Ctrl + 1 to format the cells
➤ Click on Numbers and set the decimal value to 0, as we don’t have any smaller units than seconds. But, you can also keep it in 2 decimal places, it will also mean the same, as we don’t have any smaller units like milliseconds in Excel.
Note:
Remember to give brackets around the subtraction of the final and initial time (D2-C2).
Calculate Time Difference From the Current Time
Another important formula under time calculation is to find the difference in time up to the current time. For concurrent updates, we often need to find how much time has elapsed from a certain time to the present time.
Steps:
➤ Create a Current Time (NOW) column for clarity.
➤ In the cell, write NOW() and drag the column to generate the same for the rest
➤ Now, you can again follow the same subtraction method by subtracting the final time from the initial time (D2-C2).
➤ Then select the entire row and enter Ctrl + 1 to format the cells
➤ Select Custom and hh:mm:ss to get the correct time duration.
Note:
Without creating an entire column of present time, you can also directly subtract from the present time. In that case, modify the formula to be –
=NOW()-C2, where C2 is the initial time, and the NOW function generates the present time.
Calculate Time Difference for Overnight Shifts (Avoiding Negative Values)
As already mentioned, when we try to calculate the time difference from a smaller value to a larger value, it gives us an error. For instance, if we subtract 17:00 from 5:00, it gives errors and shows #### in the cell. However, if any employee works an overnight shift, like from 11:00 PM to 4:00 AM of the next day, the subtraction of the convention methods we discussed will give errors. To get rid of this error, we can use any of the methods below.
Using IF Function
The IF function generally sets some parameters to ensure the calculation is correct without showing negative hours. Firstly, it checks if the starting time is greater than the ending time. If it is true, then it adds 1 to the ending time as it belongs ultimately to the next day, and subtracts the end time. Finally, it performs the conventional subtraction of ending time and starting time again.
Steps:
➤ In the cell place the following formula –
Here C2 is the clock-in time and D2 is the clock-out time.
➤ The worked hours generated are not correct. To fix this, we need to format the cells
➤ Enter Ctrl + 1 to open the Format cells window.
➤ Select Custom and h:mm:ss.
Note:
Use the custom time formatting to h:mm AM/PM in the clock-in and clock-out columns to get the accurate time difference.
Using MOD Function
The exact meaning of MOD in mathematical terms refers to wrapping any value such that it always gives a positive value. In Excel, the MOD function does the same thing. This function just eliminates the negative value that it generates. Along with the general subtraction formula, it also contains an extra parameter 1 that refers to one complete day or another day. The result from the usual subtraction of the start and end time is again subtracted by this to get the exact time difference.
Steps:
➤ In the cell, use the MOD formula –
Here D2 is the end time and C2 is the start time.
➤ Drag down the column to generate the same formula for all the cells.
➤ To generate the value in time format, press Ctrl + 1 to format cells.
➤ In the menu, choose Custom and h:mm:ss.
Note:
Ensure start and end times are always formatted in h:mm AM/PM for this formula to work.
Frequently Asked Questions (FAQS)
How can I format cells in a time format?
To format cells in the time format, for example, h:mm:ss or h:mm AM/PM, you need to format the cells. To format the cell/column, you can either press Ctrl + 1 or right-click by select it. If you right-clicked, choose the format cells option. The shortcut key directly opens the box showing all the menus. Select Custom and your required time format from the menu and confirm it by pressing OK.
How can I calculate the time difference in Excel between multiple days?
The time difference between multiple days, especially those that exceed 24 hours, needs to be calculated with a specific formula. At first, use the generalized formula of end time subtracted by start time for each day. Then, use the formula of SUM to find the total duration. Format the cells [h]:mm to get the results for values that cross 24 hours.
How do I calculate the difference between two dates in Excel?
To calculate the date difference, use the formula of DATEIF(StartDate, EndDate, d). Here, the StartDate will be the cell number of the starting date, and the EndDate will be the cell number of the end date. The d simply refers to the format, which ensures the result comes in days. You can also change it to months or years by putting m or y, respectively.
How do I calculate the time difference between different time zones in Excel?
While calculating the time difference between different time zones in Excel, you need to convert the time values by subtracting. For instance, if you need to calculate the difference between EST and PST, you can create a column that converts EST to PST. As PST is three hours behind EST, you can set the formula of the cell – (A2-TIME(3,0,0)), where A2 is the time in PST. This formula ensures only 3 hours are subtracted from the time. After that, you can use any of the time difference calculation methods.
How do I calculate time when the end time is earlier than the start time?
Use the MOD function to avoid a negative value when the end time is earlier than the start time. The formula should be – MOD(B2-A2, 1), where B2 and A2 are the end time and start time, respectively. This comes in handy when calculating overnight shifts.
How to calculate the average time difference in Excel?
To calculate the average time difference, use the formula of the AVERAGE and select the entire column of the time difference. The formula will look like – AVERAGE(E1:E10), where column E represents the time difference between two times.
Wrapping Up
In this article, we explored how to calculate time differences in Excel. We looked into five different methods to find out the time difference between two different times. We also delve deeper by understanding how to calculate overnight shifts or negative time differences. Even after this, if you are in confusion, feel free to share your thoughts or download the practice worksheets.
