How to find Number of days between two custom dates, excluding weekends and holidays?

How To Find Number Of Days Between Two Custom Dates, Excluding Weekends And Holidays?

Hi Splunkers!
Today we are back with another astonishing trick of Splunk. I hope you have understood from the title of this blog that what we will do today, but if not then let’s take an example.
Suppose you have a custom date field as “recived_date” which is a date of receiving an application and now you want to calculate the number of days covered until today from when the application is received.

It’s simple right but the real problem occurs if you want to exclude weekends (i.e. Saturday and Sunday) or any holidays (i.e Christmas or New year etc). That means you only need to consider working days. Ahh! Now it’s tricky right, don’t worry we are here to help you only follow us.

Step 1:
First we will start with excluding the weekends.
So try to understand the query given below and it will give you the desired result.

index="time_gap" sourcetype="csv" 
| eval now=now(),now_date=strftime(now,"%Y/%m/%d") 
| eval dates=mvrange(strptime(recived_date,"%Y/%m/%d"),now,86400) 
| table now recived_date dates now_date 
| convert ctime(dates) timeformat="%A" 
| eval dates=mvfilter(NOT match(dates,"(Saturday|Sunday)")) 
| eval noOfDays=mvcount(dates)

As you can see that in the “dates” field there is no Sunday or Saturday . That’s it, our first objective is completed. Now remove the dates and now field you will get your desired result.

Explanation:
Here at first using mvrange() function we created a multi-value field consisting all the dates from “recived_date” and now() with the range of 86400 sec (total seconds of 24 hours). Then using the ctime() function we have converted them into human readable date format and then using “timeformat” we changed those dates in days (like Monday,Tuesday etc) and finally we have discarded Sunday and Saturday from the “dates” field.

Step 2:
Now we will try to exclude the holidays, we will try to discard here 1st January, 2020 from the “noOffDays” field.
At first try to find the epoch of that you, you can do it like this.

| makeresults
| eval date="2020/01/01"
| fields - _time
| eval date_epoch=strptime(date,"%Y/%m/%d")

You need the change the date which you want to exclude in the underlined area. Then copy the value from the “date_epoch” field and paste it in your main query as shown in below figure (Step 3).
Now if you have multiple holidays to remove then you do it in this fashion,

| makeresults
| eval date=split("2019/12/25,2020/01/01",",")
| mvexpand date
| fields - _time
| eval date_epoch=strptime(date,"%Y/%m/%d")

You can split all dates separating by comma (,)
You need the change the date which you want to exclude in the underlined area. Then copy the value from the “date_epoch” field and paste it in your main query as shown in below figure (step 4).

Step 3:

index="time_gap" sourcetype="csv" 
| eval now=now(),now_date=strftime(now,"%Y/%m/%d") 
| eval dates=mvrange(strptime(recived_date,"%Y/%m/%d"),now,86400) 
| table now recived_date dates now_date 
| rex field=dates mode=sed s/1577817000.000000//g 
| convert ctime(dates) timeformat="%A" 
| eval dates=mvfilter(NOT match(dates,"(Saturday|Sunday)")) 
| eval noOfDays=mvcount(dates) 
| fields - now dates

Now you can see the changes, if you compare it with our previous result you can see one count is short here for the first two values (because the first two values are beyond 2020/01/01).

Step 4:
If you want to exclude multiple holidays then you can do this

index="time_gap" sourcetype="csv" 
| eval now=now(),now_date=strftime(now,"%Y/%m/%d") 
| eval dates=mvrange(strptime(recived_date,"%Y/%m/%d"),now,86400) 
| table now recived_date dates now_date 
| eval dates=mvfilter(NOT match(dates,"(1577212200.000000|1577817000.000000)"))
| convert ctime(dates) timeformat="%A" 
| eval dates=mvfilter(NOT match(dates,"(Saturday|Sunday)")) 
| eval noOfDays=mvcount(dates) 
| fields - now dates

Hope you have understood this topic on “How to find Number of days between two custom dates, excluding weekends and holidays?”

Happy Splunking!!