Hi @WarrenBelz - Thanks for tagging me and for linking my post. You're correct - most of these solutions are not designed to work with negative values.
The general technique relies on us calculating the number of working days based on full weeks, and to make an adjustment based on the number of partial days. The places where we 'hardcode' these additions would need to be subtractions in the case of where we want to subtract working days. Where we calculate the partial days, rather than calculate the number of days to the weekend, we need to calculate the number of days from the weekend since we're now counting backwards.
@EdouardVH - To cope with negative values, my initial thought is to base the calculation on the absolute positive value, and to introduce if statements to account for the negative variations. The code would look like this:
With({weekDaysToAdd: -18},
With(
{ weekDaysToAddAbs: Abs(weekDaysToAdd),
startDate:Date(2021,11,30) },
With(
{
fullWeeks: RoundDown(weekDaysToAddAbs / 5, 0),
remainingDays: Mod(weekDaysToAddAbs, 5),
daysToWeekend:
If(weekDaysToAdd<0,Weekday(startDate, StartOfWeek.Monday)
,5- Weekday(startDate, StartOfWeek.Monday))
},
With(
{ daysToAdd: fullWeeks * 7 + remainingDays + If(remainingDays > daysToWeekend, 2, 0) },
DateAdd(startDate, If(weekDaysToAdd<0,-1,1)*daysToAdd )))
)
)
This produces the following result:
This verifies the same formula against your original question of +45 days.
Because @CarlosFigueira 's formula was neater, it was easier to add these variations to his formula so credit goes to Carlos.
To make the adjustment for vacation days, you can count the number of days and add that as a positive value to weekDaysToAddAbs.
