Date Formating Help

Joneye

Well-known Member
Joined
May 28, 2010
Messages
749
Office Version
  1. 2016
Platform
  1. MacOS
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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,435
DateDate
Jan 10, 2019 1:49 PM10/01/2019
Feb 6, 2020 4:49 PM06/02/2020
Feb 4, 2019 5:57 PM04/02/2019
Feb 25, 2019 6:34 PM25/02/2019
Feb 22, 2020 9:13 AM22/02/2020
Feb 2, 2019 3:31 PM02/02/2019
Feb 1, 2020 7:00 AM01/02/2020
Feb 1, 2020 6:55 AM01/02/2020
Dec 3, 2018 4:04 PM03/12/2018
Dec 21, 2018 4:47 PM21/12/2018
Dec 2, 2018 6:42 PM02/12/2018
Dec 18, 2019 5:03 AM18/12/2019
Dec 18, 2019 10:26 AM18/12/2019
Dec 16, 2017 11:39 AM16/12/2017
Dec 15, 2018 2:17 PM15/12/2018
Dec 14, 2017 4:03 PM14/12/2017
Aug 8, 2018 6:48 PM08/08/2018
Aug 8, 2018 2:24 PM08/08/2018
Aug 7, 2020 6:07 PM07/08/2020
Aug 7, 2020 5:53 PM07/08/2020
Aug 6, 2020 11:11 PM06/08/2020
Aug 4, 2020 4:30 PM04/08/2020

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

Joneye

Well-known Member
Joined
May 28, 2010
Messages
749
Office Version
  1. 2016
Platform
  1. MacOS
Thanks, running Excel 2016 in mac, ill look at this however keen to see if its possible to do it in a formula?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,435
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

Well-known Member
Joined
May 28, 2010
Messages
749
Office Version
  1. 2016
Platform
  1. MacOS

ADVERTISEMENT

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

Well-known Member
Joined
Oct 24, 2015
Messages
7,435
I said profile (account details) not a post
pr.png
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,510
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
AB
1Jan 10, 2019 1:49 PM10/01/2019
2Feb 6, 2020 4:49 PM6/02/2020
3Feb 4, 2019 5:57 PM4/02/2019
4Feb 25, 2019 6:34 PM25/02/2019
5Feb 22, 2020 9:13 AM22/02/2020
6Feb 2, 2019 3:31 PM2/02/2019
7Feb 1, 2020 7:00 AM1/02/2020
8Feb 1, 2020 6:55 AM1/02/2020
9Dec 3, 2018 4:04 PM3/12/2018
10Dec 21, 2018 4:47 PM21/12/2018
11Dec 2, 2018 6:42 PM2/12/2018
12Dec 18, 2019 5:03 AM18/12/2019
13Dec 18, 2019 10:26 AM18/12/2019
14Dec 16, 2017 11:39 AM16/12/2017
15Dec 15, 2018 2:17 PM15/12/2018
16Dec 14, 2017 4:03 PM14/12/2017
17Aug 8, 2018 6:48 PM8/08/2018
18Aug 8, 2018 2:24 PM8/08/2018
19Aug 7, 2020 6:07 PM7/08/2020
20Aug 7, 2020 5:53 PM7/08/2020
21Aug 6, 2020 11:11 PM6/08/2020
22Aug 4, 2020 4:30 PM4/08/2020
Extract Date
Cell Formulas
RangeFormula
B1:B22B1=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

Well-known Member
Joined
May 28, 2010
Messages
749
Office Version
  1. 2016
Platform
  1. MacOS
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
AB
1Jan 10, 2019 1:49 PM10/01/2019
2Feb 6, 2020 4:49 PM6/02/2020
3Feb 4, 2019 5:57 PM4/02/2019
4Feb 25, 2019 6:34 PM25/02/2019
5Feb 22, 2020 9:13 AM22/02/2020
6Feb 2, 2019 3:31 PM2/02/2019
7Feb 1, 2020 7:00 AM1/02/2020
8Feb 1, 2020 6:55 AM1/02/2020
9Dec 3, 2018 4:04 PM3/12/2018
10Dec 21, 2018 4:47 PM21/12/2018
11Dec 2, 2018 6:42 PM2/12/2018
12Dec 18, 2019 5:03 AM18/12/2019
13Dec 18, 2019 10:26 AM18/12/2019
14Dec 16, 2017 11:39 AM16/12/2017
15Dec 15, 2018 2:17 PM15/12/2018
16Dec 14, 2017 4:03 PM14/12/2017
17Aug 8, 2018 6:48 PM8/08/2018
18Aug 8, 2018 2:24 PM8/08/2018
19Aug 7, 2020 6:07 PM7/08/2020
20Aug 7, 2020 5:53 PM7/08/2020
21Aug 6, 2020 11:11 PM6/08/2020
22Aug 4, 2020 4:30 PM4/08/2020
Extract Date
Cell Formulas
RangeFormula
B1:B22B1=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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,510
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :) ... and for updating your profile. (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,119,015
Messages
5,575,560
Members
412,676
Latest member
Davejf81
Top