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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,687
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,687
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;;;
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,053
Messages
5,835,149
Members
430,342
Latest member
Sailingexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top