Zoho Creator – write a program to calculate working time

If you like challenges and are not afraid of some programming - then Zoho Creator would help you implementing your tasks with no compromises.

First of all, special thanks to Mr. Gaev for detailed explanations and to Nandhini from Zoho Creator team for the sample application implementation.

Here is a sample data that is going to be used for working day and hours’ calculation:

Working Hours table:


Holidays’ table:


Naturally, Zoho Creator doesn’t have a ready-made function to process these tables and calculate working time, but Deluge Script allows creating a User-defined Function to perform this job.

The main function that is going to be used for End Date and Duration calculation is getWorkingHours. It calculates number of working hours in any particular working day, and it accounts holiday schedule as well. Here is how it looks:

int getWorkingHours(date date)
{
    working_hours = map();
    for each daily_hrs in Working_Hours  [ID != 0]
    {
        working_hours.put(daily_hrs.DayOfWeek.toString(), daily_hrs.Total_Hrs);
    }
    holidays_list = List();
    for each holidays in Holidays  [ID != 0]
    {
        holidays_list.add(holidays.Date_field);
    }
    if (holidays_list.contains(input.date))
    {
        return 0;
    }
    else
    {
        dayofWeek = input.date.getDayOfWeek();
        return (working_hours.get(dayofWeek.toString())).toLong();
    }
}

Now we may calculate End Date (Test 1):

date getEndDate(date start_date, int start_duration, int total_duration)
{
    duration = thisapp.getWorkingHours(input.start_date);
    input.start_duration = (input.start_duration  +  duration);
    if (input.start_duration  <  input.total_duration)
    {
        new_start_date = input.start_date.addDay(1);
        input.start_date = thisapp.getEndDate(new_start_date, input.start_duration, input.total_duration);
    }
    return input.start_date;
}

Here is this function output:


A similar function may be added to calculate Duration (Test2):

int getTotalWorkingHrs(date start_date, date end_date, int duration)
{
    if (input.start_date  <=  input.end_date)
    {
        date_duration = thisapp.getWorkingHours(input.start_date);
        total_duration = (input.duration  +  date_duration);
        new_start_date = input.start_date.addDay(1);
        input.duration = thisapp.getTotalWorkingHrs(new_start_date, input.end_date, total_duration);
    }
    return input.duration;
}

Here is the second function output:


As you can see, both tests were implemented successfully; however some programming was required, and I am not sure that a business user would be capable to do that. Fortunately, there is Zoho Marketplace and Forum where novice Users can ask for help implementing such functionality.

Conclusions

Zoho Creator proofed to be able to calculate End Date and Duration, correctly accounting working days and hours. I had no doubts in this tool even before starting our tests since "Deluge Script", which Zoho is based on, is actually nothing less than a programming language.

The only question that remains is: Who is he, ideal Zoho Creator customer?