Getting a type mismatch error while calculating time difference

[Gautham A]

Hello
I'm getting a type mismatch error while calculating the time difference.
In cell A1, I have the value 19/06/2020 05:40:12
In cell B1, I have the value 19/06/2020 05:45:15,
While calculating the difference between them in VBA, I'm getting type mismatch error
I used the below code

For i=1 to 10
Cells(i,3)=Cells(i,2)- Cells(i,1)
Next i

I need the output in HH:MM:SS format.

 

[DanteAmor]

If the data in A1 and B1 really are dates, you should have no problems, but if they are texts, then you must convert them to date.

 

[Gautham A]

It is in custom format dd/mm/yyyy hh:mm:ss. So does this mean they are in date format?

 

[DanteAmor]

It does not matter if the cell has that format, if it is text, it will remain text regardless of the format.
Test on a new sheet, manually capture the values in A1 and B1, and test the macro again.

 

[Gautham A]

I manually copied this in another sheet and i used Excel formula. I typed in cell C1=B1-A1. I'm getting the correct output. But in VBA I'm still getting type mismatch.

 

[DanteAmor]

I manually copied

Do not copy the data, maybe it has some details, capture the entire date and time manually.


Try only row1, maybe the problem is in another row, but for now just try the following:

Sub test2()
  Dim i As Long
  For i = 1 To 1
    Cells(i, 3) = Cells(i, 2) - Cells(i, 1)
  Next i
End Sub

varios 11jul2020.xlsm
	A	B	C
1	19/06/2020 05:40:12 a.m.	19/06/2020 05:45:15 a.m.	0:05:03
Data

 

[Gautham A]

I tried doing manually and this code works.

 

[DanteAmor]

This means that you have a character in the original data that is converting that date into text.
At the cell level formulas work in a way different from VBA code, excel automatically resolves some issues that VBA does not.

 

[Gautham A]

I can see blank spaces in the front of some cells whereas other cells looks okay. Maybe that is the issue