| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing Sort Codes
Hi there
Currently importing banking data that includes sort codes but EXcel 2003 is automatically converting the 01-07-07 format into date format 01/07/07. When I format to Custom or Text it appears as 39264. Any suggestions on a formula I can run to convert them all to the correct value in text/custom format? Thanks Claire |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Importing Sort Codes
=text(A1,"dd-mm-yy")
will convert it back to the code "ClaireBrowny" wrote: Hi there Currently importing banking data that includes sort codes but EXcel 2003 is automatically converting the 01-07-07 format into date format 01/07/07. When I format to Custom or Text it appears as 39264. Any suggestions on a formula I can run to convert them all to the correct value in text/custom format? Thanks Claire |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Importing Sort Codes
Don't convert then to text! 39264 is just the number of days since
Jan-01-1900. Microsoft started there time zero in 1900. All you have to do is highlight the column of data. Then go to Tool Menu - Format - Cells - Number - Date and select one of the date formats. The numbers will all change to dates. "ClaireBrowny" wrote: Hi there Currently importing banking data that includes sort codes but EXcel 2003 is automatically converting the 01-07-07 format into date format 01/07/07. When I format to Custom or Text it appears as 39264. Any suggestions on a formula I can run to convert them all to the correct value in text/custom format? Thanks Claire |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Importing ZIP codes in different formats via query | Excel Discussion (Misc queries) | |||
| How to sort by ZIP codes listed in one column in Excel spread sh? | Excel Discussion (Misc queries) | |||
| How do I sort area codes by time zones | Excel Discussion (Misc queries) | |||
| How do you sort zip codes with 0's being the lead number? | Excel Discussion (Misc queries) | |||
| Excel doesn't sort zip codes properly | Excel Discussion (Misc queries) | |||
Linear Mode
