Forum Discussion
PaulPeterson1
5 years agoQrew Assistant Captain
I'm sure there is a more elegant solution, but this formula works for calculating the working days in the month.
6 - DayOfWeek(ToWeekdayN(Date)) +
(((ToDays(PrevDayOfWeek(LastDayOfMonth(Date), 6) - NextDayOfWeek(LastDayOfWeek(Date), 0)) + 1)/7) * 5) +
If(IsWeekday(LastDayOfMonth(Date)),6 - DayOfWeek(ToWeekdayN(LastDayOfMonth(Date))),0)
The first line will calculate the work days in the first week, the third will calculate the number of working days in the last week of the month and the middle line will calculate the number of weeks left in the month and calculate the working days.
I hope this helps.
------------------------------
Paul Peterson
------------------------------
6 - DayOfWeek(ToWeekdayN(Date)) +
(((ToDays(PrevDayOfWeek(LastDayOfMonth(Date), 6) - NextDayOfWeek(LastDayOfWeek(Date), 0)) + 1)/7) * 5) +
If(IsWeekday(LastDayOfMonth(Date)),6 - DayOfWeek(ToWeekdayN(LastDayOfMonth(Date))),0)
The first line will calculate the work days in the first week, the third will calculate the number of working days in the last week of the month and the middle line will calculate the number of weeks left in the month and calculate the working days.
I hope this helps.
------------------------------
Paul Peterson
------------------------------
PaulPeterson1
5 years agoQrew Assistant Captain
I thought about this a little further and have a lightly cleaner version:
ToDays(ToWeekdayP(LastDayOfMonth(Date d)) - ToWeekdayN(Date d)) - Int(ToDays(ToWeekdayP(LastDayOfMonth(Date d)) - ToWeekdayN(Date d))/7)*2
This will not include the current date as a working day. If you want the current date included:
(ToDays(ToWeekdayP(LastDayOfMonth(Date d)) - ToWeekdayN(Date d)) - Int(ToDays(ToWeekdayP(LastDayOfMonth(Date d)) - ToWeekdayN(Date d))/7)*2) + 1
The one item that this will not handle is if there are any holidays in the month.
------------------------------
Paul Peterson
------------------------------
ToDays(ToWeekdayP(LastDayOfMonth(Date d)) - ToWeekdayN(Date d)) - Int(ToDays(ToWeekdayP(LastDayOfMonth(Date d)) - ToWeekdayN(Date d))/7)*2
This will not include the current date as a working day. If you want the current date included:
(ToDays(ToWeekdayP(LastDayOfMonth(Date d)) - ToWeekdayN(Date d)) - Int(ToDays(ToWeekdayP(LastDayOfMonth(Date d)) - ToWeekdayN(Date d))/7)*2) + 1
The one item that this will not handle is if there are any holidays in the month.
------------------------------
Paul Peterson
------------------------------