Number of months in date range ?

[SMARTII]

Re: Number of months in date range ?

YOU R DA MAN!!!!!!!!!!!!!!!
THANKS FER BEIN THERE!

It seems that this is where the problem is. There are roughly 365.25 days per year. That translates to:

?365.25/12
= 30.437500

So by using 30.44, we have:

?round_UP((jdate(dend) - jdate(dbgin))/30.44,0)
= 1197.000000

[G Gabriel]

Re: Number of months in date range ?

No can do:

dbgin={1/31/10}
dend={12/28/10}
?round_UP((jdate(dend) - jdate(dbgin))/30.44,0)
= 11 [COLOR="Red"]'wrong[/COLOR]
?12*(year(dend)-year(dbgin))+month(dend)-month(dbgin)+1
= 12 [COLOR="SeaGreen"]'correct[/COLOR]

[G Gabriel]

Re: Number of months in date range ?

Here is the explanation before you start spinning your head and wonder about e=mc2:
Steve is correct to use the average of 365.25/12 which accounts for leap years. But... what if you are calculating just for one year? Then your average is the correct one.
Which means.. neither is correct.

[MikeC]

Re: Number of months in date range ?

So if you are correct G about the correctness of 365.25 for leap year and 365 for non-leap year, then a simple (but long) if() statement to test for leap year would finish this thread would it not? Would have to use 366 for leap is all and 365 for regular year. Having a hard time figuring out all the subtle differences being made here--especially when sometimes 30.35 was used, sometimes 30.25 used, and all along it never made sense to me why either was used!! :)

Of course whether any of this accomplishes what Tom needs is perhaps of more importance!!!

[G Gabriel]

Re: Number of months in date range ?

So if you are correct G about the correctness of 365.25 for leap year and 365 for non-leap year, then a simple (but long) if() statement to test for leap year would finish this thread would it not?

No it wouldn't.
It is not just one for the short period and one for the long one.
If you average for a long period, say 100 years then using the average that accounts for leap years would work.
If you average for a short period, say a year or less, then the other calculation would work assuming it is not a leap year. If it is, then you have to use one that accounts for a leap year.
If you average for any other period, then you have to figure out how many leap years are in the period and add an extra day for each then calculate the average. Add to that a 7 years period could have 1 or 2 leap years. A 9 year period could have 2 or 3 leap years and so on.
Doable, but pretty awkward.

?365/12
= 30.4167
?366/12
= 30.5
?(365+365+365+366)/(4*12)
= 30.4375
?(365+365+365+366+365+365+365)/(7*12)
= 30.4285714285714

[SMARTII]

Re: Number of months in date range ?

Leapin years! Someone must take the blame for all this.
DAMN them Romans !!!!!!
ps. 30.25 wsa jsut a typo. :o

Here is the explanation before you start spinning your head and wonder about e=mc2:
Steve is correct to use the average of 365.25/12 which accounts for leap years. But... what if you are calculating just for one year? Then your average is the correct one.
Which means.. neither is correct.

[G Gabriel]

Re: Number of months in date range ?

Not so.
You should embrace their work of genius.
Having the same event, almost, occurring year after year.. it's a work of genius.
And if you want to see true work of genius, bordering on miracles, you should visit the temple of Sun in Egypt where they have an altar on one side then between the altar and where the worshipers would be there are a good number of walls built on angles to each others as if radiating from the center of a circle and converge at the center but never meet. If you are among the worshipers, the sun rays will pass across the altar through the gap between these walls shinning to the worshipers only one day a year, same day every year, which day they celebrated the sun! Unbelievable.

And you said what?
You want to average?
Go tell that to the Romans and the Pharos!

[SMARTII]

Re: Number of months in date range ?

Let's all are take ONE last stab for a definitive irrefutable elegent solution and satisfy Tom Cone Jr's problem:
Here's mine:

FUNCTION mnths_in AS N (dbgin AS D, dend AS D ) 
dim x as n 
dim xtra as n 
x = 1 
while addmonths(dbgin,x) <= dend 
    x = x + 1 
     
end while 
'-------- 
' Get the addl month for the Max Array needed: 
xtra = day(dbgin) - day(dend) 
 
if xtra > 0 then 
    x = x + 1  
end if 
mnths_in = x 
END FUNCTION  

?mnths_in({12/18/2009},{01/01/2010})
= 2

?mnths_in({1/31/10},{12/02/10})
= 12

?mnths_in({1/31/2009},{08/15/2010})
= 20

?mnths_in({12/18/1010},{08/26/2010})
= 11997

?mnths_in({12/18/2009},{01/05/2010})
= 1

?mnths_in({01/08/2000},{12/31/2000})
= 12

?mnths_in({03/15/2000},{09/11/2004})
= 54

And:
?mnths_in({12/18/1910},{12/18/2010})
= 1201

?mnths_in({12/18/1910},{08/26/2010})
= 1197
=================
Calc time ~0

