help converting date/time to correct format

[smp91986]

Hello! I've gone through other similar threads and while I find related guidance can't find anything that helps with my exact question. I am trying to calculate the difference in minutes between two date/times (formula I have for this is below). However, the issue is that when I export the data it is not in the correct date format and therefore the formula to calculate the difference will not work. I have tried using the Text to Columns tool to reformat the data and I can get it to work with just the date part but not with the date and time together.

So for example in cell X3 I have this: 04/08/2021 05:34PM

In cell V3 I have this: 03/31/2021 01:50PM

I want to find how many minutes elapsed between those two dates/times with the following (or similar) formula: =TEXT(X3-V3,"[mm]"). However, I believe it won't work because the fields are not correctly formatted.

Any hints would be appreciated. Thank you!!!

 

[AlphaFrog]

You could use the Replace feature (Ctrl+H) to add a space before each AM and PM. Then Excel will coerce the text into a Date-Time.

Sub Coerce_DateTime()
    With Range("V:V,X:X")
        .Replace What:="AM", Replacement:=" AM", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False
       
        .Replace What:="PM", Replacement:=" PM", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False
           
    End With
End Sub

 

[Ezguy4u]

smp, date and time formatting is tricky and difficult. As Alpha pointed out make sure there is a space for the time between the numbers and AM or PM. Even as I look at my example I noticed that there isn't a space in the cell but there is a space in the formula bar. So go to the formula bar and put the space there. Now I used the custom format just to get it to look like your example. Best I can say is if things aren't working the way you want them to, just keep fooling around with formatting the cell. Is 11,744 minutes the correct answer?

Book1
	V	W	X	Y
3	03/31/2021 1:50PM	44286.57639	04/08/2021 5:34PM	44294.73194
4
5	11,744
Sheet1 (2)

Cell Formulas
Range	Formula
W3,Y3	W3	=V3
V5	V5	=1440*(X3-V3)

 

[smp91986]

thank you everyone!!! the VBA code worked!

 

[Joe4]

thank you everyone!!! the VBA code worked!

Note that you don't need VBA to fix it. You can just use Excel's built-in Find/Replace functionality.
The VBA code is just what that would look like using VBA code (but it does not need to be done that way).