Date formatting

[ab123456]

I have a list of data in excel with the data arranged in rows, the data is a mix of text, numbers and dates. When i open the file and examine the data everything is fine.

Now the problem ... I want to move this worksheet on to a new sheet in an existing workbook, i use the edit >>>> move/copy sheet and the sheet is moved but when i examine the data the date format is inconsitent.

In my source sheet the date is formated dd/mm/yy but when i move it across it is a mix of dd/mm/yy and mm/dd/yy. What appears to be happening is that for dates with dd less than 12 it reverts to mm/dd format.

eg in my source sheet 01/07/05 (1st July) becomes 07/01/05 (7th Jan) but 17/06/05 (17th June) remains in that format.

There are too many cells affected to change each one individually and i cant find anyway of making any format the default for a workbook.

 

[CajunCenturion]

One possibility is to change the default system settings.

Start->Settings->Control Panel->Region and Language Options

On the Regional Options tab, press the customize button, then select the Date tab. Once there, you can change your desired formats.

[blue]Note: These will be system-wide settings so use with caution.[/blue]


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[JTDTimberframe]

ab123456,

You don't mention which version of excel you are using, and I don't know if it makes a difference, but in the version I use (2003) I simply right click on the cell, and select properties. Once there select date in the category on the left hand column, and then if you change the local in the drop down box at the bottom right the date format can be changed within Excel ONLY (and for that sheet only I think) to the English or American formats.

I realise that 2003 is the latest 'edition' and this feature may not be available in earlier, but that's what I have on my company pc. Will check on '97 at home tonight!

Hope that this helps

Steve

Stephen Napper BEng(Hons) AMIMechE

http://www.jtdtimberframe.co.uk

 

[ab123456]

CajunCenturion :
My language options in control panel is set dd/mm/yyyy

Timberframe :
I am on 2003. I have a sheet with about 20000 rows so doing each cell manually would take me for ever and this is an activity i need to do regularly. Also as the format isnt consistent within each column i cant just select and format the entire column.

It appears that it isnt the format that is changing but the actual value. The cell i gave in the example, 01/07/05 on my source sheet which become 07/01/05 on the target sheet if i try to format that so it actually spells out the month I get 7th January 05 where the real date should be 1st July.

 

[JTDTimberframe]

ab123456,

All I can suggest in this case is copy and paste special, paste values and see what happens. If Excel still changes the values, I really am at a loss.

I realise you say the 'error' isn't consitent, but if you save a copy and try to format all the cells in the column it might be worth the try?

Sorry I can't be more helpfull.



Stephen Napper BEng(Hons) AMIMechE

http://www.jtdtimberframe.co.uk

 

[zappedagain]

ab,

From Excel's help:

"Because the rules that govern the way that any calculation program interprets dates are complex, you should be as specific as possible about dates whenever you enter them. This will produce the highest level of accuracy in your date calculations." - this sounds like a possible cause of the problem you are having.

Try checking the values with the 'edate' function. Process a cell in both worksheets and see if they both come up with the same value; otherwise it does sound like there is some data corruption going on. If so you might be able to add a column with the edate function and then paste that to the new spreadsheet and format it there.

Formatted Date > number > copy > paste > format as date

instead of

Formatted date > copy > paste > Misformatted date

 

[joerd]

It might be that the data were pasted from another source, eg. a text file. When pasting, Excel converts numbers that look like dates (such as 01/07/05) to a date serial number, which is Excel's internal timekeeping system. Therefore, the data that you have in your orginal may already be corrupted.
You can check this by formatting the date cell to something explicit, like "dd-mmm-yyyy", which should format the date like "01-Jul-2005". If it doesn't, and shows "07-Jan-2005" the conversion was made wrong while importing/pasting the original data.
You might fix it by using the Excel date functions DAY, MONTH, YEAR, test for the cases where the mixup occurs, and then swap day and month. You can then regenerate the proper date serial by using the DATE function.
Good luck!

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.