Complex date conversion

%3CLINGO-SUB%20id%3D%22lingo-sub-2275271%22%20slang%3D%22en-US%22%3EComplex%20date%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2275271%22%20slang%3D%22en-US%22%3E%3CP%3EI%20received%20a%20large%20workbook%20with%20a%20string%20of%20dates%20formatted%20as%20general%20that%20are%20also%20in%20an%20odd%20order%20for%20my%20needs.%20For%20example%20these%20are%20some%20of%20the%20dates%3C%2FP%3E%3CTABLE%20width%3D%22128%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22128%22%3E20200806%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E20200804%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E20191105%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EThe%20first%20is%20August%206%2C%202020%20ect..%3C%2FP%3E%3CP%3EI%20need%20an%20easy%20way%20to%20update%20all%20of%20these%20to%20a%20date%20I%20can%20work%20with%20like%2008%2F06%2F2020.%20I%20tried%20using%20the%20date%20formatting%20before%20and%20after%20changing%20the%20way%20my%20computer%20interprets%20dates%20-%20neither%20worked.%20I%20found%20the%20date%20function%20that%20will%20take%20the%20general%20number%20and%20change%20to%20a%20date%202020%2F08%2F06%20-%20at%20this%20point%20I%20can%20use%20formatting%20to%20switch%20it%20around%20but%20how%20would%20I%20copy%20that%20too%20all%20the%20other%20cells%3F%20I%20can%20only%20copy%20the%20formula%20that%20will%20put%20the%20date%20from%20the%20source%20but%20I%20want%20the%20formula%20to%20use%20the%20data%20in%20my%20new%20cell.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2275271%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
New Contributor

I received a large workbook with a string of dates formatted as general that are also in an odd order for my needs. For example these are some of the dates

20200806
20200804
20191105

The first is August 6, 2020 ect..

I need an easy way to update all of these to a date I can work with like 08/06/2020. I tried using the date formatting before and after changing the way my computer interprets dates - neither worked. I found the date function that will take the general number and change to a date 2020/08/06 - at this point I can use formatting to switch it around but how would I copy that too all the other cells? I can only copy the formula that will put the date from the source but I want the formula to use the data in my new cell. 

2 Replies

@mjordan2255 

Select the dates in a single column (if you have multiple columns with such values, you'll have to handle each of them separately).

On the Data tab of the ribbon, click Text to Columns.

Select Delimited, then click Next >.

Click Next > again.

In Step 3, select Date, then select YMD.

Click Finish.

If necessary, widen the column and specify the desired date format.

Perfect! Thank you so much!!!