change date format without retyping dates
 

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?

change date format without retyping dates
 

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 DataText to ColumnsFinish.

Might do the job.

Or DataText to ColumnsNextNextColumn Data FormatDate.

Choose from the dropdown and Finish.


Gord Dibben MS Excel MVP


On Tue, 21 Apr 2009 13:11:03 -0700, sarajane82
wrote:

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?

change date format without retyping dates
 

Thank you, that worked!!!

"Gord Dibben" wrote:

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 DataText to ColumnsFinish.

Might do the job.

Or DataText to ColumnsNextNextColumn Data FormatDate.

Choose from the dropdown and Finish.


Gord Dibben MS Excel MVP


On Tue, 21 Apr 2009 13:11:03 -0700, sarajane82
wrote:

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?

change date format without retyping dates
 

Good to hear.

Gord

On Wed, 22 Apr 2009 07:08:02 -0700, sarajane82
wrote:

Thank you, that worked!!!

"Gord Dibben" wrote:

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 DataText to ColumnsFinish.

Might do the job.

Or DataText to ColumnsNextNextColumn Data FormatDate.

Choose from the dropdown and Finish.


Gord Dibben MS Excel MVP


On Tue, 21 Apr 2009 13:11:03 -0700, sarajane82
wrote:

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?