topic Re: How to find the difference between two date values and subtracting weekend days? in Getting Data In

How to find the difference between two date values and subtracting weekend days?

Chandras11 — Tue, 29 Sep 2020 21:09:07 GMT

Hi All,

I need to find the difference between these two dates with the removal of the weekends

I have 2 date value fields as
ASSIGNED_DT = 2018-08-30 15:33:51
ANSWER_DT= 2018-09-03 16:59:48

   | makeresults | eval ASSIGNED_DT = "2018-08-22 15:33:51" | eval ANSWER_DT= "2018-09-03 16:59:48" | eval Assigned_Time = strptime(ASSIGNED_DT, "%Y-%m-%d %H:%M:%S")   | eval Answer_Time = strptime(ANSWER_DT, "%Y-%m-%d %H:%M:%S")  |  eval start=relative_time(Assigned_Time,"@d") | eval end=relative_time(Answer_Time,"@d") | eval Date=mvrange(start,end+86400,86400) | convert ctime(Date) timeformat="%+" | eval WeekendDays=mvcount(mvfilter(match(Date,"(Sun|Sat).*"))) | eval diff = tostring(( Answer_Time - Assigned_Time), "duration") | table ASSIGNED_DT, ANSWER_DT, diff,  WeekendDays

Everything is working fine and the results are:-

 ASSIGNED_DT              ANSWER_DT         diff                 WeekendDays
2018-08-22 15:33:51 2018-09-03 16:59:48 12+01:25:57.000000  4

Now I just need help with:
1. remove the WeekendDays from the diff
2. Convert diff-WeekendDays as the only number of days in decimal: for example here : it should be 8.01 days or 8 days 1 hour 25 mins only.

Thanks for your help.

Re: How to find the difference between two date values and subtracting weekend days?

harishalipaka — Thu, 06 Sep 2018 09:47:04 GMT

hi @Chandras11

can u try like this

| makeresults | eval ASSIGNED_DT = "2018-08-22 15:33:51" | eval ANSWER_DT= "2018-09-03 16:59:48" | eval Assigned_Time = strptime(ASSIGNED_DT, "%Y-%m-%d %H:%M:%S")   | eval Answer_Time = strptime(ANSWER_DT, "%Y-%m-%d %H:%M:%S")  |  eval start=relative_time(Assigned_Time,"@d") | eval end=relative_time(Answer_Time,"@d") | eval Date=mvrange(start,end+86400,86400) | convert ctime(Date) timeformat="%+" | eval WeekendDays=mvcount(mvfilter(match(Date,"(Sun|Sat).*"))) | eval diff = ( Answer_Time - Assigned_Time) |eval diff=(diff-(WeekendDays*86400)) | eval diff = tostring(diff, "duration") | table ASSIGNED_DT, ANSWER_DT, diff,  WeekendDays

Re: How to find the difference between two date values and subtracting weekend days?

Chandras11 — Thu, 06 Sep 2018 09:52:14 GMT

Hi,

Thanks for the response. Yes, it's working perfectly.
Can we convert the diff 8+01:25:57.000000 into days with decimal values: for example 8.01 days here

BR,
Chandra

Re: How to find the difference between two date values and subtracting weekend days?

harishalipaka — Thu, 06 Sep 2018 10:16:31 GMT

hi @Chandras11

try like this

| makeresults | eval ASSIGNED_DT = "2018-08-22 15:33:51" | eval ANSWER_DT= "2018-09-03 16:59:48" | eval Assigned_Time = strptime(ASSIGNED_DT, "%Y-%m-%d %H:%M:%S")   | eval Answer_Time = strptime(ANSWER_DT, "%Y-%m-%d %H:%M:%S")  |  eval start=relative_time(Assigned_Time,"@d") | eval end=relative_time(Answer_Time,"@d") | eval Date=mvrange(start,end+86400,86400) | convert ctime(Date) timeformat="%+" | eval WeekendDays=mvcount(mvfilter(match(Date,"(Sun|Sat).*"))) | eval diff = ( Answer_Time - Assigned_Time) |eval diff=(diff-(WeekendDays*86400)) | eval diff = tostring(diff, "duration") | table ASSIGNED_DT, ANSWER_DT, diff,  WeekendDays |eval temp=split(diff,"+") |eval temp2=split(mvindex(temp,1),":") |eval diff=mvindex(temp,0)+"."+mvindex(temp2,0)+" "+"days" | table ASSIGNED_DT, ANSWER_DT, diff,  WeekendDays

Re: How to find the difference between two date values and subtracting weekend days?

Chandras11 — Thu, 06 Sep 2018 10:38:35 GMT

perfect.. thanks a lot for it 🙂
its working fine...

Re: How to find the difference between two date values and subtracting weekend days?

Dheerj — Fri, 22 Nov 2019 17:04:03 GMT

Hi @harishalipaka ,

Could you also please provide the query to find the difference in hours instead of day's by excluding weekend days.

because in my similar scenario there would be a chance where assigned and answer date is same day.

Thanks in Advance..!!!