In Excel, calculating the time difference from a few points to a few points can be achieved in a few different ways. Here are three simple and easy-to-use methods:
Method 1: Calculate directly by subtraction
Set the two cells to be calculated and the cells to display the results to the time format, and then use the end time-start time to get the result.
Method 2: Use the hour and minute functions
If you want to calculate the number of full hours and minutes between two points in time, you can use the "hour" and "minute" functions.
Enter the following formula into cell C1 to calculate the number of complete hours between two time points:
hour(c3-b3)
Enter the following formula into cell d1 to calculate the number of minutes between two time points:
minute(c3-b3)
With the above formula, the number of complete hours and minutes between two time points can be obtained.
Of course, it is also possible to combine the above two formulas into one formula:
hour(c3-b3)&"hours"&minute(c3-b3)&"points"
Method 3: Use a custom function
If you often need to do time calculations, then you can create a custom function to simplify the calculation process. Here's an example of creating a custom function using VBA (Visual Basic for Applications).
Step 1: Press "Alt+F11" to open the VBA editor.
Step 2: Select "Module" in the "Insert" menu, enter the following ** in the module window:
function timedifference(time1 as range, time2 as range) as variant
dim diff as variant
dim hours as integer, minutes as integer
hours = hour(time2.value - time1.value)
minutes = minute(time2.value - time1.value)
timedifference = hours & "hours" & format(minutes, "00") &"points"
end function
Step 3: Press "Ctrl+S" to save** to close the VBA editor.
Step 4: Go back to the excel sheet and enter the following formula in any cell to calculate the time difference between two time points:
timedifference (start time, end time).
With the above custom function, we can easily get the time difference between two time points, which is displayed in hours and minutes.
Of course, in practical application, the following factors also need to be considered, such as hourly pay only needs hours, then the above second formula can be modified as follows:
hour(c3-b3)+minute(c3-b3)/60
Then, the return result is 16。