CODEWITHSUNDEEP
excelexcel-formula
B..
B..

Reputation: 13

Tidying messed up date-time cells from excel data dump

enter image description hereHi guys, is there an efficiency way to tidy up the messed up date format in excel as shown in the image? I want to standardise them into the UK date-time format.

I am UK based and the data is pulled from a US platform. As you can see cells that have left-alignment are in UK format but those with right-alignment are in the US format. When I open Format Cells in Excel, those with UK format are categorised as 'General' but those in US format are categorised as 'Custom: dd/mm/yyyy hh:mm'.

I tried date functions in excel but they don't work as expected.

Many thanks in advance.

Upvotes: 1

Views: 285

Answers (1)

vpprof
vpprof

Reputation: 127

In the spreadsheet you shared the dates are interpreted as month/day/year. Beware. In fact, the reason why some dates are not interpreted as dates is that they begin with a number > 12, so clearly Excel is expecting the month to come first.

Anyway, fill a second column with this formula:

=IF(ISNONTEXT(B3),B3,DATE(MID(B3,7,4),MID(B3,4,2),MID(B3,1,2))+TIME(MID(B3,12,2),MID(B3,15,2),0))

and that should give you all your dates represented as actual dates, not text strings. Then if you want to copy them, select the second column, copy and paste as values (there's a Paste Special option, or Ctrl+Alt+V).

Upvotes: 1

Related Questions