Days count by month 1

[republiccommando]

Hello, CR9, odbc connection and first time to post. I appreciate any help to create this forumla or point me to the right direction. I have a date range represented by two fields. I need to have a formula that counts the days per month between the date range. Example report I need to create:

title date1 date2 Jan Feb Mar Apr May ... Dec

rec1 12/15/06 03/01/07 31 28 1 0 0 ... 15
rec2 01/01/07 01/15/07 15 0 0 0 0 ... 0
rec3 03/29/07 04/03/07 0 0 3 3 0 ... 0

Thank you very much in advance.

 

[Madawc]

You could do it using a set of formula, one for each month. This could be hard-coded, eg for January 2006 say:

if  {start.date} =< Date(2006, 1, 1)
    if {end.date} >= Date(2006, 1, 31) then 31
    else 31 - ((DateDiff("d", {end.date}, (Date(2006, 1, 31)))
else...

You could also soft-code it from the current date using currentdate and DateAdd. I'd advise getting it working hard-coded, which is simplest. Then develope to make it more flexible, assuming this isn't a one-off.

A good trick for finding the last day of a month is to find the first day of the next month and subtract one day.


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[republiccommando]

Madawc, thanks for the reply. However, I am out for the long weekend and won't be back to work until Tuesday. I will try your formula then. I'll keep you posted. Thank you once again.

 

[republiccommando]

Madawc,

Thanks for the formula hint. I adjusted it a little bit based on our data. I appreciate your generous help!

Example April '07 Formula:

if {BEGINDATE} <= Date(2007, 4, 1)
and {EXPECTRETURN} >= Date(2007, 4, 30) then 30
else
if {BEGINDATE} >= Date(2007, 4, 1)
and {EXPECTRETURN} <= Date(2007, 4, 30) then
DateDiff("d",{BEGINDATE},{EXPECTRETURN})