#  Other Applications & Softwares  > Outlook Formatting & Functions >  >  Mixed format when downloading timestamps

## KevyWevz

Hello Excel master community,

I am seeking your assistance for an issue being encountered when downloading large data ets (typically 200,000+ rows) with an unusual format in one of the columns.

This column contains time-stamp data for a particular event and the data provided is usually mm/dd/yyyy hh/hh/hh (AM/PM)

I have put AM/PM in brackets above as sometimes is is there in the data, and sometime it is not. It does not seem to matter if what time of the day the event being reported occurred, sometime's it is there and other times it is not. I have not been able to identify any pattern on when it is there.

The problem is I usually need to run this data through text to columns and map the date and hour these activities occurred but the mix of data is making it difficult to get a 'clean' view of the times of these activities.

I have attached an example of some random time-stamps to illustrate what I am referring to.

I suspect it is a time/date setting somewhere in my Excel/Windows preferences but I could be wrong, and even if I am right I am unable to locate it.

Is anyone able to help my convert this row into the below format?

mm/dd/yyyy hh/mm/ss (24hr e.g. 13:02:55)

Any help at all much appreciated.

Kindest regards,
KW

----------


## alansidman

I found the easiest way to do this is to bring the file into Power Query and do a Change Type to DateTime and then bring back to Excel. Tried to do conversions in Excel, but to complicated as to the mixed format of your data. PQ recognizes the mixed data immediately and repairs.




```
Please Login or Register  to view this content.
```

----------

