pulling months of current year out of date range

[denisemathieu]

i have a begin date and an end date that may span between multiple years. i need to only pull the months out for the current year. then i need to spread a budget amount evenly across those months. please help!please!

 

[dgillz]

Will the report always have the same number of months or will it vary? Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[denisemathieu]

the report is by project and each project has a different date range.

 

[dgillz]

Will each date range always be the same number of months? or will it vary - the first range is 2 months, next one is 6, next 4, etc? Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[denisemathieu]

yes. 1 project could have a date range from 5/1/2001-12/31/2002 and another could have 1/1/2002-3/13/2003 and so on.

 

[kenhamady]

Are you prorating partial months? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[denisemathieu]

i don't know. i don't really know what that means.

 

[kenhamady]

Take the following period:

1/1/2002-3/13/2002

Assume you had a budget of 1000, what result would you expect to see? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[denisemathieu]

i would have 12 columns, 1 for each month. then it would spread the $1000 evenly over the months. but, the months it would use are based on the begin and end date. additionally, i only want it spread thru the months of the current year. for example, if i had a date range of 5/1/01 -11/25/02, i would only want to spread that $1000 jan 2002-11/2002.

 

[kenhamady]

So in your last example, to alloocate to the months you would divide by 11 for eleven months. Even if there was only one day in that month, like if the range ended on 11/1? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[denisemathieu]

yes

 

[kenhamady]

Well to get the number of months is easy, you just take the month of the end date. Since you are always starting with January, 1 the month is exactly the number to divide by. So your formula to get the number to divide by is:

Month ({?EndDate})

So your division would be:

{Budget amount} / Month ({?EndDate})
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com