Forum Discussion

AndrewWilber's avatar
AndrewWilber
Qrew Trainee
5 years ago

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.

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
------------------------------
Formulas and functions

2 Replies

Sort By
  • PaulPeterson1's avatar
    PaulPeterson1
    Qrew Assistant Captain
    5 years ago
    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
    ------------------------------
    • PaulPeterson1's avatar
      PaulPeterson1
      Qrew Assistant Captain
      5 years ago
      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
      ------------------------------