InfoPath: Calculate business days between 2 dates including leap year

The following articles describes the process to allow InfoPath to calculate difference between 2 dates.

Required Fields:

StartDate
EndDate
StartDateFirstDayOfYear
StartDateLastDayOfYear
EndDateFirstDayOfYear
EndDateLastDayOfYear
TotalDaysYearStartDate
TotalDaysYearEndDate
StartDateDayOfYear
EndDateDayOfYear
StartDateWeekOfYear
EndDateWeekOfYear
WeeksDifference
DifferenceDays
WeekendDays
Businessdays

Step 1 – Calculate First Day of Year

Add new rule to “StartDate”
Condition: None
Rule type: Action
Run action:
Set field’s value: StartDateFirstDayOfYear
Set To:

concat(substring-before(StartDate, "-"), "-01-01")

Add new rule to “EndDate”
Condition: None
Rule type: Action
Run action:
Set field’s value: EndDateFirstDayOfYear
Set To:

concat(substring-before(EndDate, "-"), "-01-01")

Step 2 – Calculate Last day of Year

Add new rule to “StartDate”
Condition: None
Rule type: Action
Run action:
Set field’s value: StartDateFirstDayOfYear
Set To:

concat(substring-before(StartDate, "-"), "-12-31")

Add new rule to “EndDate”
Condition: None
Rule type: Action
Run action:
Set field’s value: EndDateFirstDayOfYear
Set To:

concat(substring-before(EndDate, "-"), "-12-31")

Step 3 – Calculate the total days for the year so we can check if this is a leap year

Add new rule to “StartDate”
Condition: None
Rule type: Action
Run action:
Set field’s value: StartDateFirstDayOfYear
Set To:

((number(substring(StartDateLastDayOfYear, 9, 2)) + floor((153 * (number(substring(StartDateLastDayOfYear, 6, 2)) + 12 * (floor((14 - number(substring(StartDateLastDayOfYear, 6, 2))) / 12)) - 3) + 2) / 5) + (number(substring(StartDateLastDayOfYear, 1, 4)) + 4800 - (floor((14 - number(substring(StartDateLastDayOfYear, 6, 2))) / 12))) * 365 + floor((number(substring(StartDateLastDayOfYear, 1, 4)) + 4800 - (floor((14 - number(substring(StartDateLastDayOfYear, 6, 2))) / 12))) / 4) - floor((number(substring(StartDateLastDayOfYear, 1, 4)) + 4800 - (floor((14 - number(substring(StartDateLastDayOfYear, 6, 2))) / 12))) / 100) + floor((number(substring(StartDateLastDayOfYear, 1, 4)) + 4800 - (floor((14 - number(substring(StartDateLastDayOfYear, 6, 2))) / 12))) / 400) - 32045) - (number(substring(StartDateFirstDayOfYear, 9, 2)) + floor((153 * (number(substring(StartDateFirstDayOfYear, 6, 2)) + 12 * (floor((14 - number(substring(StartDateFirstDayOfYear, 6, 2))) / 12)) - 3) + 2) / 5) + (number(substring(StartDateFirstDayOfYear, 1, 4)) + 4800 - (floor((14 - number(substring(StartDateFirstDayOfYear, 6, 2))) / 12))) * 365 + floor((number(substring(StartDateFirstDayOfYear, 1, 4)) + 4800 - (floor((14 - number(substring(StartDateFirstDayOfYear, 6, 2))) / 12))) / 4) - floor((number(substring(StartDateFirstDayOfYear, 1, 4)) + 4800 - (floor((14 - number(substring(StartDateFirstDayOfYear, 6, 2))) / 12))) / 100) + floor((number(substring(StartDateFirstDayOfYear, 1, 4)) + 4800 - (floor((14 - number(substring(StartDateFirstDayOfYear, 6, 2))) / 12))) / 400) - 32045)) + 1

Add new rule to “EndDate”
Condition: None
Rule type: Action
Run action:
Set field’s value: EndDateFirstDayOfYear
Set To:

