Date Format Challenge

%3CLINGO-SUB%20id%3D%22lingo-sub-1961668%22%20slang%3D%22en-US%22%3EDate%20Format%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1961668%22%20slang%3D%22en-US%22%3E%3CP%3EGreetings!%3CBR%20%2F%3E%3CBR%20%2F%3EI%20perform%20a%20weekly%20data%20dump%20from%20a%20third%20party%20platform%20that%20I%20then%20copy%20into%20an%20Excel%20file%20so%20that%20I%20can%20manipulate%20to%20create%20pivot%20tables%20and%20produce%20various%20visualisations.%20The%20issue%20I%20have%20is%20that%20a%20number%20of%20columns%20in%20the%20data%20file%20are%20formatted%20as%20text.%20The%20formatting%20is%20also%20in%20MMDDYY%20yet%20I%20need%20it%20in%20DDMMYY.%20Sample%20of%20the%20values%20as%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22137%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22137%22%20height%3D%2217%22%3E3%2F7%2F19%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E14%2F6%2F19%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E3%2F5%2F19%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E29%2F6%2F19%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E8%2F4%2F19%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E31%2F1%2F18%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2217%22%3E%3CP%3E28%2F1%2F19%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20pivot%20tables%20and%20visualisations%20are%20already%20created%20so%20what%20I%20would%20like%20to%20do%20is%20simply%20copy%20the%20new%20data%20in%20each%20week%20and%20somehow%20have%20those%20columns%20convert%20to%20date%20format%20but%20in%20DDMMYY.%20Can%20this%20be%20done%20automatically%20and%20if%20so%20how%3F%3CBR%20%2F%3E%3CBR%20%2F%3EMany%20thanks%20in%20advance%20(Ive%20spent%20far%20too%20long%20already%20scratching%20my%20head%20how%20to%20do%20this)%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1961668%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1962234%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Format%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1962234%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F893136%22%20target%3D%22_blank%22%3E%40jch1975%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EYour%20sample%20data%20are%20d%2Fm%2Fyy.%20What%20gives%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1962281%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Format%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1962281%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F893136%22%20target%3D%22_blank%22%3E%40jch1975%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20select%20column%20with%20such%20texts%2C%20on%20ribbon%20Data-%26gt%3BText%20to%20Columns%2C%20on%20third%20step%20of%20the%20wizard%20select%20Date%20and%20MDY%20or%20which%20format%20do%20you%20have%20now.%20Finish%2C%20you'll%20have%20dates%20now%20and%20may%20apply%20any%20date%20format%20to%20them.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Greetings!

I perform a weekly data dump from a third party platform that I then copy into an Excel file so that I can manipulate to create pivot tables and produce various visualisations. The issue I have is that a number of columns in the data file are formatted as text. The formatting is also in MMDDYY yet I need it in DDMMYY. Sample of the values as follows:

 

3/7/19
14/6/19
3/5/19
29/6/19
8/4/19
31/1/18

28/1/19

 

The pivot tables and visualisations are already created so what I would like to do is simply copy the new data in each week and somehow have those columns convert to date format but in DDMMYY. Can this be done automatically and if so how?

Many thanks in advance (Ive spent far too long already scratching my head how to do this)

4 Replies

@jch1975

Your sample data are d/m/yy. What gives?

@jch1975 

 

You can covert the date format using the text function, 

=TEXT(A1,"DD/MM/YY")

 

text.png

@jch1975 

You may select column with such texts, on ribbon Data->Text to Columns, on third step of the wizard select Date and MDY or which format do you have now. Finish, you'll have dates now and may apply any date format to them.

Sadly just the way the third party platform spits out the data.