| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Change Date Format to Specific Text Format When Copying | Excel Discussion (Misc queries) | |||
| How do I change dates input as 20080426 to date format in Excel? | Excel Discussion (Misc queries) | |||
| Use date modified to change format & create filter to track change | Excel Worksheet Functions | |||
| how can i change dates from US to UK format | Excel Discussion (Misc queries) | |||
| Can I change a column of dates from USA to UK format? | Excel Discussion (Misc queries) | |||
Hybrid Mode
