PASTE SPECIAL LINK - PROBS WITH DATE.

jgmckee

New Member
Joined
Jul 31, 2005
Messages
17
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.
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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

New Member
Joined
Jul 31, 2005
Messages
17
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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:
Code:
[>30000]dd mmmm yyyy;;;
 

Watch MrExcel Video

Forum statistics

Threads
1,118,286
Messages
5,571,313
Members
412,382
Latest member
Langtn02
Top