date corruption? 00/01/1900

[cjohnsonuk]

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

 

[Ron Rosenfeld]

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.

 

[Living the Dream]

I'm leaning more towards your actual layout.

Try the following as it works for me using XL2010

I could not replicate your formula =Date(Lookup!A5,1,1) as it returned a
#Ref, but I got it to work by using the following:

A5 = Year, B5 = Month, C5 = Day =DATE(Lookup!A5,Lookup!B5,Lookup!C5).

(Depending on your preferred format for T4 [ I'm using Medium Date], use
Custom Format): I get the date 1-Jan-2012.

All your other Cells should be formatted as "dd-Mmm-yyyy h:mm" to begin
your 1/2 day columns.

Now, as for you comment relating to Z2, I got it to display O.K. using
the following:

="Value is " & TEXT(Z4,"dd/Mmm/yyyy h:mm")

As Ron has intimated, as we do not have an active sample of your
Workbook it's difficult to analyse.

HTH
Mick.