1. ## Count hyperlinks

Dear all,

I have an excel worksheet like the following (there are about 20 cols and 500 rows)

Col h Col I Col J Col k Col L Col M
3/12/2013 Rob 1/2/2014 Susanna 3/3/2014 Tash
8/2/2014 Tash 19/11/2014 colin

Some of the dates has hyperlink associate to it, ie, not all date have hyperlink

Is there a way I can use formular to count number of hyperlink according to user defined time range, say before 1/1/2014, between 1/1/2014 and 30/10/2014, after 30/10/2014. So I may have the following results

before 1/1/2014
Tash 1
Colin 0
Rob 0
Suzanna 3

between 1/1/2014 and 30/10/2014
Tash 22
Colin 7
Rob 90
Suzanna 23

after 30/10/2014
Tash 10
Colin 2
Rob 1
Suzanna 3

Is the above achieveable using formular please?

2. ## Re: Count hyperlinks

If you want to count dates between to specified dates, you can use countifS(). However, if some of those dates are hyperlinked, and others not, I know of no regular formula that will be abke to tell the difference.

Formulas work on what the cell displays (generally), and not on what the cell contents are (formulas etc)

For instance, assume you have a cell (A1) that contains =10*10 It will display 100, if you used =left(A1,2) you would get 10, not =1...it is reading the 1st 2 characters of the answer, not the cell contents

Hope that helps?

3. ## Re: Count hyperlinks

You can extract that 10 out of the =10*10 like this...

=RIGHT(LEFT(FORMULATEXT(REFERENCED CELL WITH FORMULA),3),2)

That would pull the formula built into the cell and then give you first the left 3 and then the right 2 leaving you with 10

Now with all that said, would you mind posting a small book/sample file so that I can see the dates, formats etc...

Unless of course that formula gives you what you need

4. ## Re: Count hyperlinks

FORMULATEXT() is a function that (Im assuming) is available in 2013? It is not available in 2010 or, (I doubt) any earlier versions, and OP indicates they have 2010

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1