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