Date Calculation Problem

[Homer]

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!

 

[Peo Sjoblom]

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

 

[Roger Govier]

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.

 

[Roger Govier]

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,""))

 

[Homer]

Thanks Roger for understanding the problem and providing a solution!

Nick

 

[Homer]

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]

Hi Nick

If you have the Analysis Toolpak loaded, Tolls>Addins>Analysis 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,"")

 

[Homer]

Perfect! I tip my hat to your Excel abilities!

Hi Nick

If you have the Analysis Toolpak loaded, Tolls>Addins>Analysis 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,"")

 

[Roger Govier]

Hi Nick

Thanks for the feedback - glad it worked out for you.
Happy New Year to you