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:
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.
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.
This post first appeared on Web Based Database Software At Work, please read the originial post: here
