Jump to content

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

Recommended Posts

  • 1 year later...

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

Link to comment
Share on other sites

  • 6 months later...
  • 1 year later...
  • 1 month later...
  • 1 month later...

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

Link to comment
Share on other sites

  • 11 months later...

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:


- 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

Link to comment
Share on other sites

  • 1 month later...

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





Link to comment
Share on other sites

  • 3 months later...

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

Link to comment
Share on other sites

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

output <- diff_wd(input1, input2)



Link to comment
Share on other sites

  • 1 year later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...