Date - substract holidays

[paverley]

Hello

I'm asked to calculate the time spent for resolving a problem.
Therefore, i use the following formula:

numberVar dur := datediff ("s",{ROOTCAUSEM1.OPEN_TIME}, {ROOTCAUSEM1.CLOSE_TIME}-
datediff ("ww", {ROOTCAUSEM1.OPEN_TIME},{ROOTCAUSEM1.CLOSE_TIME},crsaturday) -
datediff ("ww", {ROOTCAUSEM1.OPEN_TIME},{ROOTCAUSEM1.CLOSE_TIME}, crsunday)
);

numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);

hhmmss := totext(hrs, "0") + ":" + totext(min, "00") + ":" + totext(sec, "00");

hhmmss


So, this is pretty good, because the weekends are substracted. Now, i want to go a bit further and also look at the holidays. Holidays are stored in the table CALHOLIDAYM1, and the beginning of a holiday is stored in the field START_DATE , the end of a holiday is stored in END_DATE. Eg. Startdate = 14/7/2004 0:00:00 and Enddate = 14/7/2004 0:00:00.

My question is : how can i calculate the time spent looking after weekends AND holidays?

Patrick

 

[paverley]

sorry, i meant:
start date : 14/7/2004 0:00:00
end date : 14/7/2004 23:59:00

 

[Madawc]

Which Crystal? In 8.5, you should try using Formula Fields, which could do the various calculations separately, using much the same logic as you use in your variable.

You could use another formula field to find @holiday_time and then subtract this from @total_time for the answer you want.

Madawc Williams (East Anglia)

 

[paverley]

it's crystal 9.0 ...

 

[Madawc]

My method should still work. As a general point, formula fields and running totals are usually better and more controllable than variables

Madawc Williams (East Anglia)

 

[paverley]

i believe you but HOW???

 

[paverley]

And, eg. Thanksgiving, ... are NOT counted as holidays over here!

 

[paverley]

pls can somebody help me
i'm really desperate

 

[Madawc]

You'd have @Fulltime

datediff ("s",{ROOTCAUSEM1.OPEN_TIME}, {ROOTCAUSEM1.CLOSE_TIME}-

You'd need to do something similar for @weekend and @holiday. I can't say exactly what, because it's not totally clear to me how your original method works. Do it by trial and error, until you get results that look right. Do a small test report with the old method along-side the new and work on it until they match.

Finally sum the separate formulae, @Alltime

@Fulltime - (@weekend + @holiday)

Madawc Williams (East Anglia)

 

[Madawc]

Is the formula something you wrote, or did you inherit it from another coder? If it's the latter, then I suggest you post again and ask someone to explain in detail what this inherited bit of code actually does. I can't help on that, because I always use different methods.

Madawc Williams (East Anglia)

 

[paverley]

well, the second part is inherited , but it only converts time to the format i needed!
i'll try what you told me and i will let you know the results (thanks so much for willing to help me out coz i'm really in trouble with that)