SOLVED

# converting text string to date time format

Copper Contributor

# converting text string to date time format

hi

I would like to be able to sort some data I get by date and time, however the date field is expressed like so:

May 15, 2023, 6:34:54 PM

how can i convert this to a date time that excel recognises?  I've initially done the following where the above date value is in column F, however if I try and add in the time I just get a 00:00:00

=DATEVALUE(CONCATENATE(MID(F2,5,2),"/",MID(F2,1,3),"/",MID(F2,9,4)))

Is there a way I can get excel to recognise this as a date field without doing the above, btw I have already tried a custom format and that does not work either.

to address the obvious problem I already run a replace on the data so dates like May 1 become May 01.

best response confirmed by ainganni (Copper Contributor)
Solution

# Re: converting text string to date time format

Assuming that all values use abbreviated month names Jan, Feb, etc. (May in your example is ambiguous!), try

=CONCATENATE(MID(F2,5,2),"",MID(F2,1,3),"-",MID(F2,9,4))+TRIM(RIGHT(F2,11))

Format the cell(s) with the formula as date and time.

You can also use Power Query (Data > From Table/Range). It will automatically convert the values to real date/time values.

1 best response

Accepted Solutions
best response confirmed by ainganni (Copper Contributor)
Solution

# Re: converting text string to date time format

Assuming that all values use abbreviated month names Jan, Feb, etc. (May in your example is ambiguous!), try

=CONCATENATE(MID(F2,5,2),"",MID(F2,1,3),"-",MID(F2,9,4))+TRIM(RIGHT(F2,11))

Format the cell(s) with the formula as date and time.

You can also use Power Query (Data > From Table/Range). It will automatically convert the values to real date/time values.