How about this

=WEEKNUM(NextTest-WEEKNUM(DATE(YEAR(NextTest),3,31)-WEEKDAY(DATE(YEAR(NextTest),3,31),2)+1,2)*7,2)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Peter From The UK" wrote in message ...
Hi.

I seem to have a problem. I have a very large Excel file with two sheets (each shed has aprox 3,000 rows) whereby the data entered is on the "Audit" sheet with the results on the "Reports" sheet. The Audit sheet contains test dates. I calculate the next test date from the previous test date which is called from the Audit sheet and from the next text date I am required to project the week number and the year of the next test. This is where a problem lies. The week number is not the standard week number, that is it is not week 1 in January. Our week 1 is week 52 in the UK tax year (last week in March). Is there a formulae which I can use to calculate these items.

Last Test =MAX(xxx:yyy) (as date)
Test Freq nn (as days)
Next Test =SUM(Last Test + Test Freq) (as date)
Week Number =WEEKNUM(Next Test,2)-41
Year Manual Entry

Part of the table looks like:

Last Test Test Freq Next Test Week Number Year

However, around about November, the week numbers start to show as a negative number, which, when you are reliant on accurate information for the tests and working to a plan, a negative number is not acceptable and I need to be able to see the planned work for the week number to save time

How can I do this please

Many thanks in advance for anyone who can shed some light or help with the problem

Peter (From the UK)