Power Query - changing date format

[lezawang]

Hi
I have a workbook which has a date column but formatted as a text because they are aligned to the left and also istext() is true. So I tried to use Power Query to convert them to date in date format not text. Please see the link for the file. Power Query fist showed me the date as picture1 (different format from Excel and it is aligned to the right) and then when I tried to click on the header of the column and select Date, it gave me "error", replaced date with error keyword. Then I cancelled the transform but to my surprised the date converted to what was in Excel format (exactly like excel and aligned to the left) - see picture two please, my questions

1) Why PowerQ behaved like that?
2) How you would covert date like that using PQ so the column will be showing date only not time. I was thinking to split from the right but I am thinking it must be better way?

Thank you very much

Book1.xlsx
	A	B	C	D
1	FirstName LastName	Dept	Starting Date
2	Skylar Widdows	Inventory	Tuesday, April 21, 2009 4:19 AM	TRUE
3	Georgia Simpson	Property department	Tuesday, October 6, 1992 11:39 AM
4	Marvin Salt	International Operations	Sunday, August 5, 2001 8:58 PM
5	Owen Mackenzie	Advertisement department	Sunday, January 12, 2014 5:39 PM
6	Eduardo Bloom	Non-Store Operations	Sunday, May 31, 2020 6:01 AM
7	Stacy Veale	Advertisement department	Saturday, March 20, 2021 7:25 PM
8	Cedrick Umney	Inventory	Monday, October 23, 2017 4:54 PM
9	Esmeralda Rose	Legal and public relation department	Thursday, September 28, 2000 9:42 PM
10	Emmanuelle Wright	Logistics	Monday, July 14, 2008 5:49 PM
11	Wade Hall	Advertisement department	Wednesday, August 12, 1998 8:17 AM
Sheet1

Cell Formulas
Range	Formula
D2	D2	=ISTEXT(C2)

Book1.xlsx

drive.google.com

 

[Alex Blakenburg]

Try this:-

• Select the Date column
• Transform > Date > Date Only

 

[lezawang]

It is working. Thank you very much.

 

[Alex Blakenburg]

No problem. Happy to help.