Forum Home
Press F1
Thread ID: 33437	2003-05-16 03:49:00	Excel	appanna (3735)	Press F1

Post ID	Timestamp	Content	User
144685	2003-05-16 03:49:00	I have to calculate the number of months between a date in 2002 and 30/4/2003. If the date in 2002 is before the 15th say 13/8/2002 then the whole of August is to be taken and if it is after the 15th say 20/8/2002 then the month of August is to be ignored and the number of months to be calculated would be from September 1 to 30/4/2003. I hope I have explained it properly and clearly. Any help with the formula will be appreciated. Thanks	appanna (3735)
144686	2003-05-16 03:54:00	I have read somewhere that there is an undocumented function which will do part of what you want: datediff I think.. Forgot what it actually was.	somebody (208)
144687	2003-05-16 04:37:00	This formula is rough and ready, in that it assumes the mid point is always the 15th of the month. =IF(DAY(startdate_cell)>=15,DATEDIF(startdate_cell,enddate_cell,"M"),DATEDIF(startdate_cell,enddate_cell,"M")-1) HTH	Russell D (18)
144688	2003-05-16 08:13:00	Hi appanna, Try the following, which will give you what you literally asked for. Enter the date(s) in A1 (or in column A) In B1 enter the formula =17-IF(DAY(A1)<15,MONTH(A1),MONTH(A1)+1) If you have a number of date entries in the A column then fill down the B column correspondingly.	rugila (214)
144689	2003-05-16 21:38:00	Thanks Ruquila that works but I have had a few more conditions come through. If the year is less than 2002 then it should return 12 months; if the month in 2002 is less than 5 it should return 12; else your formula. the day criteria remaining same as earlier. If the year is 2003, the month being always January, the months to return will always be 4. Would there be lot of changes to the formula you gave? Cheers Appanna	appanna (3735)
144690	2003-05-16 23:21:00	Sticking with DATEDIF :D, if you put the date 30/4/2003 in cell D1 and your other dates in A1 downwards, the following formula will give what you want - with the exception that the requirement for January = 4 is contradictory with the first post conditions so that days 15-31 December 2002 will come out as 3. =IF(AND(YEAR(A1)=2003,MONTH(A1)=1),DATEDIF(A1,$D$1 ,"M")+1,IF(DATEDIF(A1,$D$1,"M")>=12,12,IF(DAY(A1)<=15,DATEDIF(A1,$D$1,"M"),DATEDIF(A1,$D$1,"m")-1)))	Russell D (18)
144691	2003-05-17 08:26:00	Use =IF(A1<37407,12,IF(DATE(YEAR(A1),MONTH(A1),31)=37652,4,17-IF(DAY(A1)<15,MONTH(A1),MONTH(A1)+1))) which will again give you exactly what you asked, no more and no less. But note that: (a) you didn't specify how you wanted the 15th day of the months June to Dec 2002 handled, but you should be able to adjust the formula accordingly (b) You didn't say what you wanted done with dates after Jan 2003 so the above formula doesn't give any meaningful result for these (reason being only that no meaningful result was requested). (c) You are starting to ask fairly complicated things from Excel's essentially simple formula structures, and it might be better if you looked at writing some programming code if you want to get even more complicated.	rugila (214)
144692	2003-05-17 08:31:00	That last post of mine had an obvious spacing error in IF(DAY(A 1) That seems to be a bug in PressF1's display. The formula Is copied and pasted was fine.	rugila (214)
1