Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help With Calculating Weeks
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) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help With Calculating Weeks
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help With Calculating Weeks
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) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help With Calculating Weeks
Bob
Many thanks, that works fine. Much appreciated and I still keep my hair Peter ------------------------------------------------------- "Bob Phillips" wrote in message ... 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating number of weeks between dates? | Excel Worksheet Functions | |||
calculating number of weeks between two set dates | Excel Worksheet Functions | |||
calculating with weeks | Excel Discussion (Misc queries) | |||
Formula for calculating the last four weeks | Excel Discussion (Misc queries) | |||
calculating weeks worked | Excel Worksheet Functions |