Converting a text string into date+time value

[Mauro1234]

Hello, I'm looking for a way to convert a text string into a format that Excel recognizes as date+time. Excel version used is 14.0.7177.5000 (32 bit) Italian localization, part of Office 2010

My source text format is like this inside a single cell

Feb 15, 2017 3:01:01 PM

I need to convert into anything that is recognized as date + time in a single cell (should be in a single cell because I'll use these dates to plot data into a chart)

I have tried a number of options

• Changing cell format from text to date (or to time), but the source text string is not interpreted and the output is not treated as date+hour
• Sorting day-month-year-hour with a series of MID() and CONCATENATE() functions, but the output is still not recognized as date-hour. Used formula was =MID(A1,5,2)&CONCATENATE("/")&MID(A1,1,3)&CONCATENATE("/")&MID(A1,9,4)&CONCATENATE(" ")&MID(A1,14,15) where A1 is a cell containing the source text
• DATEVALUE() formula: only manages dates without time
• TIMEVALUE() formula: only manages hours, even if the final formatting includes dates, the formula ignores anything in the input that is not an hour (as stated in the Excel documentation)

Thank you in advance for any idea and help you may provide

 

[etaf]

try

=DATEVALUE(MID(A1,5,2)&CONCATENATE("/")&MID(A1,1,3)&CONCATENATE("/")&MID(A1,9,4))+TIMEVALUE(MID(A1,14,15) )

But what does 1-9 of the month look like ?

01 or just
1

does the day always have a comma after it ?
you could use a search for , and then you can pick up single digit or double digit days
=DATEVALUE(MID(A1,(SEARCH(",",A1,1)-2),2)&CONCATENATE("/")&MID(A1,1,3)&CONCATENATE("/")&MID(A1,SEARCH(",",A1,1)+1,5))

=DATEVALUE(MID(A1,(SEARCH(",",A1,1)-2),2)&CONCATENATE("/")&MID(A1,1,3)&CONCATENATE("/")&MID(A1,SEARCH(",",A1,1)+1,5))+TIMEVALUE(MID(A1,13,17) )

 

[Mauro1234]

Just solved with
DATEVALUE() formula + TIMEVALUE() formula

 

[Ingolf]

It's nice if you tell us that you posted here:

Converting a text string date+time [SOLVED]

 

[etaf]

your formula will not work if days 1-9 are not displayed as 01 - 09

 

[MrJoosten]

Something like this?

=DATEVALUE(TEXT(CONCATENATE(MID(D1;5;2);" ";LEFT(D1;3);" ";MID(D1;9;4);" ";RIGHT(D1;10));"dd-mm-jjjj"))+TIMEVALUE(RIGHT(D1;10))

 

[LeesaJohnson]

If the string is in cell A1
=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,10,2),RIGHT(A1,2),0)

 

[Mauro1234]

Something like this?

=DATEVALUE(TEXT(CONCATENATE(MID(D1;5;2);" ";LEFT(D1;3);" ";MID(D1;9;4);" ";RIGHT(D1;10));"dd-mm-jjjj"))+TIMEVALUE(RIGHT(D1;10))

Mine looks like this but is't basically the same as yours:

=DATEVALUE(MID(A1,5,2)&CONCATENATE("/")&MID(A1,1,3)&CONCATENATE("/")&MID(A1,9,4)&CONCATENATE(" ")) + TIMEVALUE(MID(A1,14,15))

 

[FormR]

Hi, here's a couple of other options:

Excel 2013
	A	B
1		Days are always 2 digit
2	Feb 15, 2017 3:01:01 PM	15/02/2017 15:01:01
3	Feb 01, 2017 3:01:01 PM	01/02/2017 15:01:01
4
5		Days can be 1 digit
6	Feb 15, 2017 3:01:01 PM	15/02/2017 15:01:01
7	Feb 1, 2017 3:01:01 PM	01/02/2017 15:01:01
Sheet1

Cell Formulas
Range	Formula
B2	=0+(MID(A2,5,2)&REPLACE(A2,4,4,""))
B6	=0+(MID(A6,5,FIND(",",A6)-5)&REPLACE(A6,4,4,""))