change date format without retyping dates

[sarajane82]

i am trying to fix a spreadsheet that a coworker made. it includes a formula
that subtracts one date from another. the problem is that the person put the
dates into the spreadsheet in "general" format. when i enter the formula
(which also includes a conditional), i get an error because the format of the
date is wrong. when i right click the dates and change the format to "date",
the numbers stay as they were when entered in "general" formatting, and the
only way i can get them to be correct and work with the formula is to
completely retype every single date AFTER changing the format to "date." is
there a way to make the format correct once the data is already in there
without having to retype everything?

 

[Gord Dibben]

Merely changing the format will not change the dates if they are not real
dates.

You have to force Excel to make the change.

I would try selecting the column of dates and Data>Text to Columns>Finish.

Might do the job.

Or Data>Text to Columns>Next>Next>Column Data Format>Date.

Choose from the dropdown and Finish.


Gord Dibben MS Excel MVP

 

[sarajane82]

Thank you, that worked!!!

Merely changing the format will not change the dates if they are not real
dates.

You have to force Excel to make the change.

I would try selecting the column of dates and Data>Text to Columns>Finish.

Might do the job.

Or Data>Text to Columns>Next>Next>Column Data Format>Date.

Choose from the dropdown and Finish.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Good to hear.

Gord

Thank you, that worked!!!