date conversion not responding

%3CLINGO-SUB%20id%3D%22lingo-sub-1781401%22%20slang%3D%22en-US%22%3Edate%20conversion%20not%20responding%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1781401%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20format%20a%20date%20column%20to%20MM%2FYYYY%2C%20however%2C%20none%20of%20my%20attempts%20to%20change%20the%20column%20are%20working.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1781401%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1781465%22%20slang%3D%22en-US%22%3ERe%3A%20date%20conversion%20not%20responding%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1781465%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F832757%22%20target%3D%22_blank%22%3E%40john_burrellj%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMost%20probably%20you%20have%20texts%20which%20kooks%20kike%20dates%2C%20not%20the%20dates%20in%20terms%20of%20Excel%20(actually%20they%20are%20integer%20number%20formatted%20as%20dates).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20so%2C%20you%20may%20convert%20texts%20to%20dates%20selecting%20the%20column%2C%20Data-%26gt%3BText%20to%20Columns%2C%20on%20the%20third%20step%20of%20the%20wizard%20for%20the%20column%20select%20Date%20and%20MDY%20(or%20DMY%2C%20or%20else%2C%20depends%20on%20how%20they%20are%20presented%20in%20texts).%20Finish%20and%20apply%20desired%20date%20format%20to%20the%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1781466%22%20slang%3D%22en-US%22%3ERe%3A%20date%20conversion%20not%20responding%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1781466%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F832757%22%20target%3D%22_blank%22%3E%40john_burrellj%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20probably%20means%20that%20the%20column%20contains%20text%20values%2C%20not%20real%20dates.%3C%2FP%3E%0A%3CP%3ESelect%20the%20column.%3C%2FP%3E%0A%3CP%3EOn%20the%20Data%20tab%20of%20the%20ribbon%2C%20click%20Text%20to%20Columns.%3C%2FP%3E%0A%3CP%3EClick%20Next%20%26gt%3B%20twice.%3C%2FP%3E%0A%3CP%3EIn%20Step%203%2C%20select%20Date%2C%20and%20select%20the%20format%20(MDY%2C%20DMY%2C%20...)%20matching%20the%20values%20in%20the%20column%20from%20the%20dropdown.%3C%2FP%3E%0A%3CP%3EClick%20Finish.%3C%2FP%3E%0A%3CP%3ECan%20you%20format%20the%20values%20now%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I am trying to format a date column to MM/YYYY, however, none of my attempts to change the column are working.

 

7 Replies
Highlighted

@john_burrellj 

Most probably you have texts which kooks kike dates, not the dates in terms of Excel (actually they are integer number formatted as dates).

 

If so, you may convert texts to dates selecting the column, Data->Text to Columns, on the third step of the wizard for the column select Date and MDY (or DMY, or else, depends on how they are presented in texts). Finish and apply desired date format to the column.

Highlighted

@john_burrellj 

That probably means that the column contains text values, not real dates.

Select the column.

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

Click Next > twice.

In Step 3, select Date, and select the format (MDY, DMY, ...) matching the values in the column from the dropdown.

Click Finish.

Can you format the values now?

Highlighted

@Hans Vogelaar 

I have attempted that several times, I should mention that the cell is concatenated.  I am not sure if that is a problem.  But I have attempted that format step several times to no avail.

 

Highlighted

@john_burrellj 

Could you attach a sample workbook without sensitive data?

Highlighted
Highlighted

@john_burrellj 

This isn't going to work. Excel doesn't recognize dates before the year 1900.

If you want to display the values as MM/YYYY, use

 

=D2&"/"&C2

 

You won't be able to perform date calculations with these values for the reason mentioned above.

Highlighted
Thanks, that solved my problem