March 9, 2014

Calculate Difference in Hours between Two Date Time Values

How to calculate the difference in hours between two date-time values
One of our website visitors wanted to know how to calculate the time difference in hours between two date-time values in Microsoft Excel. He also wanted to know how to do this using a macro. The problem is quite interesting for scientific experiments as well as for calculating the number of hours worked on a project.
How to enter the date and time in a cell:

  1. Select the cell
  2. Right-click on it
  3. From the menu that pops up select Format cells…
  4. In the Format Cells window that opens click on under Category the item Date
  5. Under the item Type click on the option 3/14/12 1:30 PM
  6. Now you can enter the start date in the relevant worksheet cell using the above format
  7. Similarly enter the end date and time in another cell
  8. Now the end date minus the start date multiplied by 24 gives you the number of hours that have elapsed between the two values
  9. This calculation can help you to calculate, for example, the wages of an employee

The macro code assumes that the dates are in cells A2 and B2 and you want the result to be shown in cell C2:

Sub CalculateHoursFromTwoDateTimesValues()

Range(“C2”).Value = (Range(“B2”).Value – Range(“A2”).Value) * 24

End Sub

The training video below shows the details of performing the calculations without a macro:

3 thoughts on “Calculate Difference in Hours between Two Date Time Values

  1. I have used the VBA code you give above and it works perfectly. I would like to extend the calculation through as many cells as I need in other words for a five day period. I tried copying and pasting the same code and changing the cell numbers but it didn’t work. So I would like to have cells C3, C4, and so forth. Do I have to format each cell or will the VBA code once inserted format the cells?

Comments are closed.