exceldateformattingexcel-2016

"Autocorrect format" for all values in a column (instead of line by line)

[In Excel 2016] I received worksheets with dates arranged like MM/DD/YYYY, but I need it in YYYY/MM/DD order. I use formulas to rearrange them into the correct order in a string, then paste all as values.

Original date on left, calculated date on right

However, after pasting as values, excel doesn't seem to to actually recognize the final result as a date, unless I go line by line.

1st row: Result after manually "opening and closing" the cell data. 2nd row: Manually editing the data. 3rd row: Value still in previous format.

I was going to design a macro to do this, but when I record the macro to see what the action is, it doesn't show any steps apart from "selecting" the cell.

I've tried changing the formats from General to Text to Date and every which way, but nothing seems to work apart from line by line. Would greatly appreciate some help in understanding what I'm missing (and also if I'm in the right place)!

I found a couple other questions close to mine, but they seemed to refer to converting using specific coding methods (i.e. pandas python) which is a bit beyond my requirements.

Steps to reproduce:

  1. Rearrange date values using a formula.
  2. Paste calculated values as values only.

What I expected to happen: Values would automatically "auto-detect" the format for the entire column.

What happened: Values remain as as basic text string (instead of changing to excel date values), and only update after manually opening and closing each cell.

Solution

  • Try this method:

    Open the file contains your data (the same file mentioned in your question)

    From the Data Ribbon select:

    New Query>From File>From Workbook

    Now select the same file you opened already

    From the dialouge box select the sheet which contains the dates, then Press Load

    New worksheet will created, with table contains values with correct date.

    In the same created sheet:

    Choose Column A

    Go to the Design Ribbon

    Press the Button: Convert to Range

    Confirm the process by choosing Ok from the popup message.