Discussions

Expand all | Collapse all

Working days left in the month

  • 1.  Working days left in the month

    Posted 05-04-2020 13:56
    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
    ------------------------------


  • 2.  RE: Working days left in the month

    Posted 05-05-2020 09:22
    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
    ------------------------------



  • 3.  RE: Working days left in the month

    Posted 05-07-2020 07:24
    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
    ------------------------------