| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
date format help needed please
sorry to post twice- but not sure where this question belongs...I have a
column of mixed format.... some numbers and some are brought in as numbers stored as text. I need to apply a date format of mmddyyy however, when I apply to column, the values that were numbers stored as text display the error code: ########### and the pop up tip reads "dates and times that are negative or too large display as #######". I have tried formatting the numbers stored as text into number values before I apply the date value, but excel drops the leading zero's and that is unacceptable for the reports I need to submit. Any help would be greatly appreciated.... Thank you very much. Candice |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
date format help needed please
=IF(ISNUMBER(A1),A1,DATE(RIGHT(A1,4),LEFT(A1,2),MI D(A1,3,2)))
Adjust to suit the format of your text input. -- David Biddulph "Mominator" wrote in message ... sorry to post twice- but not sure where this question belongs...I have a column of mixed format.... some numbers and some are brought in as numbers stored as text. I need to apply a date format of mmddyyy however, when I apply to column, the values that were numbers stored as text display the error code: ########### and the pop up tip reads "dates and times that are negative or too large display as #######". I have tried formatting the numbers stored as text into number values before I apply the date value, but excel drops the leading zero's and that is unacceptable for the reports I need to submit. Any help would be greatly appreciated.... Thank you very much. Candice |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
date format help needed please
THANK YOU!!!
You saved my sanity. "David Biddulph" wrote: =IF(ISNUMBER(A1),A1,DATE(RIGHT(A1,4),LEFT(A1,2),MI D(A1,3,2))) Adjust to suit the format of your text input. -- David Biddulph "Mominator" wrote in message ... sorry to post twice- but not sure where this question belongs...I have a column of mixed format.... some numbers and some are brought in as numbers stored as text. I need to apply a date format of mmddyyy however, when I apply to column, the values that were numbers stored as text display the error code: ########### and the pop up tip reads "dates and times that are negative or too large display as #######". I have tried formatting the numbers stored as text into number values before I apply the date value, but excel drops the leading zero's and that is unacceptable for the reports I need to submit. Any help would be greatly appreciated.... Thank you very much. Candice |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Format using VBA not activating when needed | Excel Discussion (Misc queries) | |||
| Convert date + time text format to date format | Excel Worksheet Functions | |||
| IF / Else Format help needed | Excel Worksheet Functions | |||
| Help needed with date format | Excel Discussion (Misc queries) | |||
| Excel 2000 date format cannot be set to Australian date format | Excel Discussion (Misc queries) | |||
Linear Mode
