Calculating date differences with the minutes interval but excluding work days

[MBE57]

Hi, I need to calculate a turnaround time (tat) between two dates, to 2 decimal points, excluding work days. A team member helped me figure out the 1st part of the calculation, but we can't find how to exclude workdays at the minutes level. We'd appreciate some direction on this. Thank you.

What we have so far:

data TATcheck;

infile datalines dsd truncover;

format beg_date datetime16. end_date datetime16.;

input beg_date:datetime16. end_date:datetime16.;

       datalines;

22MAR21:03:06:00,22MAR2021:18:16:00

17MAR21:13:23:00,17MAR2021:16:23:00

07APR21:12:29:00,07APR2021:17:34:00

12MAR21:16:55:08,15MAR2021:16:22:22

13MAR21:04:31:16, 15MAR2021:17:46:02

;

data tatcheck2;

set tatcheck;

format tat 6.2;

tat = ((intck('minute',beg_date,end_date))/60)/24;

run;

Results:

Beg_date                        End_date                        tat

22MAR21:03:06:00         22MAR21:18:16:00         0.63 (correct)

17MAR21:13:23:00         17MAR21:16:23:00         0.13 (correct)

07APR21:12:29:00          07APR21:17:34:00          0.21 (correct)

12MAR21:16:55:08         15MAR21:16:22:22         2.98 (needs to exclude weekend)

13MAR21:04:31:16         15MAR21:17:46:02         2.55 (needs to exclude weekend)

[sbxkoenk · Accepted solution]

Hello,

Is the below program giving you what you want?

data TATcheck;
infile datalines dsd truncover;
format beg_date datetime16. end_date datetime16.;
 input beg_date:datetime16. end_date:datetime16.;
datalines;
22MAR21:03:06:00,22MAR2021:18:16:00
17MAR21:13:23:00,17MAR2021:16:23:00
07APR21:12:29:00,07APR2021:17:34:00
12MAR21:16:55:08,15MAR2021:16:22:22
13MAR21:04:31:16,15MAR2021:17:46:02
;
run;

data tatcheck3(drop=i);
 set tatcheck;
 format tat: 6.2;
 tat_exclude_weekend = 0;
 tat_exclude_workday = 0;
 do i = INTNX('dtminute',beg_date,0) to INTNX('dtminute',end_date,0) by 60;
  if weekday(datepart(i)) NOT IN (1,7)       then tat_exclude_weekend=tat_exclude_weekend+1;
  if weekday(datepart(i)) NOT IN (2,3,4,5,6) then tat_exclude_workday=tat_exclude_workday+1;
 end;
 tat_exclude_weekend = (tat_exclude_weekend/60)/24;
 tat_exclude_workday = (tat_exclude_workday/60)/24;
run;
/* end of program */

Koen

[MBE57]

Thank you for your help, it's appreciated very much.

[ballardw]

First thing, clarify your actual need. The subject of the post says "excluding work days", the body of your question says "but we can't find how to exclude workdays at the minutes level." and then you example shows:

12MAR21:16:55:08         15MAR21:16:22:22         2.98 (needs to exclude weekend)

Two of these things are not like the other.

You also really don't mention what UNITS your result is supposed to be.

Details: Assuming a Friday to Monday interval, what time on FRIDAY to stop incrementing time? What time on MONDAY to you start incrementing the time?

And for an obnoxious kicker, what about Holidays? What do you expect to happen when a Holiday is in the interval?

The basic approach would be to test your two values to get the days of the week involved.

Dayofweek = weekday(datepart(beg_date));

Friday is 6, Monday is 2 for example. Then decide using that information if a weekend (week day???) is involved.

Then you get to apply the not-stated yet rule for how much time is accumulated for which days on the outside of your exclusion period needs to be counted. There is going to be some If/Then/else logic involved.

You would split things up such as

FirstBit = ((intck('minute',beg_date,<date time to stop for end of day friday>))60)/24;

Secondbit= ((intck('minute',<date time to start for monday>,end_date))60)/24;

Then add Firstbit and secondbit.

The INTNX function and/or DHMS would be in the second bit.

But without an example of the exact times involved I'm not going to write code.

[Ksharp]

data TATcheck;
infile datalines dsd truncover;
format beg_date datetime16. end_date datetime16.;
 input beg_date:datetime16. end_date:datetime16.;
datalines;
22MAR21:03:06:00,22MAR2021:18:16:00
17MAR21:13:23:00,17MAR2021:16:23:00
07APR21:12:29:00,07APR2021:17:34:00
12MAR21:16:55:08,15MAR2021:16:22:22
13MAR21:04:31:16,15MAR2021:17:46:02
;
run;
data want;
 set tatcheck;
 seconds=0;
 do i=beg_date to end_date;
   if weekday(datepart(i)) not in (1 7) then seconds+1;
 end;
 tat=seconds/60/60/24;
 drop seconds i;
run;