Converting an ugly Date Time cell to an Excel readable one

**nicoan** · 04-19-2021, 07:41 AM

Hello all

We got a column like this:
(excel cannot recognize this as date/time)

18 Apr 2021 at 17:33
22 Apr 2021 at 12:00
18 Apr 2021 at 17:55
18 Apr 2021 at 17:47
21 Apr 2021 at 00:00
19 Apr 2021 at 11:00
21 Apr 2021 at 05:00

and we want to turn it readable.

How do we do it?

**AliGW** · 04-19-2021, 07:52 AM

Data | Text to Columns | Fixed width will get it separated out for you.

**KOKOSEK** · 04-19-2021, 07:54 AM

	A	B
1	18 Apr 2021 at 17:33	18/04/2021 17:33
2	22 Apr 2021 at 12:00	22/04/2021 12:00

	A	B
1	18 Apr 2021 at 17:33	=DATEVALUE(TEXT(SUBSTITUTE(A1,"at",""),"dd mmm yyyy"))+TIMEVALUE(RIGHT(A1,5))
2	22 Apr 2021 at 12:00	=DATEVALUE(TEXT(SUBSTITUTE(A2,"at",""),"dd mmm yyyy"))+TIMEVALUE(RIGHT(A2,5))

**torachan** · 04-19-2021, 08:18 AM

=SUBSTITUTE(A1," at "," ")

in B1 - copy down - then format dd mm yyyy hh:mm
seems to work - tested taking two dates away from each other with format [h];mm
torachan.

**KOKOSEK** · 04-19-2021, 08:37 AM

With this:

Formula:

Please Login or Register  to view this content.

even if it looks like date/time there is still text.

	A	B	C
1	18 Apr 2021 at 17:33	18 Apr 2021 17:33	FALSE

	A	B	C
1	18 Apr 2021 at 17:33	=SUBSTITUTE(A1," at "," ")	=ISNUMBER(B1)

**nicoan** · 04-19-2021, 08:54 AM

Awesome KOKOSEK..
Thank you!

**AliGW** · 04-19-2021, 09:00 AM

Administrative Note

... please don't ignore contributors to your thread - acknowledge all solutions offered, even if they don't meet your requirements. Thanks.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**torachan** · 04-19-2021, 09:15 AM

@KOKOSEK, although it does not checkout as a number it still works - see attached sheet
Also if you take two date/times away from each other a number is produced and when formatted [h]:mm produces the correct difference.
I guess when is a number not a number ?????
torachan.