# I need to calculate the number of working days between two dates.

Hello Everyone,

I need to calculate the number of working days between two dates. With the datediff function I will get the absolute number of days. For instance, 12/1/2013 to 12/31/2013 would be 30 days. However, what I want is 22 days. Any suggestions on how to accomplish this?

### (7) Answers

This expression calculates a 'datediff' resulting in number of business days (weekdays). It will work regardless of the number of weekends inside of the date range.

This calculates Business Days, inclusive. (e.g. Thurs through Tues = 4 days). Subtract 1 if needed.

floor(DateDiff("wk",[Start],[End])) * 2) - ( If(DayOfWeek([Start])=0,1,0)) - ( If(DayOfWeek([End])=6,1,0)) - ( If((DayOfWeek([Start])=6) and (DayOfWeek([End])<>6),2,0)) - ( If((DayOfWeek([Start])<>6) and (DayOfWeek([End])

## 1 Comment

If working days should omit holidays (in a particular holiday calendar), then a TERR Expression Function is useful. The following example **requires you to install** the "bizdays" and "timeDate" packages (see Tools > TERR Tools > Package Management). Specify Function Type: Column Function and Return Type: Integer. The script is below; it uses the NYSE holiday calendar for the years found in the data. This function can be used in both Calculated Columns and Custom Expressions.

```
diff_wd <- function(input1, input2) {
suppressMessages(library(bizdays))
suppressMessages(library(timeDate))
rng <- range(c(input1, input2))
hols <- as.Date(as.character(holidayNYSE(as.numeric(format(rng, "%Y")))))
cal <- create.calendar(name="temp", holidays=hols, weekdays=c("saturday", "sunday"),
start.date=rng[1], end=rng[2], financial=TRUE)
x <- as.integer(bizdays(input1, input2, cal=cal))
return(x)
}
output <- diff_wd(input1, input2)
```

I have tried all of these functions and none of them seem to work accurately for me when the two dates are separated by months.

Here is what I have come up with for **whole number of working days **between dates:

DateDiff("dd",Date([DateOpen]),Date(DateTimeNow()))

- floor(DateDiff("wk",Date([DateOpen]),Date(DateTimeNow())) * 2)

+ (If(DayOfWeek([DateOpen])=7,1,0))

- (If(DayOfWeek(DateTimeNow())=7,1,0))

*Note that DayOfWeek has Sunday =0 and Saturday = 7*

## 0 Comments

+ Add a CommentI was able to create a solution that accounts for the -1 when calculating between a Saturday and Sunday within the same weekend. I have found this to be 99% effective, since rounding still creates a few small errors, but I have found it to be a very small exception. YMMV.

In this solution, Date2 is the most recent date/the date you are subtracting from. For example, if you wanted to know the number of business days between 8/25/2017 and 8/28/2017, Date2 would be Aug 28th and Date1 would be Aug 25th (and the answer is 1).

Integer(If(DayOfWeek([Date2])=0,(If(DayOfYear([Date2])=DayOfYear([Date1]),0,(Integer(DateDiff("day",[Date1],[Date2]) / 7) * 5) + DayOfWeek([Date2]) - DayOfWeek([Date1]) + (If((DayOfWeek([Date2]) - DayOfWeek([Date1]))<0,5,0)))) + 0.4,If(DayOfYear([Date2])=DayOfYear([Date1]),0,(Integer(DateDiff("day",[Date1],[Date2]) / 7) * 5) + DayOfWeek([Date2]) - DayOfWeek([Date1]) + (If((DayOfWeek([Date2]) - DayOfWeek([Date1]))<0,5,0)))))

Basically the original formula works fine if your dates are between Monday and Saturday, but the Sat-Sun confuses things so this formula creates another "if" statement around that case and essentially adds just a little more to the final calculation so that the answer comes out to 0. If Date2 is not a Sunday, then it just does the normal calculation. The "Integer" wrapper around the entire formula is to round things off, otherwise you'll get decimals.

## 0 Comments

+ Add a CommentHi,

If you have two columns with start and end dates then you can use the below TERR function in a calculated column:

TERR_Real("output <- mapply(function(x,y) {sum(!weekdays(seq(as.Date(x, '%m/%d/%y'), as.Date(y, '%m/%d/%y'), 'days')) %in% c('Saturday', 'Sunday'))}, input1, input2)",[Start Date],[End Date])

## 2 Comments

i tried using this but it gives me integer value... I was expecting 1.4 days etc...

TERR_Real("output <- mapply(function(x,y) {sum(!weekdays(seq(as.Date(x,'%m/%d/%Y%H:%M:%S'), as.Date(y,'%m/%d/%Y%H:%M:%S'), 'days')) %in% c('Saturday', 'Sunday'))}, input1, input2)",[PR Attachment Date Time],[PO Date Time])

How to use this when I have datetime to be calculated as difference.

~Amit

## 0 Comments

+ Add a Comment### Similar Questions

## Haven't found what you are looking for?

## Existing Best Answer

This Question already has a 'Best Answer'. If you believe this answer is better, you must first uncheck the current **Best Answer**