((number(substring(EndDateLastDayOfYear, 9, 2)) + floor((153 * (number(substring(EndDateLastDayOfYear, 6, 2)) + 12 * (floor((14 - number(substring(EndDateLastDayOfYear, 6, 2))) / 12)) - 3) + 2) / 5) + (number(substring(EndDateLastDayOfYear, 1, 4)) + 4800 - (floor((14 - number(substring(EndDateLastDayOfYear, 6, 2))) / 12))) * 365 + floor((number(substring(EndDateLastDayOfYear, 1, 4)) + 4800 - (floor((14 - number(substring(EndDateLastDayOfYear, 6, 2))) / 12))) / 4) - floor((number(substring(EndDateLastDayOfYear, 1, 4)) + 4800 - (floor((14 - number(substring(EndDateLastDayOfYear, 6, 2))) / 12))) / 100) + floor((number(substring(EndDateLastDayOfYear, 1, 4)) + 4800 - (floor((14 - number(substring(EndDateLastDayOfYear, 6, 2))) / 12))) / 400) - 32045) - (number(substring(EndDateFirstDayOfYear, 9, 2)) + floor((153 * (number(substring(EndDateFirstDayOfYear, 6, 2)) + 12 * (floor((14 - number(substring(EndDateFirstDayOfYear, 6, 2))) / 12)) - 3) + 2) / 5) + (number(substring(EndDateFirstDayOfYear, 1, 4)) + 4800 - (floor((14 - number(substring(EndDateFirstDayOfYear, 6, 2))) / 12))) * 365 + floor((number(substring(EndDateFirstDayOfYear, 1, 4)) + 4800 - (floor((14 - number(substring(EndDateFirstDayOfYear, 6, 2))) / 12))) / 4) - floor((number(substring(EndDateFirstDayOfYear, 1, 4)) + 4800 - (floor((14 - number(substring(EndDateFirstDayOfYear, 6, 2))) / 12))) / 100) + floor((number(substring(EndDateFirstDayOfYear, 1, 4)) + 4800 - (floor((14 - number(substring(EndDateFirstDayOfYear, 6, 2))) / 12))) / 400) - 32045)) + 1

Step 4 – Get the day of the year

Add new rule to “StartDate”
Condition: TotalDaysYearStartDate = “366”
Rule type: Action
Run action:
Set field’s value: StartDateDayOfYear
Set To:

