Excel read dates and times as numbers so that you can effortlessly apply simple to advanced arithmetic operations and formulas for different values. Are you wondering how to calculate two different time values? Or how would you manage time calculation in Excel sheet? You can even calculate date values or the difference between two date or time values with Excel.
Though manually calculating the time difference between two times could be complicated. In Excel, values for time are coded between 0 and 1.
Basic Formula
Using the basic formula for time calculation in an Excel sheet, let’s begin with a simple calculation for the difference between two-time values given on the same day.
- Firstly, you will have to subtract the first time value from the ending time value.
Also, note that AM is for the night and morning time whereas PM is for the afternoon and evening times. You can even make changes in the Time format according to your preferences.
- Next, you will have to edit the number format of the values given in the cells A2, B2, and C2 to General. As already mentioned, time values are coded as numbers between 0 and 1, that’s why the 6:00 AM value would be as 0.25 quarter through the day.
Time Difference Formula
The above-mentioned formula is not useful if the first time value is before midnight and the ending time value is after midnight.
Remember that negative time values are displayed as #######.
To sort out this issue, you have to make changes in the number format of the values given in the cells A2, B2, and C2 to General.
The time difference formula given below is effectively useful.
To explain it in detail, you will see that if the later time value is greater than or equal to the first value, you ought to subtract the start time from the end time. In contrast, you ought to add 1 full day to the later time to display a time on the next day and subtract the first time value.
How You Would Manage Time Calculation in Excel Sheet (Elapsed Time)
Suppose you need to find the difference between starting time and the finish time value. Let’s figure out how quickly you can calculate elapsed time in Excel. You can perform this operation using multiple formulas and it entirely depends on your dataset and the purpose of this operation that which formula you have to opt for. So, let’s dive in:
Subtract One Time from the Other
Usually, time values are decimal numbers that appear as times. In addition, you can add, subtract, divide or multiply numbers no matter in which format those numerical values are presented.
The most common Excel formula used for time calculation in Excel sheets is:
=End time – Start time
Your data structure determines what formula for time difference calculation you would choose, for instance:
| Formula | Explanation |
| =A2-B2 | It helps in calculating the time difference between the cell A2 and B2. |
| =TIMEVALUE(“8:30 PM”) – TIMEVALUE(“6:40 AM”) | It helps in calculating the difference between the particular times. |
| =TIME(HOUR(A2), MINUTE(A2), SECOND(A2)) – TIME(HOUR(B2), MINUTE(B2), SECOND(B2)) | In cell A2 and B2, it helps in calculating time difference between two values while overlooking the date difference as the cells carry both values of date and time. |
Bear in mind that time values are displayed in the back end of Excel by fractional parts of decimal numbers. You will get results as:
In column D, you can see decimals that are needed to be more meaningful. For this, you will have to choose custom time formatting using one of the following codes:
| Time code | Explanation |
| h | Elapsed hours, display as 4. |
| h:mm | Elapsed hours and minutes, display as 4:10. |
| h:mm:ss | Elapsed hours, minutes and seconds, display as 4:10:20. |
Using your keyboard, press CTRL + 1 in case of applying a custom time format. Doing this will open up the Format Cells dialog box, from which you have to choose the “Custom” option right from the Category list. Now, you need to type in the time codes in the Type field box.
In real-time situations, you will see the clear-cut results of the formula used for time difference calculation in the Excel sheet. In column A, you will have to enter the Start time values and in column B, enter the End time values. In columns C through E, copy the formula given below:
=$B2-$A2
The time format you chose will determine the display pattern of elapsed time.
If you see hash marks ##### in place of elapsed time values, it signifies that a cell with the formula does not have enough space to accommodate the data. Or else the outcome is a negative value that’s why you are seeing these hash marks.
Calculating Time Difference with the Text Function
To find out the time difference using the TEXT function is somehow another convenient way you can use.
Using the TEXT function, you can identify the format of the formula.
=TEXT(End Date – Start Date, Format)
In the first argument, you can see the required calculation, whereas the second argument appears with the format that shows the result of the calculation.
Using the dataset given below, when you need to calculate the time difference between the two times, here are the steps:
Using different formulas lets you get results in different formats.
Show Only the Number of Hours:
=TEXT(B2-A2,“hh”)
The above formula is helpful only when you need the number of hours elapsed between the two-time values. When your outcome is 4 hours and 30 minutes, you will only get 4.
Show the Number of Total Minutes
=TEXT(B2-A2,“[mm]”)
Show the Number of Total Seconds
=TEXT(B2-A2,“[ss]”)
Show Hours and Minutes
=TEXT(B2-A2,“[hh]:mm”)
Show Hours, Minutes, and Seconds
=TEXT(B2-A2,“hh:mm:ss”)
Calculating Hours, Minutes, or Seconds between Two Times
Apart from the elapsed time, when you need to figure out the time difference in a single time unit such as hours, minutes, and seconds, you may execute the following calculations.
Calculate Hours Between Two Times:
While showing the difference between two-time values as a decimal number, below is the useful formula:
=(End time – Start time) * 24
Assume that the start time is placed in A2 and the end time is in B2. To find the difference between two-time values, a simple equation B2-A2 is used. Later on, you have to multiply this value by 24 and the result is known for the number of hours in one day:
=(B2-A2) * 24
The INT function is another great feature used to get the number of complete hours. With this, your result will get down to the nearest integer:
=INT(B2-A2) *24
Sum of Minutes between Two Times
Calculating the minutes between two-time values need you to multiply the time difference by 1440. It is known as the number of minutes in one day (24 hours * 60 minutes=1440).
=(End time – Start time) * 1440
Below you can see the formula helps in returning both positive as well as negative values. In the later part, the end time is less than the start time that you can see in row 5:
=(B2-A2) * 1440
Sum of Seconds between Times:
Finally, when you need to find out the total seconds between two-time values, 86400 is the value used for multiplying the time difference value. The outcome would be the total of seconds in one day (24 hours * 60 minutes * 60 seconds = 86400).
=(End time – Start time) * 86400
Using the same example, the formula will be as:
=(B2-A2) * 86400
Also, note that the General format is mandatory when needed for the right display of results. Apply the format rightfully to the cells while using the time difference formula.
