Date and Time as text in varied format

[benwork]

Hi,

Im looking to fix the date and time format in a current sheet. Its an export from software so the format comes as text.

I tried doing the date + time formulas and have been somewhat successful. I can get the formula to work no problems, the issue that I have is that the text date formats are somewhat inconstant.

I only have a very small data set at this stage (a few days) but what I have found is that the times between 00:00 and 10:00 are not being reported as HH:MM but as H:MM so theres a value sometimes missing which is hurting my left,mid,right formulas

The current format is reported as MM/DD/YYY HH:MM:SS , although as I mentioned, if the first H is a 0, it misses the value.

I do not have any examples of a MM or DD where it could be reported as a single digit M, D (1-9), so I'll have to park that for now and fix that later.

Anyone have any ideas?

Thanks in advanced

Ben

A2 - 11/22/2018 10:14:26
A3 - 11/22/2018 9:28:21

=DATE(MID(A2,7,4),LEFT(A2,2),MID(A2,4,2))+TIME(MID(A2,12,2),MID(A2,15,2),MID(A2,18,2))

 

[James006]

Hi,

You can test following:

=DATE(VALUE(RIGHT(LEFT(A2,10),4)),VALUE(LEFT(LEFT(A2,10),2)),VALUE(MID(LEFT(A2,10),4,2)))+VALUE(TRIM(MID(A2,11,LEN(A2)-10)))

and format your result : dd-mm-yy hh:mm:ss

Hope this will help

 

[sandy666]

maybe use PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]11/22/2018 10:14:26[/td][td][/td][td=bgcolor:#E2EFDA]

22/11/2018 10:14:26​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]11/22/2018 9:28:21[/td][td][/td][td]

22/11/2018 09:28:21​

[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Column1", type datetime}}, "en-US")
in
    #"Changed Type with Locale"[/SIZE]

(my locale is not in US style so that is why there is dd/mm/yyyy)

 

[benwork]

Thanks all for the quick reply. This fixes the problem.

Appreciate it

 

[James006]

Thanks all for the quick reply. This fixes the problem.

Appreciate it

You are welcome ...