How to Calculate the Time Difference in Excel

Written by kate bluest
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Calculate the Time Difference in Excel
Calculate differences between times using Microsoft Excel. (clock image by Jaroslav Machacek from Fotolia.com)

An employee comes to you to complain that her hours are incorrect. Upon review, you realise you calculated her hours incorrectly. You can enter the times in Microsoft Excel and calculate the difference between the times accurately. You can enter time using the time function or directly into the spreadsheet using proper syntax so Excel knows to interpret the numbers as time. You can enter dates using the date function or directly into the cells using proper syntax so Excel knows to interpret the numbers as dates.

Skill level:
Moderate

Other People Are Reading

Instructions

  1. 1

    Open a new Microsoft Excel workbook.

  2. 2

    Select the top cell in column "A" and type "Start Time," and press "Enter."

  3. 3

    Select the top cell in column "B" and type "End Time," and press "Enter."

  4. 4

    Select the top cell in column "C" and type "Time Difference," and press "Enter."

  5. 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. 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. 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. 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. 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.

  1. 1

    Open a new Microsoft Excel workbook.

  2. 2

    Select the top cell in column "A" and type "Start Date," and press "Enter."

  3. 3

    Select the top cell in column "B" and type "End Date," and press "Enter."

  4. 4

    Select the top cell in column "C" and type "Date Difference," and press "Enter."

  5. 5

    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.

  6. 6

    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.

  7. 7

    Click the "Number" tab and select the "Date" category. Select your preferred format in the "Type:" box and click "OK."

  8. 8

    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.

  9. 9

    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.

  10. 10

    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.

  11. 11

    Calculate the date difference by using the formula end date minus start date. Type "=B2-A2" and press "Enter."

  12. 12

    Calculate the date difference using workdays only by using the NETWORKDAYS function (=NETWORKDAYS(Start Date, End Date)). Type "=NETWORKDAYS(a2, b2)" and press "Enter."

  13. 13

    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.

Tips and warnings

  • To see all the possible ways dates and times can look in a spreadsheet, click on the "Home" tab and select the arrow in the "Number" section. Go to the "Date," "Time" and "Custom" category and review all the options available.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.