4 and 5 week months

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
=4+(DATE(YEAR(B37+28),MONTH(B37+28)+1,0)-(B37+28)>3)

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
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
 
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)
 
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
 
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)
 
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.
 
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)
 
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.
 
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)
 
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.
 
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.
Click to expand...

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.
 
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.
Click to expand...


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.
 
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.
Click to expand...

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
 
Back
Top