Browse » Home
Monday, May 16, 2011
How to Calculate the Time Difference in Excel
Calculate Differences Between Hours
1. Open a new Microsoft Excel workbook.
2. Select the top cell in column 'A' and type 'Start Time,' and press 'Enter.'
3. Select the top cell in column 'B' and type 'End Time,' and press 'Enter.'
4. Select the top cell in column 'C' and type 'Time Difference,' and press 'Enter.'
5. Enter the start and end times in the appropriate columns by selecting the cell under the appropriate column and typing the time function, '=TIME(hour, minute, second)' and pressing 'Enter.' To enter times without using the TIME function, skip this step.
6. Format the cells for time by selecting the 'Home' tab on the top menu and clicking the arrow in the 'Font' section to open the 'Format Cells' window.
7. Click the 'Number' tab and select the 'Time' category. Select your preferred format in the 'Type:' box and click 'OK.' Enter times using proper syntax. Examples are '5 p' for 5:00 p.m., '5:42 p' for 5:42 p.m., or if you want to include seconds, '5:42:15 p' for 5:42:15 p.m. To display seconds, be sure to select a format that shows seconds.
8. Calculate the time difference by using the formula end time minus start time, using the 'TEXT' function for proper formatting (h=hour, m=minute and s=second). Select the cell under the appropriate column and type the combined text function and time formula, '=TEXT(B2-A2, 'h:mm:ss')' and press 'Enter.'
9. Select the cell containing the formula and hold down 'Ctrl' and 'C' to copy it. Paste the formula in column C next to all rows that contain start and end times to be calculated.
Calculate Differences Between Dates
10. Open a new Microsoft Excel workbook.
11. Select the top cell in column 'A' and type 'Start Date,' and press 'Enter.'
12. Select the top cell in column 'B' and type 'End Date,' and press 'Enter.'
13. Select the top cell in column 'C' and type 'Date Difference,' and press 'Enter.'
14. Enter the start and end dates in the appropriate columns by selecting the cell under the appropriate column and typing the date function, '=DATE(year, month, day)' and pressing 'Enter.' To enter times without using the DATE function, skip this step.
15. Format the cells for dates by selecting the 'Home' tab on the top menu and clicking the arrow in the 'Font' section to open the 'Format Cells' window.
16. Click the 'Number' tab and select the 'Date' category. Select your preferred format in the 'Type:' box and click 'OK.'
17. Enter dates using proper syntax. Click the cell and type '5-8' and press 'Enter' for dates in the current year. In the year 2010, Excel would read this date as May 8, 2010.
18. Include the year for dates not in the current year. Click the cell and type '5-8-9' or '5-8-09' and press 'Enter.' Excel would interpret this date as May 8, 2009.
19. Enter the full year for dates in other centuries. Type '5-8-1942' and press 'Enter.' Excel would interpret this date as May 8, 1942.
20. Calculate the date difference by using the formula end date minus start date. Type '=B2-A2' and press 'Enter.'
21. Calculate the date difference using workdays only by using the NETWORKDAYS function (=NETWORKDAYS(Start Date, End Date)). Type '=NETWORKDAYS(a2, b2)' and press 'Enter.'
22. Select the cell containing the formula and hold down 'Ctrl' and 'C' to copy it. Paste the formula in column C next to all rows that contain start and end dates to be calculated.