ExcelBanter - View Single Post

Ron Rosenfeld[_2_] · **date corruption? 00/01/1900**

On Wed, 7 Nov 2012 16:52:53 -0800 (PST), wrote:

I have a spread sheet that has a row of calculated dates for a complete year in row 4. cell T4 is the first date in my calendar and is set to be =DATE(Lookup!A5,1,1) where lookup!A5 contains the number "2012". U4 is =t4+.5 and this goe s on to about ABZ4 giving me 2 columns for every day in the year (an AM and PM column). Row 4 is formatted as a date and shows as dd/mm/yyyy.

However when I use formulas that refer to the cells in row 4 (my dates) they all return zero. eg if I set z1 to be =z4 then it displays 0 when formatted as a number or 00/01/1900 when formatted as a date. if z2 is set to ="value is "&z4 I get "value is 0" displayed.

But Copy and paste this range to a new worksheet and it works fine. Ive got a whole load of sheets in this workbook with data validation and conditional formatting. I really don't want to have to start again

Am I missing something really obvious?

There seems to be a lot of this about but not identical to what I'm seeing

Following the steps as you outlined them, I cannot reproduce your problem. I suggest you upload a workbook that demonstrates the problem, with sensitive data removed, to a file sharing site (e.g. live.skydrive.com) and then post a link to that workbook here.