Problem in weeknum formula

September 16, 2010 at 22:52:55
Specs: Windows 7
On column C, row 5 dated as "10/09/2010"

On column D, row 5 used formula is "=week"

I hve defined a Name Manager formula to work "=week" formula -

Note: this condition will work at any cell of the workbook and its working great.

My query when i re-opening the file for third time i getting a value as "#VALUE!" in that particular cell, Pls assist me on this.

See More: Problem in weeknum formula

September 17, 2010 at 07:41:26
Your formula will work as long as the data in cell C5 is entered as a real date, ie 10/9/2010

If you enter the date surrounded with quotes, ie "10/9/2010", as shown in your message, then you will get the VALUE error.


Report •

September 17, 2010 at 14:17:53

I presume it was the cell containing =week that showed #VALUE

If you enter the formula =IF(DATA!$C5="","","WEEK-"&WEEKNUM(DATA!$C5)-1)
in another cell, at the same time as =week shows the #VALUE, does the cell containing the full formula also show #VALUE, or does it give a WEEK-xx message.

If it gives the #VALUE error, use formula auditing to see where the error is occurring.

Select the cell then:
In Excel 2003 goto 'Tools' - 'Formula Auditing' and select 'Evaluate Formula'
In Excel 2007/2010 from the Ribbon select 'Formulas' then 'Evaluate Formula' in the 'Formula Auditing' section.

Then in the Evaluate Formulas dialog box, click Evaluate.
Look at the part of the formula that is underlined, and this is what will be evaluated at the next click of the 'Evaluate' button.

Between these two items - comparing the result of the full formula and formula evaluation - it should narrow down the source of the problem.

Let us know what you get.


Report •

September 17, 2010 at 14:32:09
I set up a workbook with the data and formula from your post on 2 different machines running 2003, opened and closed the files numerous times, and never had a problem.

Do what I did:

Copy and paste the items from your post into a worksheet. Since they worked fine for me, they should work fine for you. ;-)

Report •
Related Solutions

Ask Question