Skip to main content
Question

Working days left in the month

  • May 4, 2020
  • 0 replies
  • 47 views
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.

I currently have a tasks table where I store the amount of effort per task, with start date and end date of those tasks.


I am trying to figure out how I:

1. Calculate the remaining work week working days left (i.e. right now in May we have 20 working days left).
2. Leverage my start date and end date + hours to determine if the hours applied will consume this month (OR) subsequent months. 
3. Make a graph of utilization for next 3 months accordingly.


Any help would be greatly appreciated!


Andrew

------------------------------
Andrew Andrew
------------------------------
This topic has been closed for replies.

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
------------------------------
Terms & ConditionsAccessibility statement