Forum Discussion

AndrewGaubatz's avatar
AndrewGaubatz
Qrew Member
5 years ago

Order TAT / SLA tracking

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
------------------------------
Formulas and functions
  • BlakeHarrison's avatar
    BlakeHarrison
    Qrew Captain
    5 years 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/
    ------------------------------
    • AndrewGaubatz's avatar
      AndrewGaubatz
      Qrew Member
      5 years ago
      That worked perfectly. Thank you so much!!

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