Discussions

Expand all | Collapse all

Order TAT / SLA tracking

  • 1.  Order TAT / SLA tracking

    Posted 12 days ago
    Afternoon,

    I have a scenario that I'm having trouble finding the right method / formula to tackle.

    Key points:
    • I have orders that are taken 7 days a week.
    • The TAT for Order - > Ship should be within 2 business (week)days
      • Orders are only shipped M-F.
    • If an order is received before 1pm on a weekday, the order is processed same day, and shipped next weekday.
    • If an order is received after 1pm on a weekday, the order is processed the next weekday, and shipped the next weekday after that.
    I have:
    • a Date/Time field, [Order Date] that records the exact date and time the order is placed
    • a Date field, [Ship Date] that records the exact date the order was shipped
    • a Formula - Date field, [Ship Due Date], to calculate what day the order should ship
    • a Formula - Duration field, [Process Time], to calculate the time between the Order date and Ship date

    What I need help is figuring out the formula(s) to calculate the Ship Due date, based on the Date/Time [Order Date] field and the key points above. Once I have that, I think I can figure out how to apply the same formula(s) to calculate the actual process time from Order to Ship using the same rules.

    Any help would be greatly appreciated - definitely in the deep end of the pool on this one.

    ------------------------------
    Andrew Gaubatz
    ------------------------------


  • 2.  RE: Order TAT / SLA tracking

    Posted 12 days ago
    Andrew -

    You first need to determine if it was received before or after 1pm. For that, you'll use the HOUR function against your Timestamp field, which will result in a number representing the hour in a 24hr clock. 

    Hour([Order Date])

    Then, if it's before 1pm, you'll have to calculate the next weekday using the WeekdayAdd function.

    WeekdayAdd(ToDate([Order Date]),1)

    If it's after 1pm, you'll have to calculate 2 weekdays out using the same function.

    WeekdayAdd(ToDate([Order Date]),2)

    Altogether, the formula would look something like this:

    IF(Hour([Order Date])>=13, WeekdayAdd(ToDate([Order Date]),1), WeekdayAdd(ToDate([Order Date]),2))

    ------------------------------
    Blake Harrison
    bharrison@datablender.io
    DataBlender - Quick Base Solution Provider
    Atlanta GA
    404.800.1702 / http://datablender.io/
    ------------------------------



  • 3.  RE: Order TAT / SLA tracking

    Posted 12 days ago
    That worked perfectly. Thank you so much!!

    ------------------------------
    Andrew Gaubatz
    ------------------------------