Date and Time conversion help

[U412178]

Is there a formula that will convert a cell from "1/4/11 730am" to 01/04/2011 07:30". So the cell needs to be formatted like mm/dd/yyyy hh:mm (in milatary time). I tried to do a custom format of mm/dd/yyyy h:mm but that format didn't work. Does anyone have any ideas on how to make it work? Thank you!

 

[jimrward]

ok, this is a monster but I built it up slowly in bits, vba custom function may have been better, the only problem is that it assumes a 24 clock as I have not added 12 hours for pm

with your current date time string in A1, paste the following in B1

=DATEVALUE(LEFT(A1,FIND(" ",A1)-1))+TIME(LEFT(LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),(LEN(A1)-FIND(" ",A1))-2),LEN(LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),(LEN(A1)-FIND(" ",A1))-2))-2),RIGHT(LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),(LEN(A1)-FIND(" ",A1))-2),2),0)

 

[barry houdini]

Your data is close to being a valid time/date format, all you need is a colon between hours and minutes and a space before am/pm. This formula should do that

=REPLACE(REPLACE(A1,LEN(A1)-1,0," "),LEN(A1)-3,0,":")+0

format result cell in required date/time format i.e. mm/dd/yyyy hh:mm

 

[Glory]

Jim, A-triple-plus for effort.

Barry, is there an equivalent VBA function to convert a date from one format to another without actually using extensive strings to check and replace all the parts? (eg, 4-3 to 04-03-2011)

This is what I'm using now, I'm wondering if I'm being unnecessarily convoluted about it.

Private Function DateAutoCorrection(Optional theDate)
 
'==================================================
'Private function used in multiple object procedures to check for job redundancy
'==================================================
 
On Error GoTo YearAdder
 
theDate = Replace(theDate, "-", "/")
fourtharray = Split(theDate, "/")
 
If fourtharray(0) < 10 And _
    Len(fourtharray(0)) < 2 Then: fourtharray(0) = "0" & fourtharray(0)
 
If fourtharray(1) < 10 And _
    Len(fourtharray(1)) < 2 Then: fourtharray(1) = "0" & fourtharray(1)
 
If Len(fourtharray(2)) = 2 Then: fourtharray(2) = "20" & fourtharray(2)
 
If IsEmpty(newdate) Then: newdate = Join(fourtharray, "/")
 
DateAutoCorrection = newdate
 
'==================================================
'Error handler for adding year back into short dates
'==================================================
 
YearAdder:
 
If Err.Number = 9 Then: newdate = Join(fourtharray, "/") & "/" & Year(Date)
 
Resume Next
 
End Function