InfoPath Q&A: How do I subtract lunch hours from time calculations?

Learn how to use InfoPath functions and rules to calculate differences in time and subtract hours for lunch from that.

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.

Related Posts

• Extracting day, month, and year numbers from dates in InfoPath
• InfoPath Tip: InfoPath format date function
• InfoPath Video Tutorial: Copy SharePoint tasks to InfoPath form fields
• InfoPath Q&A: How do I avoid getting NaN when using the max() function?
• Autonumber repeating table rows in InfoPath 2010

Copyright: This article may not be used on web sites (whether personal or otherwise), copied, disseminated, altered, printed, published, broadcasted, or reproduced in any way without an expressed written consent of S.Y.M. Wong-A-Ton. Usage of techniques demonstrated in this article may be used within any Microsoft InfoPath project. This article is provided without any warranties. Copyright for this article is non-transferrable and remains with the author, S.Y.M. Wong-A-Ton.