John,
The following calc returns a ¶ list of 12 numbers giving the number
of times each month (Jan thru Dec) occurs between StartDate and
EndDate (assumed to be Date fields):
Case (
EndDate < StartDate ; "Invalid parameters" ;
Let ([
StartMonth = Month ( StartDate ) ;
EndMonth = Month ( EndDate ) ;
MonthCount = 12 * ( Year ( EndDate ) - Year
( StartDate ) ) + EndMonth - StartMonth ;
CompleteYears = Div ( MonthCount ; 12 ) - 1
] ;
CompleteYears + ( StartMonth <= 1 ) + ( EndMonth >=
1 ) & ¶ &
CompleteYears + ( StartMonth <= 2 ) + ( EndMonth >=
2 ) & ¶ &
CompleteYears + ( StartMonth <= 3 ) + ( EndMonth >=
3 ) & ¶ &
CompleteYears + ( StartMonth <= 4 ) + ( EndMonth >=
4 ) & ¶ &
CompleteYears + ( StartMonth <= 5 ) + ( EndMonth >=
5 ) & ¶ &
CompleteYears + ( StartMonth <= 6 ) + ( EndMonth >=
6 ) & ¶ &
CompleteYears + ( StartMonth <= 7 ) + ( EndMonth >=
7 ) & ¶ &
CompleteYears + ( StartMonth <= 8 ) + ( EndMonth >=
8 ) & ¶ &
CompleteYears + ( StartMonth <= 9 ) + ( EndMonth >=
9 ) & ¶ &
CompleteYears + ( StartMonth <= 10 ) + ( EndMonth
>= 10 ) & ¶ &
CompleteYears + ( StartMonth <= 11 ) + ( EndMonth
>= 11 ) & ¶ &
CompleteYears + ( StartMonth <= 12 ) + ( EndMonth
>= 12 )
)
)
You could either use this as a straight calc (in a field definition,
or script step or whatever) or define it as a CF, MonthCountList
( StartDate ; EndDate )
GetValue ( <<theResult>> ; n ) gives you the number of times month n
occurs
The first and last (possibly partial) months in the range are both
included in the counts which appears to be what you want. To exclude
one or both of these IF they are partial but not if they are complete
looks to be possible but I haven't worked out the details
BTW...
... (and how many days between the start date and the end of that
month, just in case somebody should ask for it in the future...)
The same cluncky logic goes for the last date in the range.
the number of days in the 1st month is:
Date ( Month ( StartDate ) + 1 ; 1 ; Year ( StartDate ) ) - StartDate
and in the last month, simply: Day ( EndDate )
cheers
Tom
On 5 Jun 2007, at 02:56, John Wenmeekers wrote:
I need to know (calculate) which month(s) and how many times they
are in a
given time range.
I have dateStart and dateEnd as field.
Out of those two I can generate a ¶ list (text) of days between
dateStart and
dateEnd.
Parsing out the first date from that field I can calculate the last
day of that
month, which gives me the 'first month'. (and how many days between
the
start date and the end of that month, just in case somebody should
ask for it
in the future...)
The same cluncky logic goes for the last date in the range.
Now I have two months...and stuck for the in between part.
I'm thinking to use a recursive custom function.
The 50 000 limit could not be a problem, there will be never 136 years
between dateStart and dateEnd.
Any hints how to tackle this one ?
TIA
