A real life could be more complicated than TrackVia could think

In the ideal world all the people would speak the same language, use the same alphabet and work Monday through Friday 8 hrs a day with a lunch break. In such an ideal world TrackVia would be a good tool for a working time calculation.

First of all, a special thanks to Matt Strenz, TrackVia Customer Support Engineer who created a sample database for me.

"We’ve added a database in your account that is using a calculated field to determine the number of working hours a date range would contain taking into consideration a 5 day work week of 9 hours a day and all the holidays you had mentioned."

Here are Matt’s recommendations how to implement end date calculation (Test 1):

"The first test you mention about creating an end date from the hours entered would be a more difficult calculation to do as this would require looping through the days to determine if they are a weekday or holiday and then to ignore them if so. Because this is a more complex issue it would have to be handled with a full programming language to allow for loops and variables. We built a simplified version in a database called End Date Calculation that you can review to see that this is using a dateadd() formula we have created which allows adding years, months, days, hours, or even minutes to an existing date field."

Here is how the database looks from the setup mode:


Here are calculation results:


Matt also explained how to implement duration calculations (Test 2):

"This is done using a weekdays() formula TrackVia offers which calculates the number of weekdays between two dates and datedif() to determine if there is a holiday that falls between the two entered dates. This would allow for a person to enter a starting and ending date for a project but would not take into consideration the times of day. The formula used can easily be updated to reflect a shorter or longer working day as well as changing the dates that a holiday will fall on."

Here is how the database looks from the setup mode:


Here is a complete formula that was used for the Duration Days calculation for your review:

weekdays(Start Time, End Time) - if(and(datedif(Start Time, year(Start Time)&"-12-25")>=0,datedif(End Time, year(End Time)&"-12-25")<=0),1,0) - if(and(datedif(Start Time, year(Start Time)&"-11-26")>=0,datedif(End Time, year(End Time)&"-11-26")<=0),1,0) - if(and(datedif(Start Time, year(Start Time)&"-11-11")>=0,datedif(End Time, year(End Time)&"-11-11")<=0),1,0) - if(and(datedif(Start Time, year(Start Time)&"-10-12")>=0,datedif(End Time, year(End Time)&"-10-12")<=0),1,0) - if(and(datedif(Start Time, year(Start Time)&"-09-07")>=0,datedif(End Time, year(End Time)&"-09-07")<=0),1,0) - if(and(datedif(Start Time, year(Start Time)&"-06-03")>=0,datedif(End Time, year(End Time)&"-06-03")<=0),1,0) - if(and(datedif(Start Time, year(Start Time)&"-05-25")>=0,datedif(End Time, year(End Time)&"-05-25")<=0),1,0) - if(and(datedif(Start Time, year(Start Time)&"-02-16")>=0,datedif(End Time, year(End Time)&"-02-16")<=0),1,0) - if(and(datedif(Start Time, year(Start Time)&"-01-19")>=0,datedif(End Time, year(End Time)&"-01-19")<=0),1,0) - if(and(datedif(Start Time, year(Start Time)&"-01-01")>=0,datedif(End Time, year(End Time)&"-01-01")<=0),1,0)

Here are calculation results:


Matt came to the following conclusions:

"Other date formulas available are datesub() which is the reverse of dateadd(), datetimedif() and datedif() which return the difference in either time or days, and weekday() which returns the day of the week a date falls on. Combining these formulas using if-then-else logic also available allows for all kinds of complex calculations to be made. We hope that from this you can see how complex calculations can be done in TrackVia and this will show how we have a wide range of prebuilt formulas to use for solutions to standard business problems."

My own conclusions:

Good news first: Looking at the "Duration Days" calculation formula, I think, nobody would say that TrackVia can’t perform complex calculations.

Bad news is that the system is still not able to calculate exactly what we needed. It looks like Test 1 had failed because the system is not able to calculate End Date while accounting working days (not even talking about hours). There are also some problems in the way durations were calculated for the Test 2. The main problem is that the function weekdays() is not designed for worldwide usage. If you are a TrackVia user from Israel, for example, the system would give you incorrect results because Sunday is a working day there.

As you can see, if you are going to add "working days and time support" functionality to your TrackVia application, you may need to research this system’s capabilities as well as its shortcomings to avoid surprises later on.