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.

 
 Subscribe via RSS or Email

Related Posts

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. The 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.

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners

InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners

InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010

InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers

InfoPath 2010 Cookbook 4: 101 Code Recipes for VB Developers

InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services