Find the difference in hours and minutes of two dates with format dd/mm/yyyy hh:mm

2
votes

I have been trying to find the difference in hours and minutes between 2 dates with format dd/mm/yyyy hh:mm. The code I am using works (kind of) fine but it does not take into consideration the difference in days. So, if the first date 28/09/2017 11:35 and the second one is 30/09/2017 13:45 then, it will say that the difference is 02:10 . But, the correct answer is 50:10 (50 hours and 10 minutes). I am providing the code below.

With ws1.Range("N2:N" & lastrow1)
  .Formula = "=NOW()-E2"
  .NumberFormat = "h:mm:ss"
End With
1
vbaexceldatedatetime

1 Answers

2
votes

You can use the square brackets to force the overflowed values back to it.

.NumberFormat = "[h]:mm:ss"

EDIT (for the question in comment):
Excel is smart enough that when a formula is using cell references, it will attempt to convert accordingly.

Alternatively, you can try:

With ws1.Range("N2:N" & lastrow1)
    .Formula = "=NOW()-(DateValue(E2)+TimeValue(E2))"
    .NumberFormat = "[h]:mm:ss"
End With