Home » Excel-Formulas » Time-Difference-In-Excel

Calculate Time Difference in Excel

How to Calculate a Time Difference in Excel

The easiest manner to calculate a time difference in Excel is to plainly subtract one time from the early. This works because Excel stores dates as integers and times as decimal values ( see the page on Excel dates for more details ). It is only the format of an Excel cell that causes the cell ‘s contents to be displayed as a date or fourth dimension, preferably than as a simpleton number.

consequently, when you want to calculate the remainder between two times in Excel, you can merely subtract the time values in the lapp way as you would subtract any other numeric values .

Date and Time Difference Examples

Example 1

Cell B3 of the follow spreadsheet shows a childlike example of how to calculate a time deviation by subtracting the time in cell B1 from the time in cell B2. Formulas : AB1Start Time:03:45:302End Time:10:17:453Time Difference:=B2-B1 Results : AB1Start Time:03:45:302End Time:10:17:453Time Difference:06:32:15 In the exemplar above, the time in cell B1 is internally represented by the number, 0.156597222 and the prison term in cell B2 is internally represented by the numeral 0.428993056. Subtracting these two numbers gives the solution 0.272395833, which, when formatted as a time, is 06:32:15 ( i.e. 6 hours 32 minutes and 15 seconds ) .

Formatting Cells as Times

When you calculate a time deviation in Excel, you may want the solution to be displayed as a time. If it is not already displayed as a time, you can format the cell to have a ‘Time ‘ format, using any of the follow methods :

Method 1 – Format Cells Using the Excel Ribbon Options

The easiest way to apply a bare ‘Time ‘ format is to select the cell ( s ) to be formatted and then select the Time option from the drop-down menu in the ribbon. This is found in the ‘Number ‘ group on the Home pill of the decoration ( see below ) :Excel Format Cells Drop Down Menu on Ribbon With Time Option Selected

Method 2 – Format Cells Using the ‘Format Cells’ Dialog Box

If you require a greater choice of time formats, you may prefer to apply cell formatting using the ‘Format Cells ‘ dialogue box as follows :

  • Select the cell(s) to be formatted.
  • Open up the ‘Format Cells ‘ dialogue box by either :Dialog Box Launcher in the Number Group of the Excel Ribbon
    • Clicking on the dialog box launcher in the ‘Number’ grouping, on the Home tab of the Excel ribbon (see right above).

    or

  • Ensure the Number tab of the ‘Format Cells’ dialog box is selected.
  • From the Category: list, select the option Time .(If you want a time other than the default, you can then select one of the options from the list on the right hand side of the ‘Format Cells’ dialog box.)
  • Click OK to apply the selected format and close the ‘Format Cells’ dialog box.

Example 2

The follow spreadsheet shows a childlike subtraction of two dates and times. Formulas : AB1Start Date & Time:19-Jan-2011   21:002End Date & Time:21-Jan-2011   15:003Time Difference:=B2-B1 Results ( with formatting ) : AB1Start Date & Time:19-Jan-2011   21:002End Date & Time:21-Jan-2011   15:003Time Difference:42:00 In this exercise, the date and time in cell B1 is internally represented by the number, 40562.875 and the date and time in cell B2 is internally represented by the numeral 40564.625. Subtracting these two numbers gives the resultant role 1.75 which is Excel ‘s internal measure for the time 42 hours 0 minutes .

Formatting Cells to Display Times With More Than 24 Hours:

It is probably that the standard time format ( applied when you use the decoration choice to format a time ) will only display times up to 24 hours. however, promote time format options are available in the ‘Format Cells ‘ dialogue box. To apply a time format that displays more than 24 hours :Format Cells Dialog Box With 24 Hour Time Option Selected

  • Open up the ‘Format Cells’ dialog box (as described above).
  • Ensure the Number tab of the dialog box is selected.
  • From the Category: list, select the option Time.
  • From the ‘Type’ list that appears on the right hand side of the ‘Format Cells’ dialog box, select a time format that displays an hour value that is greater than 24.
  • Click OK to apply the selected format and close the dialog box.


Common Time Difference Errors

If Excel has a trouble displaying the contents of a cell in a prison term or a date format, it will, alternatively display a row of hashes ( see below ) .Excel Cell Displaying #####
cell that is unable to display values in date or time format cell that is ineffective to display values in date or time format possible reasons for this are :

Possible Reason 1

The cell may not be wide enough to display the clock time or date in the specified format .

Solution

Excel Alter Column Width
increase column width by dragging the separator bar right increase column width by dragging the centrifuge barricade right You can alter the width of an Excel column by dragging the banish ( shown in the above picture on the right ) until the cell is wide adequate for the contents. alternatively, if you double-click on this measure, the cell should automatically re-size to fit the contents.


Possible Reason 2

Excel can not display negative times. therefore if the resultant role of your subtraction formula is a negative value, and the cell containing this convention has date or time format, the result will be displayed as a row of hashes .

Solution

You can display the underlying numeric value by formatting the cell with the ‘General ‘ format type .
See besides the page on Calculating Date Difference in Excel > >

reservoir : https://hashblink.net
Category : Excel

Leave a Reply

Your email address will not be published. Required fields are marked *