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

Slight mistake in explantiion
In eg. 1 and eg.2 please replace B37 with B5
--
Big Rick


"Big Rick" wrote:

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

=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

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

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

Ever so sorry, but that was a typo. It should of said 31 jul - 3 sep.
This is a list of that I want the formula to generate.
The last formula will not work for my needs as this is generating a date
instead of the number 4 or 5. ( I need these numbers as other formulas rely
on it)
All for 2006
3 apr - 30 apr
1 may - 28 may
29 may - 2 jul 5 week
3 jul - 30 jul
31 jul - 3 sep 5 week
4 sep - 1 oct (problem here)
2 oct - 29 oct
30 oct - 3 dec 5 week
4 dec - 31 dec
1 jan - 28 jan
29 jan - 25 feb
26 feb - 1 apr 5 week
As you can see the problem is that the end of month has already passed.

Please could you give it one last try for me. I would be ever so gratful
Alternatively, would you mind if i emailed the workbook to you so you could
have a look.

I truly appreciate all your time and effort.
--
Big Rick


"Bob Phillips" wrote:

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

On Thu, 3 Nov 2005 08:20:09 -0800, "Big Rick"
wrote:

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.

Try this:

A1: First Date; e.b. 3 Apr 2006

Start of each subsequent period:
A2: =A1+28+7*((EOMONTH(A1+27,0)-A1-27)3)

Copy/drag down as necessary.

To get the ENDing date of a period,

B1: =B2-1
Copy/Drag down

----------------
If the EOMONTH function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.
--------------------


--ron

On Sat, 05 Nov 2005 10:15:59 -0500, Ron Rosenfeld
wrote:

On Thu, 3 Nov 2005 08:20:09 -0800, "Big Rick"
wrote:

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.

Try this:

A1: First Date; e.b. 3 Apr 2006

Start of each subsequent period:
A2: =A1+28+7*((EOMONTH(A1+27,0)-A1-27)3)

Copy/drag down as necessary.

To get the ENDing date of a period,

B1: =B2-1
Copy/Drag down

----------------
If the EOMONTH function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.
--------------------


--ron


Minor Correction:

A2: =A1+28+7*((EOMONTH(A1+23,0)-A1-27)3)


--ron

Dear Ron
Sorry it took a while to reply.
Your formula works like a dream.
In order for it to generate a 4 or 5, I have simply incorporated it into an
IF statement matching the value with another cell. I was begining to despair.
I cannot thank you enough.

Dear Bob
I would like to thank you as well for time time and patience.
I hope I have not put you off helping me in the future.

I hope you both have a very Merry Christmas.
--
Big Rick


"Ron Rosenfeld" wrote:

On Sat, 05 Nov 2005 10:15:59 -0500, Ron Rosenfeld
wrote:

On Thu, 3 Nov 2005 08:20:09 -0800, "Big Rick"
wrote:

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.

Try this:

A1: First Date; e.b. 3 Apr 2006

Start of each subsequent period:
A2: =A1+28+7*((EOMONTH(A1+27,0)-A1-27)3)


--ron


Minor Correction:

A2: =A1+28+7*((EOMONTH(A1+23,0)-A1-27)3)

On Sun, 6 Nov 2005 16:12:59 -0800, "Big Rick"
wrote:

Dear Ron
Sorry it took a while to reply.
Your formula works like a dream.
In order for it to generate a 4 or 5, I have simply incorporated it into an
IF statement matching the value with another cell. I was begining to despair.
I cannot thank you enough.

Glad it worked for you. Thank you for the feedback.

By the way, one method of counting the number of weeks between two dates,
assuming your worksheet is set up as I described:

=(A2-A1)/7

will give the number of weeks form A1 to A2; and you can copy/drag the formula
down.


--ron