calculation error using WEEKDAY()

[bevort]

I want to calculate the number of weerks in a year. I use the following formula that seems to work

Please Login or Register  to view this content.

'year' is a label to a cell containing the year in four digits (2007)
The formula returns the correct value (52 in this case)

Well then! Why complain?

If the customer saves the workbook under a differant name the formula craches with #name?

replacing the year label with a value like

Please Login or Register  to view this content.

Also give the same results the value is OK at first hand but craches when saving

Splitting the formula to find the function causing the problem I end up with nothing because all the parts itself work as expected. But as soon as I glue it together in an IF() the formula craches


I am using the evaluate formula tool to check it

[oldchippy]

WEEKNUM must have the Analysis Tool Pak added in, is that the problem?

http://www.cpearson.com/excel/ATP.htm

[bevort]

No. the analysis tools are active. Otherwise I would not even get results in the seperate blocks

[oldchippy]

You say If the customer saves the workbook under a differant name the formula craches with #name?

Have they got it installed?

[bevort]

Yes, becasue other formulas base on the Anlysis tools are working.

You could also read that I tested the formula using the evaluation tool for formulas as it is build in in Excel

[oldchippy]

Well, I've tried your second formula and it works fine here, also when I save the file and reopen it it's OK????? What are you doing???

[bevort]

I placed the last line of code

Please Login or Register  to view this content.

in a blank workbook and run the tools->Formula auditing->evaluate formula and get this #name? error imidiately

If I replace one of the parts WEEKNUM() with the value to be returned no error occures
So there must be something with the combination

[bevort]

Well, I've tried your second formula and it works fine here, also when I save the file and reopen it it's OK????? What are you doing???

On my PC it also works OK untill I run the evaluation tools

Did you run the evaluation tool?

The answers are always correct

[oldchippy]

Yes, I see the problem now????

Any clues here?

http://www.cpearson.com/excel/weeknum.htm


Or here?

http://www.rondebruin.nl/weeknumber.htm

[bevort]

If I use the following formula

Please Login or Register  to view this content.

Where B1 contains:

Please Login or Register  to view this content.

and thus returns 52 for this year

The formula works without any problems


Why is the combination of WEEKDAY with WEEKNUM in one IF() troubling the evaluation tool and in my case the customer also

[oldchippy]

If you run the evaluation tool on just on the first formula, an #N/A comes up, is that the problem?

[bevort]

A first fast test seems to work with the simple solution from Ron de Bruin
I'll test this even more and report back

[daddylonglegs]

hello bevort,

there are ways to avoid using WEEKNUM and replace with other formulas but I don't really see the logic of your formula. How do you define the number of weeks in a year, exactly? If you are using WEEKNUM why is the number of weeks not always the WEEKNUM of the last day of the year?

I note that some years, e.g. 2000 your formula will return 54, is that correct?

[oldchippy]

Does this help at all?

[bevort]

hello bevort,

there are ways to avoid using WEEKNUM and replace with other formulas but I don't really see the logic of your formula. How do you define the number of weeks in a year, exactly? If you are using WEEKNUM why is the number of weeks not always the WEEKNUM of the last day of the year?

I note that some years, e.g. 2000 your formula will return 54, is that correct?

The corrert number of the weeks in a year depends on when a week starts and when it end in the year.

I my case it is importand to know if we have 52 (as normal) or 53 weeks for budgetting reasons.

I my case a simple solution, like I hoped WEEKNUW would give me, calculates the last day number of the year. If it is less then 3 (scale from 0 to 6) then we do not caluclate that week as a week for this year So the answer will be 52. If the number returned is 3 or more we take this as a week and the answer will be 53. By using WEEKNUM even this will work in your case when it returns 54 what will result in 53 weeks as biing the correct answer.


The links above from OldChap give a very good description of the problem
I hoped that internal functions like WEEKDAY and WEEKNUM would solve my problem but it seems that Microsoft ones again did not succeed in combining these solutions.

I now am testing the solution I mentioned above

[bevort]

Does this help at all?

Not quite, sorry.

I, as I explaned just a few minutes ago, need to know the last day of the week.

I now use this one

Please Login or Register  to view this content.

This one gives me the daynumber of the last day of the week
If it is greater or equal then 4 it returns the result of the first formule

Please Login or Register  to view this content.

whish will be 53 otherwise I do not add an extra 1 to the result and end up with 52

The evaluation tool dos not stumple apoon errors.
Next step is to send it to my customer for testing



I still do not understand why opening the book the first time gives him correct answers and when copying the book for ie backup gives that error #name?
but the formula above seems to go around the problem

But thanx all for the help

[daddylonglegs]

I don't think it will be appropriate for you to use WEEKNUM because the definition of week numbers is not the same as yours. If you are using the ISO week number standard, whereby the first week of each year starts on the first Monday on or after 29th December then this formula should give you the number of weeks in the year

=INT((373-(DAY(DATE(year;2;29))=1)-WEEKDAY(DATE(year;12;28),2))/7)