Home |
Search |
Today's Posts |
#12
![]() |
|||
|
|||
![]()
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
4 or 5 week months? | Excel Discussion (Misc queries) | |||
Why "datedif" function results sometimes negative numbers? | Excel Worksheet Functions | |||
user defined function | Excel Worksheet Functions | |||
Line or bar graphs for tracking stocks profit and loss. | Charts and Charting in Excel | |||
Weekly data allocated to months | Excel Worksheet Functions |