Zoho has a number of Deluge functions that give the difference between two dates. For example, there is workDaysBetween, and yearsBetween. It does not have a function for figuring the number of minutes between two datetime variables. At first glance, this would seem a simple problem, but in the following use case it turned out to be a bit of a challenge.

Use Case.

The client wanted to keep track of how long a series of pre-defined tasks took. The implementation was to update the module with the current time, in the appropriate timezone. Then when the “user” closes the task, determine how many minutes it took. There were 17 different types of activity to be tracked separately. It was not enough to time every task type and aggregate the minutes of them all. The minutes for each task type had to be tracked separately.

The simplest way to handle this was to include three fields in the module, one to record when the task was started (Start Datetime), when it was completed (End Datetime) and a formula field to calculate the difference. When updating the datetime fields from Deluge it was a simple matter of getting the appropriate timezone base value, like this: zoho.crm.updateRecord(“Module”, id,{“Start_Datetime”:zoho.currenttime.toString(“yyyy-MM-dd’T’HH:mm:ss-07:00”)});. And later on do the same with the End_Datetime. Then the formula field was able to calculate the number of minutes like this: Floor(Datecomp(${MyModule.End_Datetime},${MyModule.Start_Datetime})).

This worked well but I ran out of formula fields. What to do?

I decided on using a simple numeric field for the excess of the 15 formula fields I had used (CRM allows 40 numeric fields per module). I still had plenty of date fields left so I was able to capture the start time and end time, but I needed Deluge script to calculate the number of minutes. I thought at first it would be a simple task and tried a number of things unsuccessfully. My challenges were working with what functions Zoho has for manipulating time and handling timezones. It seemed no matter what I tried I could not get the times and timezones right. (Incidentally, I am in California, and this particular client resides in Mountain time.) In the end, it came down to the following.

The Script

moduleRecord = zoho.crm.getRecordById("Module", moduleID);
dateTimeString=now.toTime().toString("MM-dd-yyyy hh:mm:ss a");
denverTime = toTime(dateTimeString,"MM-d-yyyy hh:mm:ss a","America/Los_Angeles").addHour(1);
diff = denverTime.toTime("yyyy-MM-dd HH:mm:ss") - moduleRecord.get("The_Task_Type_Start").toTime("yyyy-MM-dd'T'HH:mm:ss");
minutes = floor(diff/1000/60);

Get the module record that contains the start time. Create a variable with the current time, and convert it to Mountain time. I did this by getting Zoho’s server time, converting it to my time (American/Los_Angeles), and adding an hour, because in this case, Mountain time is one hour ahead of Pacific time. (Caution, this would not apply to states like Arizona which do not use daylight savings time. You would have to adjust accordingly.) Once I had Denver time it was just a matter of subtracting the start time from the end time, giving my milliseconds, and converting to minutes. I floored the results, throwing away fractional minutes for my use case.