Rhonda wrote:
I used the following article “Calculating time differences without custom code” for creating an employee timesheet and it works perfect. Thank you so much. I need to add one more field to substract the lunch hour or half hour, so I created a field for the number to be entered, but cannot decide how it goes into the formula. Do you have just a little more to help with this same article?
Hi Rhonda,
Glad to hear that the article was of good use to you. The article performs the calculation in seconds and then in the end converts everything to hours. So if you want to subtract an hour or half an hour for lunch and let’s say you called your lunch field lunch and placed it in the repeating table along with the other fields, then the formula for startTime would look something like
(((number(substring(../my:endTime, 1, 2)) * 3600) + (number(substring(../my:endTime, 4, 2)) * 60)) - ((number(substring(., 1, 2)) * 3600) + (number(substring(., 4, 2)) * 60)) - (../my:lunch * 3600)) div 3600
and the formula for endTime would look something like
(((number(substring(., 1, 2)) * 3600) + (number(substring(., 4, 2)) * 60)) - ((number(substring(../my:startTime, 1, 2)) * 3600) + (number(substring(../my:startTime, 4, 2)) * 60)) - (../my:lunch * 3600)) div 3600
And don’t forget to add a rule with a formula for your lunch field too. The formula would look something like
(((number(substring(../my:endTime, 1, 2)) * 3600) + (number(substring(../my:endTime, 4, 2)) * 60)) - ((number(substring(../my:startTime, 1, 2)) * 3600) + (number(substring(../my:startTime, 4, 2)) * 60)) - (. * 3600)) div 3600
Hope this helps.
Comments to this post were closed 30 days after it was published.