Working Days Between Two Dates

[haydn]

I know it's probably obvious, but I need to simply calculate the number of working days between two dates.

 

[1chicken]

Try using this in your record or group selection formula:

not(dayofweek({Table.Date_Field)) in [1,7]) then count the days without having to worry about the weekend.

 

[Naith]

Have a look at the formula section on Ken Hamady's website.

http://www.kenhamady.com.

There is a formula or two there which handles what you want.

Naith

 

[FloridaHawk]

This is what I made last week for the exact same problem works great. Have it set up so you can test in any report:

// use this to text forumla- later remove TestDate (and currentdatetime if apply)and enter the field name(s)

datetimevar TestDate := datetime(2002,12,01);

numberVar BusSec :=
(datediff("s",TestDate,CurrentDateTime)) -

((If

datepart("yyyy",currentdatetime) = datepart("yyyy",TestDate) THEN
datepart("ww",currentdatetime) - datePart ("ww",TestDate)

ELSE
((((datepart("yyyy",currentdatetime) - datepart("yyyy",TestDate)) * 52) +
datepart("ww",currentdatetime))) - datePart ("ww",TestDate)) * (86400*2));


NumberVar AvgSecs := BusSec;

NumberVar secs := (AvgSecs Mod 60);

NumberVar mins := (AvgSecs\60 Mod 60);

NumberVar hours := (AvgSecs\ 3600 Mod 24);

NumberVar days := AvgSecs\ 3600 \ 24;

StringVar HH := totext(truncate(hours));

StringVar MM := totext(truncate(mins));


StringVar SS := totext(round(secs, 0));

StringVar DD := totext(truncate(days));


DD + " " + "Days" + " " + HH +"hrs"+ " " + MM+"Min" + " " +SS


//Create if statement for grouping

 

[Reebo99]

If this is just weekdays you can use :

DateDiff ("d", {StartDateField}, {EndDateField}) -
DateDiff ("ww", {StartDateField}, {EndDateField}, crSaturday) -
DateDiff ("ww", {StartDateField}, {EndDateField}, crSunday)

Hope this helps.....




Reebo
Scotland (Sunny with a Smile)

 

[mbarron]

Reebo,
The formula you provided has one minor flaw. It does not count the first day if it falls on a weekday. (see this thread thread149-304415)

A check for the the DOW of the first date in the range can be added to add that day to the total.

numbervar days ;
days:=
DateDiff ("d", {StartDateField}, {EndDateField}) -
DateDiff ("ww", {StartDateField},EndDateField},crSaturday) -
DateDiff ("ww", {StartDateField}, {EndDateField}, crSunday)


if dayofweek(StartDateField) in [2,3,4,5,6] then days:=days+1;
days


If you have CR8 or above, here is a loop I created to count days.

datevar firstday:={StartDateField});
datevar lastday:={EndDateField};
numbervar loop;
local numbervar wds;
numbervar span;


span:=lastday-firstday;
For loop:= 0 to span do(
if dayofweek(firstday+loop)in [2 to 6] then wds:=wds+1 else wds:=wds);
wds



Mike