I get 5 for 29th May. I do get 4 for 31Jul, but I don't understand why you
think it is 5.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Rick" wrote in message
...
I have changed B37 to B5 as that is the first cell of the date column.
The formula gives me 5 for every month.
Please can you help me further.

Please see examples of correct 4 or 5 week months. All for 2006
Month 1 3 apr to 30 apr
Month 2 1 may to 28 may
Month 3 29 may to 2 jul (5 week)
Month 4 3 jul to 30 jul
Month 5 31 jul to 3 sep (5 week)

Thanking you in anticipation
--
Big Rick

"Bob Phillips" wrote:

=4+(DATE(YEAR(B37+28),MONTH(B37+28)+1,0)-(B37+28)3)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Rick" wrote in message
...
In my current timesheets, I have to manually decide wether each month
is 4
or
5 week month. I simply do this by putting a 4 or 5 in $A$44.
The formula I have in the date cells (B37:B43) is
=IF($A$44=4,"---",$B$5+28 =IF($A$44=4,"---",$B$5+29 etc

The criteria for wether it is a 4 or 5 week month is as follows.
e.g. 1
B37 = 1 May 06. Therefore B35 will be 28 May 06.
It is then only 3 days till the actual end of month making this a 4
week
month.

e.g. 2
b37 = 29 May 06. Therefore B35 will be 25 June 06.
It is then 5 days till the actual end of month making this a 5 week
month.

The actual criteria is 3 or less days = 4 week month or 4 or more days
= 5
week month. Is it possible to put a formula in place to make this
automated.
Hope you can understand this explanation.

Thanking you in anticipation.
--
Big Rick

Month 1 3 apr - 30 apr = 0 days to end of Apr. end of wk 4 = 30th apr <= 3)
Month 2 1 may - 28 may = 3 days to end of May. end of week 4 = 28 may (<=3)
Month 3 29 may - 2 jul = 5 days till end of Jun. end of week 4 = 25 Jun (=4)
Month 4 3 jul - 30 jul = 1 day till end of Jul. end of wk 4 = 30 jul (<=3)
Month 5 31 jul to 3 sep = 4 days till end of Aug. end of wk 4 = 27 jul (=4)

Therefore if it is 3 days or less to end of month it becomes a 4 week month
and 4 days or more becomes a 5 week month.

Hoping that this explains further
--
Big Rick


"Bob Phillips" wrote:

I get 5 for 29th May. I do get 4 for 31Jul, but I don't understand why you
think it is 5.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Rick" wrote in message
...
I have changed B37 to B5 as that is the first cell of the date column.
The formula gives me 5 for every month.
Please can you help me further.

Please see examples of correct 4 or 5 week months. All for 2006
Month 1 3 apr to 30 apr
Month 2 1 may to 28 may
Month 3 29 may to 2 jul (5 week)
Month 4 3 jul to 30 jul
Month 5 31 jul to 3 sep (5 week)

Thanking you in anticipation
--
Big Rick

"Bob Phillips" wrote:

=4+(DATE(YEAR(B37+28),MONTH(B37+28)+1,0)-(B37+28)3)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Rick" wrote in message
...
In my current timesheets, I have to manually decide wether each month
is 4
or
5 week month. I simply do this by putting a 4 or 5 in $A$44.
The formula I have in the date cells (B37:B43) is
=IF($A$44=4,"---",$B$5+28 =IF($A$44=4,"---",$B$5+29 etc

The criteria for wether it is a 4 or 5 week month is as follows.
e.g. 1
B37 = 1 May 06. Therefore B35 will be 28 May 06.
It is then only 3 days till the actual end of month making this a 4
week
month.

e.g. 2
b37 = 29 May 06. Therefore B35 will be 25 June 06.
It is then 5 days till the actual end of month making this a 5 week
month.

The actual criteria is 3 or less days = 4 week month or 4 or more days
= 5
week month. Is it possible to put a formula in place to make this
automated.
Hope you can understand this explanation.

Thanking you in anticipation.
--
Big Rick

By my calculation, 31st July + 28 = 28th Aug, which leaves 3 days to end of
Aug = 4.

Are you getting 5 for 29 May yet with my formula?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Rick" wrote in message
...
Month 1 3 apr - 30 apr = 0 days to end of Apr. end of wk 4 = 30th apr <=
3)
Month 2 1 may - 28 may = 3 days to end of May. end of week 4 = 28 may
(<=3)
Month 3 29 may - 2 jul = 5 days till end of Jun. end of week 4 = 25 Jun
(=4)
Month 4 3 jul - 30 jul = 1 day till end of Jul. end of wk 4 = 30 jul (<=3)
Month 5 31 jul to 3 sep = 4 days till end of Aug. end of wk 4 = 27 jul
(=4)

Therefore if it is 3 days or less to end of month it becomes a 4 week
month
and 4 days or more becomes a 5 week month.

Hoping that this explains further
--
Big Rick


"Bob Phillips" wrote:

I get 5 for 29th May. I do get 4 for 31Jul, but I don't understand why
you
think it is 5.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Rick" wrote in message
...
I have changed B37 to B5 as that is the first cell of the date column.
The formula gives me 5 for every month.
Please can you help me further.

Please see examples of correct 4 or 5 week months. All for 2006
Month 1 3 apr to 30 apr
Month 2 1 may to 28 may
Month 3 29 may to 2 jul (5 week)
Month 4 3 jul to 30 jul
Month 5 31 jul to 3 sep (5 week)

Thanking you in anticipation
--
Big Rick

"Bob Phillips" wrote:

=4+(DATE(YEAR(B37+28),MONTH(B37+28)+1,0)-(B37+28)3)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Rick" wrote in message
...
In my current timesheets, I have to manually decide wether each
month
is 4
or
5 week month. I simply do this by putting a 4 or 5 in $A$44.
The formula I have in the date cells (B37:B43) is
=IF($A$44=4,"---",$B$5+28 =IF($A$44=4,"---",$B$5+29 etc

The criteria for wether it is a 4 or 5 week month is as follows.
e.g. 1
B37 = 1 May 06. Therefore B35 will be 28 May 06.
It is then only 3 days till the actual end of month making this a
4
week
month.

e.g. 2
b37 = 29 May 06. Therefore B35 will be 25 June 06.
It is then 5 days till the actual end of month making this a 5
week
month.

The actual criteria is 3 or less days = 4 week month or 4 or more
days
= 5
week month. Is it possible to put a formula in place to make this
automated.
Hope you can understand this explanation.

Thanking you in anticipation.
--
Big Rick

The way it works on August is as follows
wk 1 mon to sun = 31 july - 6 aug
wk 2 mon to sun = 7 aug - 13 aug
wk 3 mon to sun = 14 aug - 20 aug
wk 4 mon to sun = 21 aug - 27 - aug
wk 5 = mon 28 aug, tues 29 aug, wed 30 aug, thurs 31 aug, fri 1 sep, sat 2
sep, sun 3 sep.
This demonstrates that there are 4 days till the end of the month.
I put your formula in for the first 3 months only and it gave me 5 for each
one.

Hope this can help you now.
--
Big Rick


"Bob Phillips" wrote:

By my calculation, 31st July + 28 = 28th Aug, which leaves 3 days to end of
Aug = 4.

Are you getting 5 for 29 May yet with my formula?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Rick" wrote in message
...
Month 1 3 apr - 30 apr = 0 days to end of Apr. end of wk 4 = 30th apr <=
3)
Month 2 1 may - 28 may = 3 days to end of May. end of week 4 = 28 may
(<=3)
Month 3 29 may - 2 jul = 5 days till end of Jun. end of week 4 = 25 Jun
(=4)
Month 4 3 jul - 30 jul = 1 day till end of Jul. end of wk 4 = 30 jul (<=3)
Month 5 31 jul to 3 sep = 4 days till end of Aug. end of wk 4 = 27 jul
(=4)

Therefore if it is 3 days or less to end of month it becomes a 4 week
month
and 4 days or more becomes a 5 week month.

Hoping that this explains further
--
Big Rick


"Bob Phillips" wrote:

I get 5 for 29th May. I do get 4 for 31Jul, but I don't understand why
you
think it is 5.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Rick" wrote in message
...
I have changed B37 to B5 as that is the first cell of the date column.
The formula gives me 5 for every month.
Please can you help me further.

Please see examples of correct 4 or 5 week months. All for 2006
Month 1 3 apr to 30 apr
Month 2 1 may to 28 may
Month 3 29 may to 2 jul (5 week)
Month 4 3 jul to 30 jul
Month 5 31 jul to 3 sep (5 week)

Thanking you in anticipation
--
Big Rick

"Bob Phillips" wrote:

=4+(DATE(YEAR(B37+28),MONTH(B37+28)+1,0)-(B37+28)3)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Rick" wrote in message
...
In my current timesheets, I have to manually decide wether each
month
is 4
or
5 week month. I simply do this by putting a 4 or 5 in $A$44.
The formula I have in the date cells (B37:B43) is
=IF($A$44=4,"---",$B$5+28 =IF($A$44=4,"---",$B$5+29 etc

The criteria for wether it is a 4 or 5 week month is as follows.
e.g. 1
B37 = 1 May 06. Therefore B35 will be 28 May 06.
It is then only 3 days till the actual end of month making this a
4
week
month.

e.g. 2
b37 = 29 May 06. Therefore B35 will be 25 June 06.
It is then 5 days till the actual end of month making this a 5
week
month.

The actual criteria is 3 or less days = 4 week month or 4 or more
days
= 5
week month. Is it possible to put a formula in place to make this
automated.
Hope you can understand this explanation.

Thanking you in anticipation.
--
Big Rick

Try this then

=4+(DATE(YEAR(B37+27),MONTH(B37+27)+1,0)-(B37+27)3)


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Rick" wrote in message
...
The way it works on August is as follows
wk 1 mon to sun = 31 july - 6 aug
wk 2 mon to sun = 7 aug - 13 aug
wk 3 mon to sun = 14 aug - 20 aug
wk 4 mon to sun = 21 aug - 27 - aug
wk 5 = mon 28 aug, tues 29 aug, wed 30 aug, thurs 31 aug, fri 1 sep, sat 2
sep, sun 3 sep.
This demonstrates that there are 4 days till the end of the month.
I put your formula in for the first 3 months only and it gave me 5 for
each
one.

Hope this can help you now.
--
Big Rick


"Bob Phillips" wrote:

By my calculation, 31st July + 28 = 28th Aug, which leaves 3 days to end
of
Aug = 4.

Are you getting 5 for 29 May yet with my formula?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Rick" wrote in message
...
Month 1 3 apr - 30 apr = 0 days to end of Apr. end of wk 4 = 30th apr
<=
3)
Month 2 1 may - 28 may = 3 days to end of May. end of week 4 = 28 may
(<=3)
Month 3 29 may - 2 jul = 5 days till end of Jun. end of week 4 = 25
Jun
(=4)
Month 4 3 jul - 30 jul = 1 day till end of Jul. end of wk 4 = 30 jul
(<=3)
Month 5 31 jul to 3 sep = 4 days till end of Aug. end of wk 4 = 27 jul
(=4)

Therefore if it is 3 days or less to end of month it becomes a 4 week
month
and 4 days or more becomes a 5 week month.

Hoping that this explains further
--
Big Rick


"Bob Phillips" wrote:

I get 5 for 29th May. I do get 4 for 31Jul, but I don't understand
why
you
think it is 5.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Rick" wrote in message
...
I have changed B37 to B5 as that is the first cell of the date
column.
The formula gives me 5 for every month.
Please can you help me further.

Please see examples of correct 4 or 5 week months. All for 2006
Month 1 3 apr to 30 apr
Month 2 1 may to 28 may
Month 3 29 may to 2 jul (5 week)
Month 4 3 jul to 30 jul
Month 5 31 jul to 3 sep (5 week)

Thanking you in anticipation
--
Big Rick

"Bob Phillips" wrote:

=4+(DATE(YEAR(B37+28),MONTH(B37+28)+1,0)-(B37+28)3)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Rick" wrote in message
...
In my current timesheets, I have to manually decide wether
each
month
is 4
or
5 week month. I simply do this by putting a 4 or 5 in $A$44.
The formula I have in the date cells (B37:B43) is
=IF($A$44=4,"---",$B$5+28 =IF($A$44=4,"---",$B$5+29
etc

The criteria for wether it is a 4 or 5 week month is as
follows.
e.g. 1
B37 = 1 May 06. Therefore B35 will be 28 May 06.
It is then only 3 days till the actual end of month making
this a
4
week
month.

e.g. 2
b37 = 29 May 06. Therefore B35 will be 25 June 06.
It is then 5 days till the actual end of month making this a 5
week
month.

The actual criteria is 3 or less days = 4 week month or 4 or
more
days
= 5
week month. Is it possible to put a formula in place to make
this
automated.
Hope you can understand this explanation.

Thanking you in anticipation.
--
Big Rick

Very very close, but not quite.
This works fine for the first 5 months
3 apr - 30 apr
1 may - 28 may
29 may - 2 jul
3 jul - 30 jul
31 jul - 3 aug

but falls down on the next month which should be 3 aug - 1 sep.
I assume that that as the end of the month has already passed it is
calculating a 5 instead of a 4.
If I plead and beg, please could you put a condition in the formula that
states that if the last day of the 4th week is lets say anything between the
1st and the 7th, to make it a 4 week month.

Ok. here goes.
pleeeeeeeeeeeeeeese.
--
Big Rick


"Bob Phillips" wrote:

Try this then

=4+(DATE(YEAR(B37+27),MONTH(B37+27)+1,0)-(B37+27)3)


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Rick" wrote in message
...
The way it works on August is as follows
wk 1 mon to sun = 31 july - 6 aug
wk 2 mon to sun = 7 aug - 13 aug
wk 3 mon to sun = 14 aug - 20 aug
wk 4 mon to sun = 21 aug - 27 - aug
wk 5 = mon 28 aug, tues 29 aug, wed 30 aug, thurs 31 aug, fri 1 sep, sat 2
sep, sun 3 sep.
This demonstrates that there are 4 days till the end of the month.
I put your formula in for the first 3 months only and it gave me 5 for
each
one.

Hope this can help you now.
--
Big Rick


"Bob Phillips" wrote:

By my calculation, 31st July + 28 = 28th Aug, which leaves 3 days to end
of
Aug = 4.

Are you getting 5 for 29 May yet with my formula?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Rick" wrote in message
...
Month 1 3 apr - 30 apr = 0 days to end of Apr. end of wk 4 = 30th apr
<=
3)
Month 2 1 may - 28 may = 3 days to end of May. end of week 4 = 28 may
(<=3)
Month 3 29 may - 2 jul = 5 days till end of Jun. end of week 4 = 25
Jun
(=4)
Month 4 3 jul - 30 jul = 1 day till end of Jul. end of wk 4 = 30 jul
(<=3)
Month 5 31 jul to 3 sep = 4 days till end of Aug. end of wk 4 = 27 jul
(=4)

Therefore if it is 3 days or less to end of month it becomes a 4 week
month
and 4 days or more becomes a 5 week month.

Hoping that this explains further
--
Big Rick


"Bob Phillips" wrote:

I get 5 for 29th May. I do get 4 for 31Jul, but I don't understand
why
you
think it is 5.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Rick" wrote in message
...
I have changed B37 to B5 as that is the first cell of the date
column.
The formula gives me 5 for every month.
Please can you help me further.

Please see examples of correct 4 or 5 week months. All for 2006
Month 1 3 apr to 30 apr
Month 2 1 may to 28 may
Month 3 29 may to 2 jul (5 week)
Month 4 3 jul to 30 jul
Month 5 31 jul to 3 sep (5 week)

Thanking you in anticipation
--
Big Rick

"Bob Phillips" wrote:

=4+(DATE(YEAR(B37+28),MONTH(B37+28)+1,0)-(B37+28)3)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Rick" wrote in message
...
In my current timesheets, I have to manually decide wether
each
month
is 4
or
5 week month. I simply do this by putting a 4 or 5 in $A$44.
The formula I have in the date cells (B37:B43) is
=IF($A$44=4,"---",$B$5+28 =IF($A$44=4,"---",$B$5+29
etc

The criteria for wether it is a 4 or 5 week month is as
follows.
e.g. 1
B37 = 1 May 06. Therefore B35 will be 28 May 06.
It is then only 3 days till the actual end of month making
this a
4
week
month.

e.g. 2
b37 = 29 May 06. Therefore B35 will be 25 June 06.
It is then 5 days till the actual end of month making this a 5
week
month.

The actual criteria is 3 or less days = 4 week month or 4 or
more
days
= 5
week month. Is it possible to put a formula in place to make
this
automated.
Hope you can understand this explanation.

Thanking you in anticipation.
--
Big Rick

You've lost me. Where does 31-Jul to 3 Aug come into it, that is 4 days not
4 weeks.

I think the problem is that you are trying to come up with a formula that
determines whether the next start date is 4 or 5 weeks hence, but you have
clouded it with all that 4 week/5 week stuff.

Assuming you have a first date in A1, I think this will predict the rest

=A1+28+(AND(DATE(YEAR(A1),MONTH(A1)+2,0)-(A1+28)<8,DATE(YEAR(A1),MONTH(A1)+2
,0)-(A1+28)3))*7

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Rick" wrote in message
...
Very very close, but not quite.
This works fine for the first 5 months
3 apr - 30 apr
1 may - 28 may
29 may - 2 jul
3 jul - 30 jul
31 jul - 3 aug

but falls down on the next month which should be 3 aug - 1 sep.
I assume that that as the end of the month has already passed it is
calculating a 5 instead of a 4.
If I plead and beg, please could you put a condition in the formula that
states that if the last day of the 4th week is lets say anything between
the
1st and the 7th, to make it a 4 week month.

Ok. here goes.
pleeeeeeeeeeeeeeese.
--
Big Rick


"Bob Phillips" wrote:

Try this then

=4+(DATE(YEAR(B37+27),MONTH(B37+27)+1,0)-(B37+27)3)


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Rick" wrote in message
...
The way it works on August is as follows
wk 1 mon to sun = 31 july - 6 aug
wk 2 mon to sun = 7 aug - 13 aug
wk 3 mon to sun = 14 aug - 20 aug
wk 4 mon to sun = 21 aug - 27 - aug
wk 5 = mon 28 aug, tues 29 aug, wed 30 aug, thurs 31 aug, fri 1 sep,
sat 2
sep, sun 3 sep.
This demonstrates that there are 4 days till the end of the month.
I put your formula in for the first 3 months only and it gave me 5 for
each
one.

Hope this can help you now.
--
Big Rick


"Bob Phillips" wrote:

By my calculation, 31st July + 28 = 28th Aug, which leaves 3 days to
end
of
Aug = 4.

Are you getting 5 for 29 May yet with my formula?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Rick" wrote in message
...
Month 1 3 apr - 30 apr = 0 days to end of Apr. end of wk 4 = 30th
apr
<=
3)
Month 2 1 may - 28 may = 3 days to end of May. end of week 4 = 28
may
(<=3)
Month 3 29 may - 2 jul = 5 days till end of Jun. end of week 4 =
25
Jun
(=4)
Month 4 3 jul - 30 jul = 1 day till end of Jul. end of wk 4 = 30
jul
(<=3)
Month 5 31 jul to 3 sep = 4 days till end of Aug. end of wk 4 = 27
jul
(=4)

Therefore if it is 3 days or less to end of month it becomes a 4
week
month
and 4 days or more becomes a 5 week month.

Hoping that this explains further
--
Big Rick


"Bob Phillips" wrote:

I get 5 for 29th May. I do get 4 for 31Jul, but I don't
understand
why
you
think it is 5.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Rick" wrote in message
...
I have changed B37 to B5 as that is the first cell of the date
column.
The formula gives me 5 for every month.
Please can you help me further.

Please see examples of correct 4 or 5 week months. All for
2006
Month 1 3 apr to 30 apr
Month 2 1 may to 28 may
Month 3 29 may to 2 jul (5 week)
Month 4 3 jul to 30 jul
Month 5 31 jul to 3 sep (5 week)

Thanking you in anticipation
--
Big Rick

"Bob Phillips" wrote:

=4+(DATE(YEAR(B37+28),MONTH(B37+28)+1,0)-(B37+28)3)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Rick" wrote in
message
...
In my current timesheets, I have to manually decide wether
each
month
is 4
or
5 week month. I simply do this by putting a 4 or 5 in
$A$44.
The formula I have in the date cells (B37:B43) is
=IF($A$44=4,"---",$B$5+28 =IF($A$44=4,"---",$B$5+29
etc

The criteria for wether it is a 4 or 5 week month is as
follows.
e.g. 1
B37 = 1 May 06. Therefore B35 will be 28 May 06.
It is then only 3 days till the actual end of month making
this a
4
week
month.

e.g. 2
b37 = 29 May 06. Therefore B35 will be 25 June 06.
It is then 5 days till the actual end of month making this
a 5
week
month.

The actual criteria is 3 or less days = 4 week month or 4
or
more
days
= 5
week month. Is it possible to put a formula in place to
make
this
automated.
Hope you can understand this explanation.

Thanking you in anticipation.
--
Big Rick