help converting date/time to correct format

smp91986

New Member
Joined
Jul 14, 2021
Messages
2
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!!!



 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,434
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.

VBA Code:
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

Board Regular
Joined
Feb 10, 2010
Messages
158
Office Version
  1. 2019
Platform
  1. Windows
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
VWXY
303/31/2021 1:50PM44286.5763904/08/2021 5:34PM44294.73194
4
511,744
Sheet1 (2)
Cell Formulas
RangeFormula
W3,Y3W3=V3
V5V5=1440*(X3-V3)


1626301566536.png
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,499
Office Version
  1. 365
Platform
  1. Windows
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).
 

Forum statistics

Threads
1,144,337
Messages
5,723,797
Members
422,517
Latest member
VisioExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top