Getting #VALUE! error while calculating time difference

[Gautham A]

Hi Team,

In cell B3, the time is 06:32:09 and in A3 I have 03:34:46. In cell C3,I apply the formula =B3-A3. I should get 02:57:23. But I'm getting #VALUE! error.

 

[Leandroarb]

See how the cell is doing, see if it is as text. I did the same calculation and the result was correct.

 

[Gautham A]

It is in text format only. Still it is the same.

 

[Leandroarb]

It is in text format only. Still it is the same.

Change the format to General.

 

[Gautham A]

Tried all formats. Still it is showing #VALUE error

 

[Gautham A]

I found some thing. Actually for each cell there is a empty space at the end. I removed that manually.Now it is working. Is there any VBA code to remove empty space in the end? Because I have to do the same task for about 200 cells. Thanks in advance

 

[Joe4]

You can actually fix them all at the same time without using VBA.
Just select all the cells, and do a Find/Replace, replacing a single space with nothing.
If you choose the "Replace All" option, it will do all selected cells at once.

 

[Leandroarb]

I found some thing. Actually for each cell there is a empty space at the end. I removed that manually.Now it is working. Is there any VBA code to remove empty space in the end? Because I have to do the same task for about 200 cells. Thanks in advance

Good morning!

I use the Trim() function to this.

Example: =Trim(B3)-Trim(A3)

But pay attention for the orientations from @Joe4 to you.

 

[Gautham A]

Hi Joe and leandroarb
I tried both of your methods but still I'm getting the error

 

[Fluff]

What happens if you run this

Sub GauthamA()
   ActiveSheet.UsedRange.Replace Chr(160), "", xlPart, , , , False, False
End Sub