To subtract the times, we need to follow these steps: Similarly to the previous example, in the cell D4, we want to get the difference in hours and minutes, so the format will be “h:mm”, while in the cell D5, we want only hours, so the format will be “h”. Formatting the cells for times subtraction Formula: To do this, we need to press Ctrl+1, choose “Custom” category and enter the format of the time – h:mm:ss.įigure 4. Before doing the calculation, we must set the format of the resulting cell as a time. We will use the same data set like in the previous example. Subtract Times to Calculate a Time DifferenceĪnother way to calculate a time difference is to simply subtract two cells containing time values. In the cell D4 we get 3:04 (“h:mm”), while in the cell D5 we get the difference only in hours (3), which is “h” format. We can see in cell D3, we get the difference 3:04:30, which is “h:mm:ss” format. Using the TEXT function to calculate the time difference In cell D5, insert the formula: =TEXT(B5-C5,"h")įigure 3.In cell D4, insert the formula: =TEXT(B4-C4,"h:mm"). ![]() In cell D3, insert the formula: =TEXT(B3-C3,"h:mm:ss").To apply the TEXT function, we need to follow these steps: Finally, in the cell D5, we want only hours, so the format_text will be “h”. Similarly, in the cell D4, we want to get the difference in hours and minutes, so format_text parameter will be “h:mm”. Setting the time like this, we will get the time difference in hours, minutes and seconds. This will be our format_text parameter in the function. In order to present it as a time, we need to format the value as “h:mm:ss”. The value is B3-C3, which Excel calculates as a decimal number. ![]() In our example, we want to calculate the time difference between 6:19:00 PM and 3:14:30 PM and get the value in the cell D3. Data that we will use in the time difference calculation Time Difference Calculation Using the TEXT Function In column D, we want to get a time difference between a start and end time.įigure 2. Our table consists of 3 columns: “Start Time” (column B), “End Time” (column C) and “Time Difference” (column D). Setting up Our Data for the Time Difference Calculation
0 Comments
Leave a Reply. |