Tom C:
I believe Tom M is using 30.25 as an average days per month, something you should never do and sorry Tom M, we are not there yet and we will never be there using average and rounding. Wrong concept, bad practice.

dbgin = {12/18/1910}
dend = {08/26/2010}
?round_UP((jdate(dend) - jdate(dbgin))/30.35,0)
= 1200 [COLOR=red]'wrong answer[/COLOR]
?12*(year(dend)-year(dbgin))+month(dend)-month(dbgin)+1
= 1197 [COLOR=seagreen]'correct answer[/COLOR]

To illustrate it further and make simpler to comprehend:

dbgin = {12/18/1910}
dend = {12/18/2010}
?round_UP((jdate(dend) - jdate(dbgin))/30.35,0)
= 1204 [COLOR=red]'wrong answer[/COLOR]
?12*(year(dend)-year(dbgin))+month(dend)-month(dbgin)+1
= 1201 [COLOR=seagreen]'correct answer[/COLOR]

[G Gabriel]

Re: Number of months in date range ?

Tom:
This is almost the same function I posted earlier and later scrapped it. It's no good. See earlier posts.

[Mike Wilson]

Re: Number of months in date range ?

Call me unintelligable or whatever, but please help me understand what is wrong with G's code as a creatively insightful contraction of what I posted? Does it not work somewhere/sometime.

12*(year(dend)-year(dbgin))+month(dend)-month(dbgin)+1

[SMARTII]

Re: Number of months in date range ?

I'm with you.
That is so simple a cave man could use it. In fact I think I will.
Just make it as a general function.

Call me unintelligable or whatever, but please help me understand what is wrong with G's code as a creatively insightful contraction of what I posted? Does it not work somewhere/sometime.

12*(year(dend)-year(dbgin))+month(dend)-month(dbgin)+1

[csda1]

Re: Number of months in date range ?

And Mike & Tom,

Call me unintelligable or whatever, but please help me understand what is wrong with G's code as a creatively insightful contraction of what I posted? Does it not work somewhere/sometime.

12*(year(dend)-year(dbgin))+month(dend)-month(dbgin)+1

And how is that different (except for incrementing by one) from the "Trivial Solution" I posted in the Code Archive Section in April of 2002?

AgeMonths=(YEAR(Date2)*12)+MONTH(Date2)-(YEAR(date1)*12)-MONTH(date1)

The terms may be rearranged, but it is no different.

On the other hand, my more elaborate solution there has 7 (or 11 depending if you count the IF's as operational) operative lines for the calculation, and is 100% accurate for any date that Alpha can work with, handles negative months and positive months and uses no loop, meaning it is faster. I also spent a lot of time proving that the fractional months, when multiplied out by 31, returns the correct day value when rounded appropriately.

Also, when using approximations for days of the months, the chance for accumulated errors building up in are risky, as you increase the total months and pass certain worst case years in the ranges. Unless you control the range of months, or create an approximation number that works without accumulated errors for all cases, there may be cases where it returns the wrong # (probably off by 1). 3 out of 4 every century is a leap year, but that is not accounted for. While you may say that the approximation method is good enough for the usable past and future, you can't be sure unless you run the numbers of the accumulated errors vs the worst case scenario. And the minute (or should I say month :) ) that you start putting in qualifications for leap years, the code becomes even more complex.

As an aside, using JDATE functions is a throwback to those languages that did not handle dates well. Alpha doesn't need them (but retains them for compatibility to other languages being modified for Alpha).

[Mike Wilson]

Re: Number of months in date range ?

Oops,
Yes, and Ira's solution that handles the subject with the same general method added an increment in a slightly different construct. While an interesting exchange... let us all let this thread become a good memory and an agreed solved issue... hey?

[Mark Pearson]

Re: Number of months in date range ?

My 20cents worth

FUNCTION countmonths AS N (dat1 AS D, dat2 AS D )
dim monthcount as n
dim month1 as n
dim month2 as n
dim year1 as n
dim year2 as n
dim global year3 as n

year1 = year(dat1)
year2 = year(dat2)
month1 = month(dat1)
month2 = month(dat2)

if year2 <> year1 then
year3 = year2 - year1

select case year3
case year3 = 1
monthcount = 12 - month1
countmonths = monthcount + month2

case (year3 > 1) .and. (month2 > month1)
monthcount = (month2 - month1) + (year3 * 12)
countmonths = monthcount
case (year3 > 1) .and. (month1 > month2)
monthcount = 12 - month1
monthcount = (monthcount + month2) + ((year3-1) * 12)
countmonths = monthcount
end select


else
countmonths = month2 - month1


end if




END FUNCTION

[Mike Wilson]

Re: Number of months in date range ?

Mark,
Nice code but it doesn't return the needed answer. The answer should be 13

dim d1 as D={02/02/2008}
dim d2 as d={02/08/2009}

? countmonths(d1,d2)
= 12