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!

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!

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!

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!

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!

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!

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!

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!

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!

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!