((number(substring(StartDate, 9, 2)) + floor((153 * (number(substring(StartDate, 6, 2)) + 12 * floor((14 - number(substring(StartDate, 6, 2))) / 12) - 3) + 2) / 5) + (number(substring(StartDate, 1, 4)) + 4800 - floor((14 - number(substring(StartDate, 6, 2))) / 12)) * 365 + floor((number(substring(StartDate, 1, 4)) + 4800 - floor((14 - number(substring(StartDate, 6, 2))) / 12)) / 4) - floor((number(substring(StartDate, 1, 4)) + 4800 - floor((14 - number(substring(StartDate, 6, 2))) / 12)) / 100) + floor((number(substring(StartDate, 1, 4)) + 4800 - floor((14 - number(substring(StartDate, 6, 2))) / 12)) / 400) - 32045 - (number(substring(StartDateFirstDayYear, 9, 2)) + floor((153 * (number(substring(StartDateFirstDayOfYear, 6, 2)) + 12 * floor((14 - number(substring(StartDateFirstDayOfYear, 6, 2))) / 12) - 3) + 2) / 5) + (number(substring(StartDateFirstDayOfYear, 1, 4)) + 4800 - floor((14 - number(substring(StartDateFirstDayOfYear, 6, 2))) / 12)) * 365 + floor((number(substring(StartDateFirstDayOfYear, 1, 4)) + 4800 - floor((14 - number(substring(StartDateFirstDayOfYear, 6, 2))) / 12)) / 4) - floor((number(substring(StartDateFirstDayOfYear, 1, 4)) + 4800 - floor((14 - number(substring(StartDateFirstDayOfYear, 6, 2))) / 12)) / 100) + floor((number(substring(StartDateFirstDayOfYear, 1, 4)) + 4800 - floor((14 - number(substring(StartDateFirstDayOfYear, 6, 2))) / 12)) / 400) - 32045) + 1

Add new rule to “StartDate”
Condition: TotalDaysYearStartDate = “365”
Rule type: Action
Run action:
Set field’s value: StartDateDayOfYear
Set To:

((number(substring(StartDate, 9, 2)) + floor((153 * (number(substring(StartDate, 6, 2)) + 12 * floor((14 - number(substring(StartDate, 6, 2))) / 12) - 3) + 2) / 5) + (number(substring(StartDate, 1, 4)) + 4800 - floor((14 - number(substring(StartDate, 6, 2))) / 12)) * 365 + floor((number(substring(StartDate, 1, 4)) + 4800 - floor((14 - number(substring(StartDate, 6, 2))) / 12)) / 4) - floor((number(substring(StartDate, 1, 4)) + 4800 - floor((14 - number(substring(StartDate, 6, 2))) / 12)) / 100) + floor((number(substring(StartDate, 1, 4)) + 4800 - floor((14 - number(substring(StartDate, 6, 2))) / 12)) / 400) - 32045 - (number(substring(StartDateFirstDayYear, 9, 2)) + floor((153 * (number(substring(StartDateFirstDayOfYear, 6, 2)) + 12 * floor((14 - number(substring(StartDateFirstDayOfYear, 6, 2))) / 12) - 3) + 2) / 5) + (number(substring(StartDateFirstDayOfYear, 1, 4)) + 4800 - floor((14 - number(substring(StartDateFirstDayOfYear, 6, 2))) / 12)) * 365 + floor((number(substring(StartDateFirstDayOfYear, 1, 4)) + 4800 - floor((14 - number(substring(StartDateFirstDayOfYear, 6, 2))) / 12)) / 4) - floor((number(substring(StartDateFirstDayOfYear, 1, 4)) + 4800 - floor((14 - number(substring(StartDateFirstDayOfYear, 6, 2))) / 12)) / 100) + floor((number(substring(StartDateFirstDayOfYear, 1, 4)) + 4800 - floor((14 - number(substring(StartDateFirstDayOfYear, 6, 2))) / 12)) / 400) - 32045) + 5

Add new rule to “EndDate”
Condition: TotalDaysYearEndDate = “366”
Rule type: Action
Run action:
Set field’s value: EndDateDayOfYear
Set To:

((number(substring(EndDate, 9, 2)) + floor((153 * (number(substring(EndDate, 6, 2)) + 12 * (floor((14 - number(substring(EndDate, 6, 2))) / 12)) - 3) + 2) / 5) + (number(substring(EndDate, 1, 4)) + 4800 - (floor((14 - number(substring(EndDate, 6, 2))) / 12))) * 365 + floor((number(substring(EndDate, 1, 4)) + 4800 - (floor((14 - number(substring(EndDate, 6, 2))) / 12))) / 4) - floor((number(substring(EndDate, 1, 4)) + 4800 - (floor((14 - number(substring(EndDate, 6, 2))) / 12))) / 100) + floor((number(substring(EndDate, 1, 4)) + 4800 - (floor((14 - number(substring(EndDate, 6, 2))) / 12))) / 400) - 32045) - (number(substring(EndDateLastDayOfYear, 9, 2)) + floor((153 * (number(substring(EndDateLastDayOfYear, 6, 2)) + 12 * (floor((14 - number(substring(EndDateLastDayOfYear, 6, 2))) / 12)) - 3) + 2) / 5) + (number(substring(EndDateLastDayOfYear, 1, 4)) + 4800 - (floor((14 - number(substring(EndDateLastDayOfYear, 6, 2))) / 12))) * 365 + floor((number(substring(EndDateLastDayOfYear, 1, 4)) + 4800 - (floor((14 - number(substring(EndDateLastDayOfYear, 6, 2))) / 12))) / 4) - floor((number(substring(EndDateLastDayOfYear, 1, 4)) + 4800 - (floor((14 - number(substring(EndDateLastDayOfYear, 6, 2))) / 12))) / 100) + floor((number(substring(EndDateLastDayOfYear, 1, 4)) + 4800 - (floor((14 - number(substring(EndDateLastDayOfYear, 6, 2))) / 12))) / 400) - 32045)) + 1

Add new rule to “EndDate”
Condition: TotalDaysYearEndDate = “365”
Rule type: Action
Run action:
Set field’s value: EndDateDayOfYear
Set To:

((number(substring(EndDate, 9, 2)) + floor((153 * (number(substring(EndDate, 6, 2)) + 12 * (floor((14 - number(substring(EndDate, 6, 2))) / 12)) - 3) + 2) / 5) + (number(substring(EndDate, 1, 4)) + 4800 - (floor((14 - number(substring(EndDate, 6, 2))) / 12))) * 365 + floor((number(substring(EndDate, 1, 4)) + 4800 - (floor((14 - number(substring(EndDate, 6, 2))) / 12))) / 4) - floor((number(substring(EndDate, 1, 4)) + 4800 - (floor((14 - number(substring(EndDate, 6, 2))) / 12))) / 100) + floor((number(substring(EndDate, 1, 4)) + 4800 - (floor((14 - number(substring(EndDate, 6, 2))) / 12))) / 400) - 32045) - (number(substring(EndDateLastDayOfYear, 9, 2)) + floor((153 * (number(substring(EndDateLastDayOfYear, 6, 2)) + 12 * (floor((14 - number(substring(EndDateLastDayOfYear, 6, 2))) / 12)) - 3) + 2) / 5) + (number(substring(EndDateLastDayOfYear, 1, 4)) + 4800 - (floor((14 - number(substring(EndDateLastDayOfYear, 6, 2))) / 12))) * 365 + floor((number(substring(EndDateLastDayOfYear, 1, 4)) + 4800 - (floor((14 - number(substring(EndDateLastDayOfYear, 6, 2))) / 12))) / 4) - floor((number(substring(EndDateLastDayOfYear, 1, 4)) + 4800 - (floor((14 - number(substring(EndDateLastDayOfYear, 6, 2))) / 12))) / 100) + floor((number(substring(EndDateLastDayOfYear, 1, 4)) + 4800 - (floor((14 - number(substring(EndDateLastDayOfYear, 6, 2))) / 12))) / 400) - 32045)) + 5

Step 5 – Calculate the week of the year

Set field StartDateWeekOfYear default value to Formula:

round(StartDateDayOfYear / 7)

Set field EndDateWeekOfYear default value to Formula:

round(EndDateDayOfYear / 7)

Step 6 – Get the number of weeks difference between the 2 dates

Set field WeeksDifference default value to Formula:

(EndDateWeekOfYear - StartDateWeekOfYear)

Step 7 – Calculate the difference in days between the two dates

Set field DifferenceDays default value to Formula:

(EndDateDayOfYear - StartDateDayOfYear) + 1

Note: On this case I’ve added one day to the formula so the last day is inclusive, if you don’t want the last day inclusive, remove the “+ 1” from your formula
Step 8 – Calculate the weekend Days

Set field default value to Formula: WeeksDifference * 2

Step 9 – Calculate the business days

Set field Businessdays default value to Formula:

DifferenceDays - WeekendDays

2 thoughts on “InfoPath: Calculate business days between 2 dates including leap year

  1. Hi Stephen,

    Thanks for your clearly-written out post. However, you had me scratching my head as you did not the field types (string, integer, etc.) for the fields you used to create this solution.

    All I’m left is to assume that all the field types in your writeup is of string type.
    I hope that this assumption doesn’t lead to any form of errors.

    Thanks,

    1. Hi Anthony,
      Thanks for your comment on our blog.

      Yes, all the fields should be created as text/dates.

      If you have any issues by trying to replicate the same, please feel free to contact so we can give you some help.

      Regards,

Leave a Reply


%d bloggers like this: