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

[Chandras11]

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.

[Dheerj]

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..!!!

[harishalipaka · Accepted solution]

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

[Chandras11]

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

[harishalipaka]

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

[Chandras11]

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