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

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