QuickBase is one of the most mature products of the online database market. One can think with years of experience and such a huge client base there is no issue the system can’t solve. That’s why it was quite a surprise that the task to calculate working time with working days and hours was a hard nut to crack.This example is prepared with Kirk Trachys help and I’d really like to thank for the assistance. He commented on the implementation peculiarities of End Date (Test 1) calculation taking into account hours of operation and holidays in QuickBase:
"QuickBase has some native field types and formula functions that address the calculations of dates, workdates and weekdays. Many calculations can be as simple as: WeekDayAdd([Start Date],[Business Days]). Where this takes a start date and you add the number days to it and it only calculates for Monday through Friday days. The result will jump over weekends and resume the next week if necessary. In the calculations below we created a field to accept a Start Date (01-01-2009) and another to accept the number of hours a task might take (120). Since each weekday was 8 hours we calculated the number of days by making them divisible by 8 hours resulting in (15) Business Days."
"Now that we have calculated the business days we need to account for the holiday schedule so we add the holiday dates (see table below) and then calculated if there were any holiday dates that should be added to the calculation. We calculate this by totaling how many times a holiday is present between the [Start Date] and the [Start Date Plus Business Work Days]. This holiday count is added to the business days and applied as a total to the WeekDayAdd function. The End Date then reflects both days based on hours as well as any holidays. Below is the same form as above but with the formulas displayed as text to the right of each field:"
"All of this was calculated in one table. When calculating date ranges that bridge over multiple year periods one will want to use a relationship to multiple records."
"QuickBase provides over 160 formula functions and operators with six SDKs and an open HTTP XML API."
Conclusions
From Kirks description and the app we can see "Hours of Operation" info is not used during the calculation. So I assume, variable hours of operations and the weekends different from Saturday and Sunday can't be implemented with built-in QuickBase functionality.
Even though Kirk didn’t manage to calculate duration between two timestamps (Test 2) I think it’s possible in QuickBase, but only in case we always have 8 hours working day with Saturday and Sunday as weekends.
The situation is the same as with TrackVia: WeekDayAdd function can’t be applied worldwide and count the data as needed and built-in QuickBase functionality doesn’t allow to implement this calculation without it, using hours of operation info.
Unfortunately 160 formula functions and operators can’t help you to calculate working time and you will have to do some coding through one of six SDKs or HTTP XML API.
I actually ended up setting up a similar model in Coghead for a Title Company. They have very specific deadline rules for completing loan closings that include Sundays off, sometimes Saturdays and Holidays.
ReplyDeleteI have managed to do some amazing trickery with QuickBase dynamic relationships but this is a task that does not seem achievable through lookups, formulas and summaries alone. Some fairly simple Javascript in QuickBase could deliver the result though.
Govind, I agree that the script capable of calculating this is not that difficult, as Zoho Example shows. On the other hand we can do this through relations, formulas and summary as in TeamDesk for instance.
ReplyDeleteActually, the goal of all these tests is not to show something the product can’t do, but to point the scope where the user must do some coding or scripting.
maybe you would like to test tackling the situation with HyperBase, HyperOffice's recently launched online database tool.
ReplyDelete