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

[Manoj Chaurasia]

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

[Philippe Verspeelt]

The good old question of workdays :-). I use the formula below at work and plugged it on our spotfire intranet pages for other users to use. I took this from the spotfire tips and tricks blog last year.

If you want to use this formula, just replace the START and END DATES with your corresponding columns. This formula does not take into account any national holidays.

if(dayofyear([sTART DATE])=dayofyear([END DATE]),0,(Integer(DateDiff("day",[sTART DATE],[END DATE]) / 7) * 5) + DayOfWeek([END DATE]) - DayOfWeek([sTART DATE]) + (if((DayOfWeek([END DATE]) - DayOfWeek([sTART DATE]))

[Manoj Chaurasia]

Thanks Philippe, works like a charm!

[Ashwin Kumar 5]

i am not able to get the proper answer by this code. 

 

getting unexpected error in XPath.

[Michael Ng 2]

Hi,

 

This solution does not work for Saturday to Sunday (It will return value as -1)

Is it possible to make this zero

 

Regards,

 

Michael Ng

[Giovanni Lucarelli]

More simple:if(DayOfWeek([YourDate]) in (0, 6),0,1)

 

Ciao

[Ala Jaouni]

Hi,

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

[John Gayton]

I 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]))

[Denise Bossarte]

I have tried all of these functions and none of them seem to work accurately for me when thetwo dates areseparated bymonths.

Here is whatI have come up with for whole number of workingdays 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

[Amit Patil 2]

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

 

~Amit

[Amit Patil 2]

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])

 

 

 

 

[Thom Siragusa]

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.

DateDiff("dd",[start],[End]) + 1 - (

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])

[Jagrata Minardi]

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)

 

 

[Roberto Sichera]

Hi Thom,

Your expression seems to be truncated, can you post the full formula please