I have set up a series of Excel spreadsheets which are linked by a macro. The macro copies and pastes data from one spreadsheet to another (amongst other things)Somehow, someone has set their spreadsheet to the 1904 date system so now one of the linked spreadsheets contains a mixture of dates in the 1900 and 1904 formats which messes up the chronological order :-(( Can anyone suggest an easy way to convert a range of cells from 1904 format to 1900 whilst leaving the rest unaltered? Tearing my hair out! thanks Shaun

Just a thought, but if your macro pastes from other sheets, can you get it to paste just the value, (as in paste special)? The date is just a number but if you simply "paste" it will also paste the original format, overriding the format of the destination cell. If you can "paste values", your destination cell should determine how it displays and it won't matter how the original sheets is set up.

I do use paste special, but if the value pasted is generated in the 1904 format and is pasted into a sheet in 1900 format you get the wrong date displayed. The actual value pasted is a numeric value calculating the days from 1900 or 1904 to the date specified, hence same date, two formats two different pasted values. Thanks for trying. Shaun

Very interesting problem. I think hair pulling is in order. It seems that the 1904 date system is tied to the workbook. Can you convert the 1904 workbook to 1900 values and then update the linked sheets? You might want to check John Walkenbach's site, or the MS forum for Excel.