Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Calculation Problem
I have several jobs each with a different start date, and frequency period before a job becomes due again. I need to automatically calculate whether any jobs would occur the week of any given date (always a Monday). eg Job Start Date 23/01/06 frequency 9 weeks, If I needed to know if the job fell on the week commencing the 14/05/07, what formula could I use? Thanks for any help! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Calculation Problem
I don't see how 23/01/06 + 9 weeks gives 14/05/07
If you want to add the number of weeks (in B1) to a date in A1 then you can use the formula =A1+B1*7 "Homer" wrote: I have several jobs each with a different start date, and frequency period before a job becomes due again. I need to automatically calculate whether any jobs would occur the week of any given date (always a Monday). eg Job Start Date 23/01/06 frequency 9 weeks, If I needed to know if the job fell on the week commencing the 14/05/07, what formula could I use? Thanks for any help! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Calculation Problem
One day is 1 in Excel so you can just add 9 weeks
if you start date is in A1, thenm =A1+(9*7) and format the result as a date Regards, Peo Sjoblom Homer wrote: I have several jobs each with a different start date, and frequency period before a job becomes due again. I need to automatically calculate whether any jobs would occur the week of any given date (always a Monday). eg Job Start Date 23/01/06 frequency 9 weeks, If I needed to know if the job fell on the week commencing the 14/05/07, what formula could I use? Thanks for any help! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Calculation Problem
Hi
If you wanted a grid to see when jobs were due, you could use the following. Assuming Date of job is in column A, Job number in Column B and Frequency (weeks) is in column C. In D1 enter =INT(TODAY()/7)*7+2 This will give the Monday of the Current week In E1 enter =D1+7 and copy across as far as required In D2 enter =IF($A2+63<$D$1,"",IF(AND($A2+7*$C2=D$1,$A2+7*$C2 <=E$1),$B2,"")) and copy across for the same number of columns as you have dates entered in row 1. Copy this row of formulae down as far as required. Now the Job number will show up under the appropriate column for you, and will be constantly adjusted as each starting week number gets adjusted through the formula in D1. -- Regards Roger Govier "Homer" wrote in message ... I have several jobs each with a different start date, and frequency period before a job becomes due again. I need to automatically calculate whether any jobs would occur the week of any given date (always a Monday). eg Job Start Date 23/01/06 frequency 9 weeks, If I needed to know if the job fell on the week commencing the 14/05/07, what formula could I use? Thanks for any help! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Calculation Problem
Apologies
The formula in D2 should be =IF($A2+7*$C2<$D$1,"",IF(AND($A2+7*$C2=D$1,$A2+7* $C2<=E$1),$B2,"")) -- Regards Roger Govier "Roger Govier" wrote in message ... Hi If you wanted a grid to see when jobs were due, you could use the following. Assuming Date of job is in column A, Job number in Column B and Frequency (weeks) is in column C. In D1 enter =INT(TODAY()/7)*7+2 This will give the Monday of the Current week In E1 enter =D1+7 and copy across as far as required In D2 enter =IF($A2+63<$D$1,"",IF(AND($A2+7*$C2=D$1,$A2+7*$C2 <=E$1),$B2,"")) and copy across for the same number of columns as you have dates entered in row 1. Copy this row of formulae down as far as required. Now the Job number will show up under the appropriate column for you, and will be constantly adjusted as each starting week number gets adjusted through the formula in D1. -- Regards Roger Govier "Homer" wrote in message ... I have several jobs each with a different start date, and frequency period before a job becomes due again. I need to automatically calculate whether any jobs would occur the week of any given date (always a Monday). eg Job Start Date 23/01/06 frequency 9 weeks, If I needed to know if the job fell on the week commencing the 14/05/07, what formula could I use? Thanks for any help! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Calculation Problem
Thanks Roger for understanding the problem and providing a solution!
Nick "Roger Govier" wrote in message ... Apologies The formula in D2 should be =IF($A2+7*$C2<$D$1,"",IF(AND($A2+7*$C2=D$1,$A2+7* $C2<=E$1),$B2,"")) -- Regards Roger Govier "Roger Govier" wrote in message ... Hi If you wanted a grid to see when jobs were due, you could use the following. Assuming Date of job is in column A, Job number in Column B and Frequency (weeks) is in column C. In D1 enter =INT(TODAY()/7)*7+2 This will give the Monday of the Current week In E1 enter =D1+7 and copy across as far as required In D2 enter =IF($A2+63<$D$1,"",IF(AND($A2+7*$C2=D$1,$A2+7*$C2 <=E$1),$B2,"")) and copy across for the same number of columns as you have dates entered in row 1. Copy this row of formulae down as far as required. Now the Job number will show up under the appropriate column for you, and will be constantly adjusted as each starting week number gets adjusted through the formula in D1. -- Regards Roger Govier "Homer" wrote in message ... I have several jobs each with a different start date, and frequency period before a job becomes due again. I need to automatically calculate whether any jobs would occur the week of any given date (always a Monday). eg Job Start Date 23/01/06 frequency 9 weeks, If I needed to know if the job fell on the week commencing the 14/05/07, what formula could I use? Thanks for any help! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Calculation Problem
Roger,
I've tried this over a longer period and I can only get the formula to work for the first occurance of each job. If the frequency is 4 wks for instance, I need the job number indicated every fourth week on the chart, as my chart goes several months forward. I can't see where your formula allows for this? Do you know a way of adding this calculation? Thanks, Nick "Roger Govier" wrote in message ... Apologies The formula in D2 should be =IF($A2+7*$C2<$D$1,"",IF(AND($A2+7*$C2=D$1,$A2+7* $C2<=E$1),$B2,"")) -- Regards Roger Govier "Roger Govier" wrote in message ... Hi If you wanted a grid to see when jobs were due, you could use the following. Assuming Date of job is in column A, Job number in Column B and Frequency (weeks) is in column C. In D1 enter =INT(TODAY()/7)*7+2 This will give the Monday of the Current week In E1 enter =D1+7 and copy across as far as required In D2 enter =IF($A2+63<$D$1,"",IF(AND($A2+7*$C2=D$1,$A2+7*$C2 <=E$1),$B2,"")) and copy across for the same number of columns as you have dates entered in row 1. Copy this row of formulae down as far as required. Now the Job number will show up under the appropriate column for you, and will be constantly adjusted as each starting week number gets adjusted through the formula in D1. -- Regards Roger Govier "Homer" wrote in message ... I have several jobs each with a different start date, and frequency period before a job becomes due again. I need to automatically calculate whether any jobs would occur the week of any given date (always a Monday). eg Job Start Date 23/01/06 frequency 9 weeks, If I needed to know if the job fell on the week commencing the 14/05/07, what formula could I use? Thanks for any help! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Calculation Problem
Hi Nick
If you have the Analysis Toolpak loaded, TollsAddinsAnalysis Toolpak then you could use the following formula in cell D2 copied across and down =IF(MOD((WEEKNUM(D$1)-WEEKNUM($A2))/$C2,1)=0,$B2,"") -- Regards Roger Govier "Homer" wrote in message ... Roger, I've tried this over a longer period and I can only get the formula to work for the first occurance of each job. If the frequency is 4 wks for instance, I need the job number indicated every fourth week on the chart, as my chart goes several months forward. I can't see where your formula allows for this? Do you know a way of adding this calculation? Thanks, Nick "Roger Govier" wrote in message ... Apologies The formula in D2 should be =IF($A2+7*$C2<$D$1,"",IF(AND($A2+7*$C2=D$1,$A2+7* $C2<=E$1),$B2,"")) -- Regards Roger Govier "Roger Govier" wrote in message ... Hi If you wanted a grid to see when jobs were due, you could use the following. Assuming Date of job is in column A, Job number in Column B and Frequency (weeks) is in column C. In D1 enter =INT(TODAY()/7)*7+2 This will give the Monday of the Current week In E1 enter =D1+7 and copy across as far as required In D2 enter =IF($A2+63<$D$1,"",IF(AND($A2+7*$C2=D$1,$A2+7*$C2 <=E$1),$B2,"")) and copy across for the same number of columns as you have dates entered in row 1. Copy this row of formulae down as far as required. Now the Job number will show up under the appropriate column for you, and will be constantly adjusted as each starting week number gets adjusted through the formula in D1. -- Regards Roger Govier "Homer" wrote in message ... I have several jobs each with a different start date, and frequency period before a job becomes due again. I need to automatically calculate whether any jobs would occur the week of any given date (always a Monday). eg Job Start Date 23/01/06 frequency 9 weeks, If I needed to know if the job fell on the week commencing the 14/05/07, what formula could I use? Thanks for any help! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Calculation Problem
Perfect! I tip my hat to your Excel abilities!
"Roger Govier" wrote in message ... Hi Nick If you have the Analysis Toolpak loaded, TollsAddinsAnalysis Toolpak then you could use the following formula in cell D2 copied across and down =IF(MOD((WEEKNUM(D$1)-WEEKNUM($A2))/$C2,1)=0,$B2,"") -- Regards Roger Govier "Homer" wrote in message ... Roger, I've tried this over a longer period and I can only get the formula to work for the first occurance of each job. If the frequency is 4 wks for instance, I need the job number indicated every fourth week on the chart, as my chart goes several months forward. I can't see where your formula allows for this? Do you know a way of adding this calculation? Thanks, Nick "Roger Govier" wrote in message ... Apologies The formula in D2 should be =IF($A2+7*$C2<$D$1,"",IF(AND($A2+7*$C2=D$1,$A2+7* $C2<=E$1),$B2,"")) -- Regards Roger Govier "Roger Govier" wrote in message ... Hi If you wanted a grid to see when jobs were due, you could use the following. Assuming Date of job is in column A, Job number in Column B and Frequency (weeks) is in column C. In D1 enter =INT(TODAY()/7)*7+2 This will give the Monday of the Current week In E1 enter =D1+7 and copy across as far as required In D2 enter =IF($A2+63<$D$1,"",IF(AND($A2+7*$C2=D$1,$A2+7*$C2 <=E$1),$B2,"")) and copy across for the same number of columns as you have dates entered in row 1. Copy this row of formulae down as far as required. Now the Job number will show up under the appropriate column for you, and will be constantly adjusted as each starting week number gets adjusted through the formula in D1. -- Regards Roger Govier "Homer" wrote in message ... I have several jobs each with a different start date, and frequency period before a job becomes due again. I need to automatically calculate whether any jobs would occur the week of any given date (always a Monday). eg Job Start Date 23/01/06 frequency 9 weeks, If I needed to know if the job fell on the week commencing the 14/05/07, what formula could I use? Thanks for any help! |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Calculation Problem
Hi Nick
Thanks for the feedback - glad it worked out for you. Happy New Year to you -- Regards Roger Govier "Homer" wrote in message ... Perfect! I tip my hat to your Excel abilities! "Roger Govier" wrote in message ... Hi Nick If you have the Analysis Toolpak loaded, TollsAddinsAnalysis Toolpak then you could use the following formula in cell D2 copied across and down =IF(MOD((WEEKNUM(D$1)-WEEKNUM($A2))/$C2,1)=0,$B2,"") -- Regards Roger Govier "Homer" wrote in message ... Roger, I've tried this over a longer period and I can only get the formula to work for the first occurance of each job. If the frequency is 4 wks for instance, I need the job number indicated every fourth week on the chart, as my chart goes several months forward. I can't see where your formula allows for this? Do you know a way of adding this calculation? Thanks, Nick "Roger Govier" wrote in message ... Apologies The formula in D2 should be =IF($A2+7*$C2<$D$1,"",IF(AND($A2+7*$C2=D$1,$A2+7* $C2<=E$1),$B2,"")) -- Regards Roger Govier "Roger Govier" wrote in message ... Hi If you wanted a grid to see when jobs were due, you could use the following. Assuming Date of job is in column A, Job number in Column B and Frequency (weeks) is in column C. In D1 enter =INT(TODAY()/7)*7+2 This will give the Monday of the Current week In E1 enter =D1+7 and copy across as far as required In D2 enter =IF($A2+63<$D$1,"",IF(AND($A2+7*$C2=D$1,$A2+7*$C2 <=E$1),$B2,"")) and copy across for the same number of columns as you have dates entered in row 1. Copy this row of formulae down as far as required. Now the Job number will show up under the appropriate column for you, and will be constantly adjusted as each starting week number gets adjusted through the formula in D1. -- Regards Roger Govier "Homer" wrote in message ... I have several jobs each with a different start date, and frequency period before a job becomes due again. I need to automatically calculate whether any jobs would occur the week of any given date (always a Monday). eg Job Start Date 23/01/06 frequency 9 weeks, If I needed to know if the job fell on the week commencing the 14/05/07, what formula could I use? Thanks for any help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mileage Claim Formula | New Users to Excel | |||
Tricky Date calculation: How to calculate a future date | Excel Discussion (Misc queries) | |||
Nett Date Calculation | Excel Worksheet Functions | |||
Date Calculation | Excel Discussion (Misc queries) | |||
Date calculation | Excel Worksheet Functions |