change date format without retyping dates

  • Thread starter Thread starter sarajane82
  • Start date Start date
S

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?
 
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
 
Thank you, that worked!!!

Gord Dibben said:
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
Click to expand...
 
Back
Top