Mysterious date format change - help pls!

[juliet_jia]

Hi all,

From many daily reports, I copy some data into a master excel file. These data include dates with different formats, e.g. 15/10/2011 or 15/10 (date/month) or 1400/09 (time/date) or sometimes only 05 (date). All these are pasted special as values into the master file.

The problem comes when I clean up these different formats - I don't make changes to the original pasted-in data, but only add columns next to it. BUT the original values always changes when macros are ran!! Troubles are in those cells only with dates in:

E.g. 05 will be shown as 05/01/1900 in the 1st round, then sometimes will change to 01/05/1900 (i.e. month and date shifts position)....... if the date is >12, no month-date mess though of course.

Any one has ANY inputs will be very much appreciated!

Many thanks
Juliet

 

[Derek Brown]

Dates are held as the number of days since 01/01/1900 ("05 will be shown as 05/01/1900 in the 1st round") and what you see depends on the way the cells are formatted. Formatting a cell does not change the value in the cell - only the way it is displayed.
Perhaps you should format the cells in the master workbook before pasting as 'values'?
Have a look at the following for more information:
http://www.cpearson.com/excel/datetime.htm