Help With Calculating Weeks

[Peter From The UK]

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)

 

[Carim]

Hi,

Have a look at Chip's excellent site ...

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

HTH

 

[Bob Phillips]

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)



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)

 

[Peter From The UK]

Bob

Many thanks, that works fine.

Much appreciated and I still keep my hair

Peter
-------------------------------------------------------

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)



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)