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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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?
 
Upvote 0
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.
 
Upvote 0
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;;;
 
Upvote 0

Forum statistics

Threads
1,218,792
Messages
6,144,512
Members
450,550
Latest member
Melanie_Annies

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
Back
Top