Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Set Due Date based on working days

Hi everyone, 

 

I need to calculate a Due Date column based on a given date and working days. 

 

I am working with tickets issue in a given date-time that have to be completed before a due date based on different OLAs. I am having issues trying to calculate the Due Date for those tickets with OLAs 1, 3, 5 and 7 working days.

 

As an example, if I am getting a tickets issued on Wednesday 25/07/2018 11:00 with an OLA of 3 working days, it has to be completed by Monday 30/07/2018 17:00 (Close of business hours).

 

I have seen several post about counting working days between two dates based on a date table, but I don't know how to apply the same logic to get the Due Date column based on a fix number of working days. 

 

Hope you can guide me to the right solution. 

 

Much appreciated.

 

Vaillo

Need Help

6 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Yes, and I have to take into account public holidays as well (by state), so I have a date table with the working days. The issue is that I don't know how to use the table to be able to count 1,3,5 or 7 business dates to get the Due Date I need.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Anonymous,

     

    I'd like to suggest you use countrows with calendar functions to create variable calendar table to calculate working days.


    Calculate column sample:

    working days except weekend =
VAR endDate =
    IF ( Table[End] <> BLANK (), Table[End], TODAY () )
VAR _calendar =
    FILTER ( CALENDAR ( Table[Start], endDate ), WEEKDAY ( [Date], 2 ) <= 5 ) //calendar wihtout weekend
VAR _holidayList =
    VALUES ( Holiday[Date] ) //holiday date list
RETURN
    IF (
        COUNTROWS ( _holidayList ) > 0,
        COUNTROWS ( EXCEPT ( _calendar, _holidayList ) ),
        COUNTROWS ( _calendar )
    )


    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Anonymous,

       

      Thank you for your answer.

       

      That's not exactly what I am looking for. You are calculating the number of working days based in an end date. I need to do the opposite, calculate the end date based on the number of working days.

       

      My scenario is the following: I get jobs issued with different OLAs (1, 3, 5 or 7 working days), so I need to calculate the date when the jobs are due to organise the workforce to meet as many OLAs as possible. So I need to create a calculation that given the date the job was issued and the number of working days of its OLA, I could get the date the job is due:

       

                 Due Date = fx ( Date issued, Number of working Days)

       

      Best regards, 

       

      Vaillo

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi Anonymous,

         

        So you mean you want to calculate max deadline based on current date and working days?


        If this is a case, you can try to use below formula:

         

        Max available deadline =
VAR _workingDays = xxxx
VAR _nonWorkingDays =
    COUNTROWS (
        FILTER (
            CALENDAR ( Table[Start], Table[Start] + _workingDays ),
            WEEKDAY ( [Date], 2 ) > 5
        )
    )
RETURN
    Table[Start] + _workingDays
        + _nonWorkingDays

         

         

        Regards,

        Xiaoxin Sheng