4 and 5 week months
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
|