Date Formating Help

[Joneye]

Hi All,

All im looking to learn more about is how can i use formlas to translate the below into DD/MM/YY example 10/01/2019 for the first line.

Jan 10, 2019 1:49 PM

Feb 6, 2020 4:49 PM

Feb 4, 2019 5:57 PM

Feb 25, 2019 6:34 PM

Feb 22, 2020 9:13 AM

Feb 2, 2019 3:31 PM

Feb 1, 2020 7:00 AM

Feb 1, 2020 6:55 AM

Dec 3, 2018 4:04 PM

Dec 21, 2018 4:47 PM

Dec 2, 2018 6:42 PM

Dec 18, 2019 5:03 AM

Dec 18, 2019 10:26 AM

Dec 16, 2017 11:39 AM

Dec 15, 2018 2:17 PM

Dec 14, 2017 4:03 PM

Aug 8, 2018 6:48 PM

Aug 8, 2018 2:24 PM

Aug 7, 2020 6:07 PM

Aug 7, 2020 5:53 PM

Aug 6, 2020 11:11 PM

Aug 4, 2020 4:30 PM

 

[sandy666]

Date		Date
Jan 10, 2019 1:49 PM		10/01/2019
Feb 6, 2020 4:49 PM		06/02/2020
Feb 4, 2019 5:57 PM		04/02/2019
Feb 25, 2019 6:34 PM		25/02/2019
Feb 22, 2020 9:13 AM		22/02/2020
Feb 2, 2019 3:31 PM		02/02/2019
Feb 1, 2020 7:00 AM		01/02/2020
Feb 1, 2020 6:55 AM		01/02/2020
Dec 3, 2018 4:04 PM		03/12/2018
Dec 21, 2018 4:47 PM		21/12/2018
Dec 2, 2018 6:42 PM		02/12/2018
Dec 18, 2019 5:03 AM		18/12/2019
Dec 18, 2019 10:26 AM		18/12/2019
Dec 16, 2017 11:39 AM		16/12/2017
Dec 15, 2018 2:17 PM		15/12/2018
Dec 14, 2017 4:03 PM		14/12/2017
Aug 8, 2018 6:48 PM		08/08/2018
Aug 8, 2018 2:24 PM		08/08/2018
Aug 7, 2020 6:07 PM		07/08/2020
Aug 7, 2020 5:53 PM		07/08/2020
Aug 6, 2020 11:11 PM		06/08/2020
Aug 4, 2020 4:30 PM		04/08/2020

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Parse = Table.TransformColumns(Source,{{"Date", each Date.From(DateTimeZone.From(_)), type date}})
in
    Parse

 

[Joneye]

Thanks, running Excel 2016 in mac, ill look at this however keen to see if its possible to do it in a formula?

 

[sandy666]

I don't know if your version XL on Mac contain Power Query, probably not.
Would be great if you will update your profile about Excel version and OS, less troubles and wasted posts
if you want formula you'll need to wait for someone else

 

[Joneye]

I don't know if your version XL on Mac contain Power Query, probably not.
Would be great if you will update your profile about Excel version and OS, less troubles and wasted posts
if you want formula you'll need to wait for someone else

Version 16.16.27 (201012) - applogies been a while since i've posted here. Opps

 

[sandy666]

I said profile (account details) not a post

 

[Peter_SSs]

Try this formula then format the result column with the date format that you want as it isn't entirely clear from your post as the highlighted formats below are different.

translate the below into DD/MM/YY example 10/01/2019 for the first line.

20 11 04.xlsm
	A	B
1	Jan 10, 2019 1:49 PM	10/01/2019
2	Feb 6, 2020 4:49 PM	6/02/2020
3	Feb 4, 2019 5:57 PM	4/02/2019
4	Feb 25, 2019 6:34 PM	25/02/2019
5	Feb 22, 2020 9:13 AM	22/02/2020
6	Feb 2, 2019 3:31 PM	2/02/2019
7	Feb 1, 2020 7:00 AM	1/02/2020
8	Feb 1, 2020 6:55 AM	1/02/2020
9	Dec 3, 2018 4:04 PM	3/12/2018
10	Dec 21, 2018 4:47 PM	21/12/2018
11	Dec 2, 2018 6:42 PM	2/12/2018
12	Dec 18, 2019 5:03 AM	18/12/2019
13	Dec 18, 2019 10:26 AM	18/12/2019
14	Dec 16, 2017 11:39 AM	16/12/2017
15	Dec 15, 2018 2:17 PM	15/12/2018
16	Dec 14, 2017 4:03 PM	14/12/2017
17	Aug 8, 2018 6:48 PM	8/08/2018
18	Aug 8, 2018 2:24 PM	8/08/2018
19	Aug 7, 2020 6:07 PM	7/08/2020
20	Aug 7, 2020 5:53 PM	7/08/2020
21	Aug 6, 2020 11:11 PM	6/08/2020
22	Aug 4, 2020 4:30 PM	4/08/2020
Extract Date

Cell Formulas
Range	Formula
B1:B22	B1	=LEFT(SUBSTITUTE(REPLACE(MID(A1,5,2)&A1,6,3,""),",",""),10)+0

To update your profile click your user name at the top right of the forum and choose 'Account details'. (Don’t forget to scroll down & ‘Save’)

 

[Joneye]

Try this formula then format the result column with the date format that you want as it isn't entirely clear from your post as the highlighted formats below are different.

20 11 04.xlsm
	A	B
1	Jan 10, 2019 1:49 PM	10/01/2019
2	Feb 6, 2020 4:49 PM	6/02/2020
3	Feb 4, 2019 5:57 PM	4/02/2019
4	Feb 25, 2019 6:34 PM	25/02/2019
5	Feb 22, 2020 9:13 AM	22/02/2020
6	Feb 2, 2019 3:31 PM	2/02/2019
7	Feb 1, 2020 7:00 AM	1/02/2020
8	Feb 1, 2020 6:55 AM	1/02/2020
9	Dec 3, 2018 4:04 PM	3/12/2018
10	Dec 21, 2018 4:47 PM	21/12/2018
11	Dec 2, 2018 6:42 PM	2/12/2018
12	Dec 18, 2019 5:03 AM	18/12/2019
13	Dec 18, 2019 10:26 AM	18/12/2019
14	Dec 16, 2017 11:39 AM	16/12/2017
15	Dec 15, 2018 2:17 PM	15/12/2018
16	Dec 14, 2017 4:03 PM	14/12/2017
17	Aug 8, 2018 6:48 PM	8/08/2018
18	Aug 8, 2018 2:24 PM	8/08/2018
19	Aug 7, 2020 6:07 PM	7/08/2020
20	Aug 7, 2020 5:53 PM	7/08/2020
21	Aug 6, 2020 11:11 PM	6/08/2020
22	Aug 4, 2020 4:30 PM	4/08/2020
Extract Date

Cell Formulas
Range	Formula
B1:B22	B1	=LEFT(SUBSTITUTE(REPLACE(MID(A1,5,2)&A1,6,3,""),",",""),10)+0

To update your profile click your user name at the top right of the forum and choose 'Account details'. (Don’t forget to scroll down & ‘Save’)

Hi Peter,

Massive help and solved - really appreciated. Also updated my profile.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up. ... and for updating your profile.