Calculating how long a cell has been an error

**John99** · 09-18-2009, 04:01 AM

Hi all!

I have a worksheet where I have some cells with #N/A N/A values. I'd like to know how long it has been that the function has not found a value for that cell.

So is it somehow possible to build a VBA function that would calculate the time that the cell has given #N/A N/A..? Or would it be easier to calculate how many times the worksheet has been opened and the value has been #N/A N/A..?

Any ideas??

Br,
John

**Andy Pope** · 09-18-2009, 04:40 AM

Maybe this would make more sense to us if you explained what the formula was and what changes that makes the formula return #N/A at some point.

Example workbook may also help

**John99** · 09-18-2009, 05:32 AM

Sorry for being confusing. I am retrieving data from Bloomberg and using Bloomberg Add-inn's function bdp. And the function returns #N/A N/A if it cannot find a certain information (eg. credit rating) for a instrument (eg. bond or stock) from Bloomberg's database.

So if I am not getting a value for an instrument for example in two months I should start to investigate if the instrument is still in the database.

John

**Andy Pope** · 09-18-2009, 05:37 AM

Not sure if this will work for you but could you.

Store the date the instrument is first added to the list of things to check. Say in the cell to the right.
Then in the cell to the right of that add a formula to check for NA and check date against today.