New Contributor
Posts: 2

# time difference between timestamps during business hours

Hi,

I would like to calculate time difference between two timestamps(datetime) during business hours only(8:00AM to 6:00PM), excluding public holidays and week ends. public holidays list is saved in a dataset or as a list in a macro variable value.

for example, starting time is 30DEC2014:15:30:00 and ending time is 05JAN2015:09:52:00.

In this case 01Jan2015 is public holiday and 3rd & 4th Jan15 are week end. Therefore, time difference I want to calculate is

2:30(on 30th Dec14) + 10:00(on 31st Dec14) + 10:00(on 2nd Jan15)  + 1:52(on 5th Jan15) =24:22

However, starting and/or ending time may be earlier than 8:00AM and/or later than 6:00PM. Still, I am only interested in time duration

Can any one help me to implement an efficient logic for the time calculation please.

Hari

Super User
Posts: 9,606

## Re: time difference between timestamps during business hours

Hi,

Here is a way, its a bit messy and not optimised, but its only to show you a method.  First, I wouldn't recommend putting the holidays in a macro parameter, that will just cause you headaches.  I would expand the time by dates first, then remove holidays.  Then have a datastep with a calculation of time, and retain that count through the dataset ending up with last row having total.  As mentioned this was done quickly to give the idea.

data phols;

format hol_date date9.;

hol_date="01JAN2015"d; output;

run;

/* Expand dates */

data inter;

format start_dt end_dt datetime. datepart date9.;

start_dt="30dec2014T15:30:00"dt;

end_dt="05JAN2015T09:52:00"dt;

do i=datepart(start_dt) to datepart(end_dt);

datepart=i;

output;

end;

run;

/* Remove holidays */

proc sql;

delete from INTER where exists(select HOL_DATE from PHOLS where HOL_DATE=DATEPART);

quit;

data want;

set inter end=last;

format tot_time time5.;

retain tot_time;

if _n_=1 then do;  /* These are where times come in */

if timepart(start_dt) < "08:00"t then tot_time="10:00"t;

else tot_time="18:00"t - timepart(start_dt);

end;

else if last then do;

if timepart(end_dt) > "18:00"t then tot_time=tot_time+"10:00"t;

else tot_time=tot_time + ("10:00"t - ((timepart(end_dt) - "08:00"t)));

/*    output;*/

end;

else tot_time=tot_time+"10:00"t;

run;

Posts: 3,852

## Re: time difference between timestamps during business hours

Sounds like a "Custom Time Interval" is what you need, perhaps you can use the "Holiday" function to help you determine other days to exclude.

Super User
Posts: 10,787

## Re: time difference between timestamps during business hours

```data holidays;
input date : date9.;
format date date9.;
cards;
01Jan2015
;
run;
data _null_;
if _n_ eq 1 then do;
declare hash h(dataset:'holidays');
h.definekey('date');
h.definedone();
end;
time=-1;
do i='30DEC2014:15:30:00'dt to '05JAN2015:09:52:00'dt ;
date=datepart(i);
if h.check() ne 0 and weekday(date) not in (1 7) then do;
if 8 le hour(i) lt 18 then time+1;
end;
end;
putlog time time.;
run;
```

Xia Keshan

Discussion stats
• 3 replies
• 620 views
• 0 likes
• 4 in conversation