|
4 and 5 week months
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 and 5 week months
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 and 5 week months
=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 |
4 and 5 week months
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 |
4 and 5 week months
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 |
4 and 5 week months
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 |
4 and 5 week months
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 |
4 and 5 week months
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 |
4 and 5 week months
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 |
4 and 5 week months
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 |
4 and 5 week months
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 |
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 |
4 and 5 week months
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 |
4 and 5 week months
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 |
4 and 5 week months
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) |
4 and 5 week months
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 |
| All times are GMT +1. The time now is 08:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com