Thursday, September 3, 2009

I want to calculate working time! Good luck buddy..

So many business tasks require calculating future date or time duration between two events but the majority of web based systems are not capable to calculate it right. The reason is nobody takes into account working days and hours. I am going to check if any system out there can handle this issue and how easy the implementation is.

This is initial data I am going to use to calculate working days and hours.

Working Time table

Day Of WeekStart TimeEnd Time
Monday10:00 am6:00 pm
Tuesday10:00 am6:00 pm
Wednesday10:00 am6:00 pm
Thursday10:00 am6:00 pm
Friday10:00 am6:00 pm
Saturday0:00 am0:00 am
Sunday0:00 am0:00 am

If Start Time = End Time this day is considered as a weekend.

Holiday’s table

12/25/2009Christmas Day
11/26/2009Thanksgiving Day
11/11/2009Veterans Day
10/12/2009Columbus Day
9/7/2009Labor Day
6/3/2009Independence Day
5/25/2009Memorial Day
2/16/2009Washington’s Birthday
1/19/2009Birthday of Martin Luther King, Jr.
1/1/2009New Year’s Day

Test 1: End date calculation.

Start DateThe beginning of any process. The date is set by user.
Working Duration (Hours)The duration of the process in hours. Set by user.
End DateThe system calculates the End date taking into account holydays and working time.

On practice it can be used to manage projects or in planning. For example, you need to calculate the end date of anything (working days and hours included).

Test 2: Working time calculation.

Start Date/TimeThe beginning of any process. The date is set by user.
End Date/TimeThe ending of the process. Set by user.
Working Duration (Hours)The system calculates the duration taking into account holydays and work time.

For example, one can control SLA in CRM. Let’s take the case you commit yourself to handle the client request within N business hours.

Well, I understand it’s not such an easy task, that’s why I am asking vendors to tune it in their systems if possible. After that I’ll sum up the results so you could compare the implementation (if any at all) in different systems, how easy it is and what skills are required.


  1. Hi Jane,
    We have created a sample application in Zoho Creator.Please find the application on the below url.


  2. Hi Jane,
    We already have a sample application posted on our site (absence request form - 1st one) that calculates working days, as well as calendar days - see

    Just download the application locally and then import it in your PerfectForms free trial account:

    The sample doesn't currently include a formula to exclude holidays but I will get that added for you.

    You can also see some of the other date/time functionality in this interactive example: