count the nonworkable days

[Lhuffst]

In our business, we keep track of the days a contractor cannot work (raindays). This is generally a holiday or rainday or it's too cold. The original file is in excel and saved as a .csv file.
On the crystal side, I need to count the number of raindays that fall within a certain time frame and need help with the formula.

Fields:
Issue Date - (Date)
Complete Date - (Date)
RainDays (date)

so I need to count the number of raindays that fall within the issue date and complete date for each record.
How would I write the formula? I was thinking count(raindays) in (issue date, complete date) but that give me an error
Thanks
Lhuffst

 

[Lhuffst]

I should have said, the raindays is a spreadsheet that is continually updated. Currently, there are 50 days from January 1, 2016 to dec 31, 2016 as the holidays are already entered.
If the day off is before or after the issue and complete dates, then that record should not count those days. For each record, since they are different dates, the count will be different.

 

[pmax9999]

Details are a bit vague but based on the limited information available I would create the following formula:

IF	{table.raindays} IN [{table.issue date} TO {table.complete date}]
THEN	1

Create a Summary to SUM the result of that formula.

Hope this helps.


Cheers
Pete

 

[Lhuffst]

Hi Pete
Would that work for each record? I'm not trying to get a summary but need the number of nonworkable days between two dates (per record)
for example if the raindays (non-workabledays) has:
Jan 1 - holiday
Jan 2 - rain
Jan20 - snow
May 30 - holiday
July 4 - holiday
etc.

AND
the record has: issue date Complete Date Num of Days
Dec 30 2015 March 20, 2016 79 (march 20 - Dec 30 - 3 (rain days in that time frame)
Feb 2 2016 Aug 30 2016 207 (aug 30 - feb 2 - 3 (rain days)

Thanks
Lhuffst