0

RHEL 7.5

BASH GNU bash, version 4.2.46(2)-release (x86_64-redhat-linux-gnu)

In MS Excel, I'm able to use Network days to find no. of days between two dates. Wondering if it's possible using bash as first preference (--or any other pre-installed language on Linux supporting to solve this possibly using a one-liner - my second preference). I'm not sure if there exists any library or custom tool/utility in Linux which calculates this value.

  • To calculate the number of workdays between two dates, you can use the NETWORKDAYS function. NETWORKDAYS automatically excludes weekends, and it can optionally exclude a custom list of holidays as well. Note that NETWORKDAYS includes both the start and end dates in the calculation if they are workdays.

I have a file.txt containing 2 column fields in YYYY-mm-dd format for Resolved and Start dates (you can ignore header line for now): 

Resolved,StartOfWork
2020-01-16,2020-01-10
2020-01-13,2020-01-13
2020-01-20,2020-01-15
2020-01-20,2020-01-14
2020-01-14,2020-01-09
2020-01-09,2020-01-08
2020-01-16,2020-01-14
2020-01-09,2020-01-07
2020-01-14,2020-01-12

For each row, I want to calculate no. of NETWORK i.e. WEEK DAYS only between these 2 dates (doesn't matter if Resolved/StartOfWork dates were on weekend days: Saturday/Sunday).

  • The calculation of no. of days SHOULD NOT include 'weekend-days i.e. Saturday/Sunday in it.

PS: For the purpose of this post, my question is very different than what this post is asking for: How to find the difference in days between two dates?

AKS
  • 16,482
  • 43
  • 166
  • 258
  • 1
    Note that requests for a "one liner" tend to be frowned on a bit here. If terseness is more important to you than robustness, maintainability, &c., that argues against a question being "practical" as our rules require (right next to the similar criteria "answerable"). – Charles Duffy Feb 14 '20 at 00:37
  • (BTW, I'd never heard the word "network days" used before; more accustomed to the terminology "business days" or "workdays"). – Charles Duffy Feb 14 '20 at 00:37
  • ...btw, insofar as you're specifying preinstalled software -- Python interpreter work for you? If that's the case, there's https://stackoverflow.com/questions/18233122/using-python-to-count-the-number-of-business-days-in-a-month -- yes, the specification is "in a month" whereas you want an arbitrary date range, but the accepted answer's logic is readily adaptable. – Charles Duffy Feb 14 '20 at 00:39
  • @CharlesDuffy I know there are bash experts way better than me, so I thought if one-liner is possible great, but it doesn't have to be. I agree with you. Agree with you on business day too. – AKS Feb 14 '20 at 00:39
  • In Python, there are few solutions using: https://stackoverflow.com/questions/3615375/count-number-of-days-between-dates-ignoring-weekends I wish BASH had all these goodies `workdays` or `numpy`'s busday_count() – AKS Feb 14 '20 at 00:50
  • Bash is a glue language. Python is just one of the many things you can use it to glue together. :) – Charles Duffy Feb 14 '20 at 00:53

2 Answers2

2

I'd call through to a Python interpreter for this. Adopting the accepted answer from Using Python to count the number of business days in a month? --

countBusinessDaysPy=$(cat <<'EOF'
import datetime, sys

businessdays = 0
startDate = datetime.date.fromisoformat(sys.argv[1])
endDate = datetime.date.fromisoformat(sys.argv[2])
if endDate < startDate:
    (startDate, endDate) = (endDate, startDate)

while startDate <= endDate:      # change from <= to < to not count both start and end days
    if startDate.weekday() < 5:
        businessdays += 1
    startDate += datetime.timedelta(days=1)

print(businessdays)
EOF
)

countBusinessDays() { python3 -c "$countBusinessDaysPy" "$@"; }

...gives you a shell function that calls a Python interpreter to do the math you need (note that this is an inclusive range). Thereafter:

$ countBusinessDays 2019-01-01 2020-01-01
262
$ countBusinessDays 2019-01-01 2019-01-07
5

Calling this looping over your file (note that in the real world, I'd do the looping in Python, not in bash) might look like:

{
  read -r header; printf '%s\n' "$header,TotalDates"
  while IFS=, read -r resolved startOfWork rest; do
    printf '%s\n' "${resolved},${startOfWork}${rest:+,$rest},$(countBusinessDays "$startOfWork" "$resolved")"
  done
} <yourInputFile

...which emits as output:

Resolved,StartOfWork,TotalDates
2020-01-16,2020-01-10,5
2020-01-13,2020-01-13,1
2020-01-20,2020-01-15,4
2020-01-20,2020-01-14,5
2020-01-14,2020-01-09,4
2020-01-09,2020-01-08,2
2020-01-16,2020-01-14,3
2020-01-09,2020-01-07,3
2020-01-14,2020-01-12,2
Charles Duffy
  • 280,126
  • 43
  • 390
  • 441
1

It may be the reinvention of the wheel but here's a bash solution (if interested).
Note that it requires the -d option to the date command.

while IFS="," read -r endday startday; do
    if (( lineno++ == 0 )); then                # handle header line
        echo "Resolved,StartOfWork,TotalDates"
        continue
    fi
    startsec=$(date -d "$startday" +%s)
    startdayofweek=$(date -d "$startday" +%w)   # 0 for Sun, ... 6 for Sat
    endsec=$(date -d "$endday" +%s)
    days=$(( (endsec - startsec) / 86400 + 1 )) # calendar days
    weeks=$(( days / 7 ))                       # number of weeks
    frac=$(( days % 7 ))                        # fraction mod 7
    if (( startdayofweek == 0 )); then          # case of starting on Sunday
        if (( frac > 0 )); then
            add=1                               # additional number of holidays
        else
            add=0
        fi
    else
        magic=$(( frac + (startdayofweek + 6) % 7 ))
                                                # calculate number of holidays
                                                # in the fraction period
        if (( magic < 6 )); then
            add=0
        elif (( magic == 6 )); then
            add=1
        else
            add=2
        fi
    fi
    holidays=$(( weeks * 2 + add ))             # total number of holidays
    workdays=$(( days - holidays ))             # subtract the holidays
    echo "$endday,$startday,$workdays"
done < inputfile
tshiono
  • 21,248
  • 2
  • 14
  • 22