No of Businessdays between two dates

[snehalatha]

Hi , The below give me the no of days between two dates but i want to calculate only no of business days between two dates.
eval start=relative_time(now(),"@d") |
eval start_string=strftime(start,"%Y-%m-%d %A")|
eval end=strptime('time_submitted',"%Y-%m-%d %H:%M")|
eval end_string=strftime(end,"%Y-%m-%d %A") |
eval SLA_duration=round((start-end)/86400,0)|

Please help me

[joesrepsolc]

,cmerriman, trying to use this solution for my dates that are already in epoch time (start and end dates). Unexpected results. Data is coming in from .csv files and date columns are a string (like “06/14/2019”) with no times. Which i am just converting to epoch time to do some math between dates. Struggling to avoid the weekend dates though. Data brought in through forwarder have all the additional date_ fields in Splunk, but not from the .csv file.

WHAT I HAVE SO FAR (calculates the days just fine, but not sure how to eliminate the weekends, and only use business days (Mon-Fri) in the results):

index=testdata
| eval TriggerDateX = strptime(TriggerDate,"%m/%d/%y")
| eval MailDateX = strptime(MailDate,"%m/%d/%y")
| eval Diff = round((MailDateX-TriggerDateX)/86400,0)

Appreciate the help. Joe

[snehalatha]

Wow perfect answer. The small mistake what I did till now is
|eval Date1=mvrange(start,end,86400) which was not giving the results.

Thank you so much cmerriman. . Appreciate your quick response.

[cmerriman · Accepted solution]

you'll want something like this at the end of your syntax (if I understand, it could probably replace SLA_duration and BusinessDays could be that field name, instead):

|eval Date1=mvrange(start,end,86400)
|eval BusinessDays=mvcount(mvfilter(NOT match(Date1,"(Sun|Sat).*")))

[elliotproebstel]

I'd never used mvrange before, so I was playing around with this answer. Fun new trick!

A couple things I noticed - @snehalatha, if your value for start is later/greater than your value for end (seems likely, based on the way your calculation of SLA_duration was subtracting end from start value) - then the first line will need to be:
|eval Date1=mvrange(end,start,86400)
And at least with my testing, I needed to add a strftime to the second line, so mine wound up like this:
|eval BusinessDays=mvcount(mvfilter(NOT match(strftime(Date1, "%A"),"(Sun|Sat).*")))