Working days left in the month
Hello, I am trying to build a report where I look at my teams utilization and show how much they can get done in the remainder of the month and if I have given them enough tasks to fill that time. ...
Forum Discussion
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
------------------------------
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
------------------------------