Forum Discussion

Uspace87's avatar
Uspace87
Icon for Resolver III rankResolver III
3 years ago
Solved

Adding Working days to Order Created date

Hi everyone,

 

I would like to calculate a Shipping Date (Deadline) for WebOrders adding the Shipping condition and skipping not working days like Weekend and Public Holidays. 

 

 

 

Order Created DateShipping ConditionShipping Date
23/12/202248hrs  

 

In the Example above the order has been create on 23/12/2022 which is Friday so in this case I can consider 24hrs for the 23rd, then I will have to skip 24/12-25/12-26/12-/27/12 because them are public holidays so the actual Shipping date should be 28/12 EOD.

 

Any help please?

 

 

"Need Help"
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi Uspace87 

    Not sure if i fully get your,  you may try to add a column like this:

    EOD - NextWorkingDay = 
VAR _date = [OrderDate]
RETURN
 MINX(
    FILTER(
        data,
        data[OrderDate]> _date
            && NOT WEEKDAY(data[OrderDate], 2) IN {6, 7}
            && NOT [OrderDate] IN VALUES(Holiday[Holidays])
    ),
    data[OrderDate]
)

     

    tried and it worked like this:

    the holiday table:

     

3 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Uspace87 

    Not sure if i fully get your,  you may try to add a column like this:

    EOD - NextWorkingDay = 
VAR _date = [OrderDate]
RETURN
 MINX(
    FILTER(
        data,
        data[OrderDate]> _date
            && NOT WEEKDAY(data[OrderDate], 2) IN {6, 7}
            && NOT [OrderDate] IN VALUES(Holiday[Holidays])
    ),
    data[OrderDate]
)

     

    tried and it worked like this:

    the holiday table:

     

    • Uspace87's avatar
      Uspace87
      Icon for Resolver III rankResolver III
      3 years ago

      That is brilliant thank you, I had to add some other logic but that formula is perfect! Thank you very much!