text to date formatting issue

%3CLINGO-SUB%20id%3D%22lingo-sub-2317286%22%20slang%3D%22en-US%22%3Etext%20to%20date%20formatting%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2317286%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20-%20I%20have%20a%20field%20in%20a%20report%20that%20has%20date%2Ftime%20information%20however%2C%20I'm%20having%20problems%20with%20converting%20this%20field%20to%20a%20date%20formatting.%20I%20have%20tried%20every%20trick%20in%20the%20book%20to%20get%20it%20down%2C%20including%20text-to-column.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anybody%20could%20assist%20-%20it'd%20be%20greatly%20appreciated.%20Data%20example%20attached.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2317286%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2317343%22%20slang%3D%22en-US%22%3ERe%3A%20text%20to%20date%20formatting%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2317343%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1041172%22%20target%3D%22_blank%22%3E%40ncstle%3C%2FA%3E%26nbsp%3BIf%20it's%20just%20the%20date%20date%20you%20need%2C%20use%20text-to-columns%20to%20separate%20the%20date%20from%20the%20time%20stamp%20using%20space%20as%20the%20delimiter.%20Then%20select%20%22Date%3A%20MDY%22%20as%20the%20data%20type%20for%20the%20date%20column%20and%20skip%20the%20time%20column.%20You'll%20end%20up%20with%20a%20column%20containing%20real%20dates.%3C%2FP%3E%3CP%3EAlternatively%2C%20use%20Power%20Query.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2317344%22%20slang%3D%22en-US%22%3ERe%3A%20text%20to%20date%20formatting%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2317344%22%20slang%3D%22en-US%22%3EI%20do%20need%20both%20the%20date%20and%20time.%20Do%20you%20have%20have%20any%20suggestions%20to%20resolve%20this%20that%20includes%20both%3F%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi - I have a field in a report that has date/time information however, I'm having problems with converting this field to a date formatting. I have tried every trick in the book to get it down, including text-to-column. 

 

If anybody could assist - it'd be greatly appreciated. Data example attached. 

4 Replies

@ncstle If it's just the date date you need, use text-to-columns to separate the date from the time stamp using space as the delimiter. Then select "Date: MDY" as the data type for the date column and skip the time column. You'll end up with a column containing real dates.

Alternatively, use Power Query.

I do need both the date and time. Do you have have any suggestions to resolve this that includes both?

@ncstle Actually, am playing around with your table a bit. Try Find & Replace. Find AM and PM and replace with nothing (blank). Fair chance that your Excel will immediately transform the cells to real date/time cells.

 

If not, I'd go for Power Query. Especially if you need to do this very frequently and on large(r) data sets.

 

I don't think your data is in a proper time format as there is no space between the time and the AM/PM. Try doing a find/replace (ctrl+h) on your actual delivery column and add a space by replacing "AM" with " AM" and "PM" with " PM" (without quotes - I used the quotes so you can seep where the space is).