Good day everyone,

I need some advice with Zoho Analytics.

I am trying to get a report that will show the work hours of a user.

We are using Zoho People timesheet/time logs.

I connected Zoho People and Zoho Analytics, and everything is working fine.

So, what I need to do is the following.

I need to calculate the hours in 4 different time.

It goes like this.

23:00 to 7:00 7 Monday to Sunday the user gets 1.50 more per hour.

7:00 to 23:00 Saturday and Sunday the user gets 1.00 more per hour.

18:00 to 23:00 Monday to Friday the user gets 0.75 more per hours.

So, looking at the time log table I run into issues.

The first one is that the from time and to time are strings. Can’t figure out how to get them in time HH:mm. So, what I did for test purposes is change them into decimal to do the maths and was kind of able to get what I need.

1.  if((to_decimal((concat( substring_before( "From Time",':'))))+"Hours") > 23 OR to_decimal((concat( substring_before( "From Time",':'))))<7,if((to_decimal((concat( substring_before( "From Time",':'))))+"Hours") > 23,8-(31 -(to_decimal((concat( substring_before( "From Time",':')))+"Hours"))),0),0)

1. if((to_decimal((concat( substring_before( "From Time",':'))))+"Hours")>18  AND day_of_week( "Date") >1 AND day_of_week("Date" ) < 7, if((to_decimal((concat( substring_before( "From Time",':'))))+"Hours")<=23,5-(23 -(to_decimal((concat( substring_before( "From Time",':'))))+"Hours")),if((to_decimal((concat( substring_before( "From Time",':'))))+"Hours")>=23,(23 - to_decimal((concat( substring_before( "From Time",':')))))-5,0)),0)

Now the issue is that adding time as number doesn’t work cause people could get stuff like 5:78 and well that would not compute. Also having to manage doing decimal all the time makes it more complicated.

Also, I saw that the entry if you start working on Friday and end up on Saturday the date doesn’t change in the entry log. 

Because at the end in the report always need to make sure that the biggest prime is the one being applied.

So, if you work Saturday, you get the 1$ for the day but when the night turn in those needs to be calculated with the 1.50.

So, I guess if I can manage to get the all the time in the 3 columns, I could always just do weekend – night but what if he only works 2 hours during the day and 7 during the nights.

The table also doesn’t have any place for pause and can’t seem to be able to ad fields in the time log form.

If any of you guy’s out there have any advice or things to help me sort this out, I would gladly appreciate it.