PASTE SPECIAL LINK - PROBS WITH DATE.

[jgmckee]

Hi All,

Can someone please tell me how I go about counting the number of cells in a column that have a date in them.

I have cells that have dates and I have cells that just have numbers in them, I want to set it up so that I get an automatic count of just those cells with dates in them.

Thanks very much.

Jason.

 

[GlennUK]

What has this to do with PASTE SPECIAL LINK?

What is the numeric range of the non-date cells? Will any of the numbers be above 30,000? What is the earliest possible date that will appear?

 

[jgmckee]

Hi there,

This is the problem I have at the moment.

I have one worksheet with a column which is blank.

As dates are entered into this I want these dates automatically pasted into another worksheets column.

What I have done is copied and pasted links from the original worksheet column into the second worksheet column.

When I enter a date into the original worksheet they get copied across fine, but those cells in the original worksheet that do not have a date in them and are just blank, I am getting a date of 00 January 1900.

I have set up a count of the second worksheets column for those cells with dates in them but they are also counting the cells that have the date 00 January 1900 (these are empty cells in the original worksheet).

Can I set it up so that unless there is a date in the first worksheets column nothing gets pasted across to the second worksheet, rather than the date 00 January 1900. This would then let me count the cells with dates in them on the second worksheet.

Thanks

Jason.

 

[GlennUK]

A link will produce a result of zero if the source cell is blank, and 0 is equivalent to 31/Dec/1899 or, as Excel displays it 00/Jan/1900. So to count those dates that are "real" use COUNTIF with a clause of >0.

To alter the display of the zero cells ( from 00/Jan/1900 ) use a custom number format:

[>30000]dd mmmm yyyy;;;