I am tracking SLA in my app, my problem is my start date needs to be figured by a time stamp and exclude weekends and holidays. For example: If the time stamp is before 12:00 PM it is the same day, after 12:00 pm start date should be next business day. Help! I feel that this should be a pretty easy formula but I have totally confused myself over the last two days.
 110 Points
Posted 10 months ago
Evan Martinez, Community Manager
 8,724 Points
Hi Stacie,
Is this Time stamp an existing field? Then you just need to know the SLA based upon this existing Time Stamp field?
Is this Time stamp an existing field? Then you just need to know the SLA based upon this existing Time Stamp field?
 110 Points
yes, it is an existing field. This will be my start date to determine my 3 day SLA, I need to be able to convert the Date, time stamp to just a date. make sense? For example: 1517 11:05 AM would convert to 1517 or 1517 12:05 pm would convert to 1817 for start date. Need to convert time stamp to single day, excluding weekends and some holidays.
Evan Martinez, Community Manager
 8,724 Points
The first part of checking against the time of day and then if it is before 12 PM setting the SLA to today or if it is after 12 PM setting the SLA to 3 week days in the future can be achieved using a formula like the one below:
If(ToTimeOfDay([TimeStamp])<ToTimeOfDay("12 PM"), ToDate([TimeStamp]), WeekdayAdd(ToDate([TimeStamp]), 3))
Unfortunately, accounting for holidays is going to be much harder. Weekdays and Weekends are differentiated by Quick Base so a formula function like WeekdayAdd will adjust 3 days in the future skipping over weekends but holidays are not a set value in Quick Base. So the formulas will not know for example that 12/25 would be a holiday and should be skipped. Instead the formula logic would need to be hard coded with a reference for each possible holiday outcome and then given different logic. That would then need a new reference for each holiday going forward that would need to be added in. That or the table structure itself would need to be built out with a special table that helps to track and adjust for all holidays. These holidays would then also need to be put in for each year. The holidays aren't going to be as easy as the time check and weekday adjustment.
If(ToTimeOfDay([TimeStamp])<ToTimeOfDay("12 PM"), ToDate([TimeStamp]), WeekdayAdd(ToDate([TimeStamp]), 3))
Unfortunately, accounting for holidays is going to be much harder. Weekdays and Weekends are differentiated by Quick Base so a formula function like WeekdayAdd will adjust 3 days in the future skipping over weekends but holidays are not a set value in Quick Base. So the formulas will not know for example that 12/25 would be a holiday and should be skipped. Instead the formula logic would need to be hard coded with a reference for each possible holiday outcome and then given different logic. That would then need a new reference for each holiday going forward that would need to be added in. That or the table structure itself would need to be built out with a special table that helps to track and adjust for all holidays. These holidays would then also need to be put in for each year. The holidays aren't going to be as easy as the time check and weekday adjustment.
 110 Points
i have my field set up with Date and Time, will this work or is better to add the time stamp field?
Evan Martinez, Community Manager
 8,724 Points
Date/Time field works just fine with this. I just used the name Time Stamp as a stand in for the field.
 110 Points
I keep getting an formula syntax error. The following is the formula.
if(ToTimeOfDay([Order Date]))<ToTimeOfDay("12 PM"),ToDate([Order Date]),WeekdayAdd(ToDate([Order Date]),3))
if(ToTimeOfDay([Order Date]))<ToTimeOfDay("12 PM"),ToDate([Order Date]),WeekdayAdd(ToDate([Order Date]),3))
Evan Martinez, Community Manager
 8,724 Points
Try the formula below. Looks like there is just one parenthesis too many. Let me know if the below formula works better
If(ToTimeOfDay([Order Date])<ToTimeOfDay("12 PM"),ToDate([Order Date]),WeekdayAdd(ToDate([Order Date]),3))
If(ToTimeOfDay([Order Date])<ToTimeOfDay("12 PM"),ToDate([Order Date]),WeekdayAdd(ToDate([Order Date]),3))
(Edited)
 110 Points
Thank you! Got this to work. Now i need help with excluding Holidays. Below is the formula to determine my SLA start date, now i need to exclude the Holidays.
If(ToTimeOfDay([Order Date])<ToTimeOfDay("12 PM"),ToDate([Order Date]),WeekdayAdd(ToDate([Order Date]),1))
I have added an additional table with holiday dates, below is the formula that i have for the date
If([SLA Start Date]<=(ToDate("January 01,2018"))and[Ship Date]>=(ToDate("January 01,2018")),1)
then i combined all holidays to below
Sum(0,[New Year's Day],[Memorial Day],[Independence Day],[Labor Day],[Thanksgiving Day 1],[Thanksgiving Day 2],[Christmas Day 1],[Christmas Day 2])
I cannot figure out the best way incorporate into the first formula, HELP!
If(ToTimeOfDay([Order Date])<ToTimeOfDay("12 PM"),ToDate([Order Date]),WeekdayAdd(ToDate([Order Date]),1))
I have added an additional table with holiday dates, below is the formula that i have for the date
If([SLA Start Date]<=(ToDate("January 01,2018"))and[Ship Date]>=(ToDate("January 01,2018")),1)
then i combined all holidays to below
Sum(0,[New Year's Day],[Memorial Day],[Independence Day],[Labor Day],[Thanksgiving Day 1],[Thanksgiving Day 2],[Christmas Day 1],[Christmas Day 2])
I cannot figure out the best way incorporate into the first formula, HELP!
Related Categories

App builders
 760 Conversations
 41 Followers

Formulas & functions
 2661 Conversations
 58 Followers