Calculating Elapsed Time…without code!


UPDATE: Due to the number of requests for adding more columns to this sample, I have re-designed the sample. If I have time in the future I will update the steps to create this new design; however, for now you can download the updated design here to see the changes. The expressions are very similar to the original design but I now use a repeating table for entering “break” times so you can have as many as you need! 


How many times have you needed to calculate the difference between time entries…only to find out the only way to accomplish this was to write custom code? Well no more! If your time entries will not cross days, then you can use a number of functions and expressions to parse the entered times and calculate the difference. In the sample below we will show you the types of expressions that are needed for these calculations to work – I would encourage you to first create this sample as documented as the expressions are not for the “faint of heart!” 🙂


Note: if you choose to copy/paste the expressions for this sample, you will use the Default Value box for each field and after clicking the “fx” button, be sure to also enable the “Exit XPath” option before you paste these expressions.

If you’d like to take a look at the completed version of this sample, here is the .xsn file – make sure to save it locally before opening it.

Create the data structure

1) Add a Repeating Table to your View with 4 columns


2) Rename the fields and specify the data types as follows, from left to right:



















Name Data Type
myDate Date (date), Format: *3/14/2001
StartTime Time (time), Format: *9:46 AM
EndTime Time (time), Format: *9:46 AM
ActualTime Text (string)


3) Change the first field in the table to a Date Picker control


 
4)  Add 2 additional fields to the Repeating Group node (these will not be shown on the view – they are used to aid in the calculations)













Name Data Type
ElapsedTime Text (string)
TotalMinutes Decimal (double)



5) Add one final field to calculate the sum of all the entries – this should be added to the myFields node…not the Repeating node










Name Data Type
TotalTime Text (string)


Your final data structure should look like this:



And your form could look like this:



 


Adding the expressions…let the fun begin!

The first step is to convert the StartTime and EndTime values to minutes – this makes the calculation a bit easier. In addition, we don’t want to execute this calculation if either the StartTime or EndTime is blank. So to perform this “conditional statement”, we’ll use the logic explained in this blog entry.


Step 1: Parse the Hours and Minutes

1) We first need to parse the “hours” and convert this to minutes by multiplying that value by 60. To do this we will use the “substring-before” function to look for the colon (“:”) in the time field and extract the value before the colon:



substring-before(../my:EndTime, “:”) * 60)
substring-before(../my:StartTime, “:”) * 60


2) To each of these values, we need to add the minutes that were entered after the colon. Now when time values are stored in the underlying XML, they are stored in this manner: hh:mm:ss. So to pull just the minutes entered, we will use a combination of the “substring-before” and “substring-after” functions since we need the value entered “after” the first colon and “before” the last colon:



substring-before(substring-after(../my:EndTime, “:”), “:”)
substring-before(substring-after(../my:StartTime, “:”), “:”)


3) Now, at this point we could place each of these expressions together to get the total hours and minutes for each time entry:



((substring-before(../my:EndTime, “:”) * 60) + substring-before(substring-after(../my:EndTime, “:”), “:”))
((substring-before(../my:StartTime, “:”) * 60) + substring-before(substring-after(../my:StartTime, “:”), “:”))


4) Keep in mind, we don’t want this expression to execute if either the StartTime or EndTime fields are empty and we also need to subtract the StartTime total minutes from the EndTime total minutes. So to do this we will use the “substring” function in conjunction with a “condition.” The substring function has the following signature:



substring([String Value], [Starting Position], [Condition/Length])


To incorporate our expressions into the substring function, it would look like this:



Substring([EndTime – StartTime], 1, EndTime != “” and StartTime != “”)


EndTime expression:



((substring-before(../my:EndTime, “:”) * 60) + substring-before(substring-after(../my:EndTime, “:”), “:”))


StartTime expression:



((substring-before(../my:StartTime, “:”) * 60) + substring-before(substring-after(../my:StartTime, “:”), “:”))


Starting position: 1


Condition expression (that if true, evaluates to the length of what we want to return so we use the “string-length” function to get the length of our initial expression):



(../my:StartTime != “” and ../my:EndTime != “”) * string-length(((substring-before(../my:EndTime, “:”) * 60) + substring-before(substring-after(../my:EndTime, “:”), “:”)) – ((substring-before(../my:StartTime, “:”) * 60) + substring-before(substring-after(../my:StartTime, “:”), “:”)))


So our final expression for the TotalMinutes field would look like this:



substring(((substring-before(../my:EndTime, “:”) * 60) + substring-before(substring-after(../my:EndTime, “:”), “:”)) – ((substring-before(../my:StartTime, “:”) * 60) + substring-before(substring-after(../my:StartTime, “:”), “:”)), 1, (../my:StartTime != “” and ../my:EndTime != “”) * string-length(((substring-before(../my:EndTime, “:”) * 60) + substring-before(substring-after(../my:EndTime, “:”), “:”)) – ((substring-before(../my:StartTime, “:”) * 60) + substring-before(substring-after(../my:StartTime, “:”), “:”))))


 


 


Step 2: Convert resulting Total Minutes to hours and minutes

This expression will use the same basic logic we used above: first divide the resulting total minutes field by 60 to see how many hours have elapsed. If the result of that division contains a decimal point (i.e. 90 minutes / 60 = 1.5) then parse the value before the decimal point (for the hours) and then use the “mod” function to return the balance of the minutes.

1) We need to divide the TotalMinutes field by 60 to get how many hours have elapsed; however, we also need to check if the resulting value contains the decimal point. So the first expression will use the “substring” function so we can incorporate the conditional test. For the conditional test, we will use the “contains” function to see if the result contains the decimal point:



contains(../my:TotalMinutes div 60, “.”)


So our initial expression would be as follows: this incorporates the “concat” function so if the resulting value contains a decimal point, we will parse that value, concatenate a colon and the concatenate the result of TotalMinutes mod 60:



substring(concat(substring-before(../my:TotalMinutes div 60, “.”), “:”, ../my:TotalMinutes mod 60), 1, contains(../my:TotalMinutes div 60, “.”) * string-length(concat(substring-before(../my:TotalMinutes div 60, “.”), “:”, ../my:TotalMinutes mod 60)))


2) If the resulting expression does not contain a decimal point, then we will simply concatenate a colon with the results of TotalMinutes mod 60:



substring(concat(../my:TotalMinutes div 60, “:”, ../my:TotalMinutes mod 60), 1, not(contains(../my:TotalMinutes div 60, “.”)) * string-length(concat(../my:TotalMinutes div 60, “:”, ../my:TotalMinutes mod 60)))


3) So our final expression for the ElapsedTime field would be as follows:



concat(substring(concat(substring-before(../my:TotalMinutes div 60, “.”), “:”, ../my:TotalMinutes mod 60), 1, contains(../my:TotalMinutes div 60, “.”) * string-length(concat(substring-before(../my:TotalMinutes div 60, “.”), “:”, ../my:TotalMinutes mod 60))), substring(concat(../my:TotalMinutes div 60, “:”, ../my:TotalMinutes mod 60), 1, not(contains(../my:TotalMinutes div 60, “.”)) * string-length(concat(../my:TotalMinutes div 60, “:”, ../my:TotalMinutes mod 60))))


 


 


Step 3: Final Formatting – the ActualTime field


The way the expression is written for the ElapsedTime field, if the resulting minutes is less than 10, only a single value will be returned (i.e. 0:9). Because of this, we want to test for this condition and if the minutes are less than 10, then concatenate a zero (“0”) before that value so the time appears correct. Now this expression could possibly have been incorporated into the ElapsedTime expression but for ease and clarity, I separated these two steps into different fields.

This expression uses the “string-length” function to determine the length of the string after the colon. If the length is 2 (minutes are greater than 9) then simply concatenate the hours, a colon and the minutes. However, if the length is 1 (minutes are less than 10) then concatenate the hours, a colon with a zero (“:0”) and the minutes. Like before, we will use the “substring” function with a condition statement to determine what to return:



concat(substring(concat(substring-before(../my:ElapsedTime, “:”), “:”, substring-after(../my:ElapsedTime, “:”)), 1, (string-length(substring-after(../my:ElapsedTime, “:”)) = 2) * string-length(concat(substring-before(../my:ElapsedTime, “:”), “:”, substring-after(../my:ElapsedTime, “:”)))), substring(concat(substring-before(../my:ElapsedTime, “:”), “:0”, substring-after(../my:ElapsedTime, “:”)), 1, (string-length(substring-after(../my:ElapsedTime, “:”)) = 1) * string-length(concat(substring-before(../my:ElapsedTime, “:”), “:0”, substring-after(../my:ElapsedTime, “:”)))))



Step 4: Create the TotalTime expression for keeping a running total of the elapsed time

The last step is to create the TotalTime expression – this is similar to the ActualTime expression except we now incorporate the “sum” function to get the running total:



concat(substring(concat((sum(../my:group1/my:group2/my:TotalMinutes) – sum(../my:group1/my:group2/my:TotalMinutes) mod 60) div 60, “:”, sum(../my:group1/my:group2/my:TotalMinutes) mod 60), 1, (sum(../my:group1/my:group2/my:TotalMinutes) mod 60 > 9) * string-length(concat((sum(../my:group1/my:group2/my:TotalMinutes) – sum(../my:group1/my:group2/my:TotalMinutes) mod 60) div 60, “:”, sum(../my:group1/my:group2/my:TotalMinutes) mod 60))), substring(concat((sum(../my:group1/my:group2/my:TotalMinutes) – sum(../my:group1/my:group2/my:TotalMinutes) mod 60) div 60, “:0”, sum(../my:group1/my:group2/my:TotalMinutes) mod 60), 1, (sum(../my:group1/my:group2/my:TotalMinutes) mod 60 < 10) * string-length(concat((sum(../my:group1/my:group2/my:TotalMinutes) – sum(../my:group1/my:group2/my:TotalMinutes) mod 60) div 60, “:0”, sum(../my:group1/my:group2/my:TotalMinutes) mod 60))))


Step 5: Test!
Enter a Start and End Time in the form of: h:mm AM(PM), such as: 8:00 AM and 5:00 PM – the ActualTime field should display the difference (as 9:00) and if you continue adding rows, the TotalTime field should reflect the running total.


This functionality allows you to create elapsed time scenarios that work in both client and browser forms without writing one line of code!


Scott Heim
Support Engineer

ElapsedTime_NoCode_NewDesign.xsn

Comments (61)

  1. alex_broere says:

    How do I get a extra collum with Break time inserted into this formula? I work in a company where people need to insert 3 things: Begin time, end time and break time. The break time gets substracted from the total time. Is there a code (formula) I have to insert to get this to work? (the break time is mostly only minutes)

    Great blog it works great only the extra collum inserted would be very nice.

  2. Scott Heim says:

    Hi Alex,

    Try these steps…assuming you are using the same data structure as what I have in this post:

    – Make a backup copy of your working form

    – Add a new field to the repeating group named: BreakTime

    – Set the data type of BreakTime to "Time (time)"

    – Add BreakTime to your Repeating Table on the view

    – Set the format of the BreakTime field in the table to: 9:46 and then set the default value to: 0:00

    – In the Data Source Task Pane, display the Properties for the TotalMinutes field

    – Click the "fx" button for the Default Value

    – Enable the "Edit XPath" check box

    – Delete the existing expression and replace it with the following:

    substring((((substring-before(../my:EndTime, ":") * 60) + substring-before(substring-after(../my:EndTime, ":"), ":")) – ((substring-before(../my:StartTime, ":") * 60) + substring-before(substring-after(../my:StartTime, ":"), ":"))) – ((substring-before(../my:BreakTime, ":") * 60) + substring-before(substring-after(../my:BreakTime, ":"), ":")), 1, (../my:StartTime != "" and ../my:EndTime != "") * string-length((((substring-before(../my:EndTime, ":") * 60) + substring-before(substring-after(../my:EndTime, ":"), ":")) – ((substring-before(../my:StartTime, ":") * 60) + substring-before(substring-after(../my:StartTime, ":"), ":"))) – ((substring-before(../my:BreakTime, ":") * 60) + substring-before(substring-after(../my:BreakTime, ":"), ":"))))

    – Click OK to get back to the form and test!

  3. alex_broere says:

    Oh you are good!!! thankyou for the fast response!! It works great….

    Thankyou sooo much!!

    Keep up the good work

  4. ugdstudios says:

    I have used the code you provided but when I use the form to track shifts that have an end time earlier that the start time, it doesnt calculate properly.  Example:

    Shift start : 12:00 Noon  Shift end: 3:00 AM    

    I should receive a total of 15.00 hours, but instead i get -9.00 hours.

    ???

    Any thoughts on how to rectify this problem?

    Also,  I need the totals to be in decimal format rather than time format…ex:   3pm to 9:30pm  gives me 6.50 rather than 6:30 as a total.

    Please advise??

  5. Scott Heim says:

    Hi ugstudios,

    When I created this sample, it was designed for elapsed time calculations that were within one day – the times that you are attempting cross days. As you have seen, the expressions to get this to work within one day are quite extensive – to cross days, it is probaby easier in the long run to use custom code to perform the calculations.

    Regarding displaying the values in decimal format – if users would still be entering time in hh:mm format, then the "TotalMinutes" field contains just the total minutes for that record. You could choose to display that in whatever format you desire.

    Scott

  6. pmdci says:

    Excellent article! Tried it and it really works. Question is, can we calculate elapsed DAYS between two dates without adding any code? That’s worthy of an article, right?! 🙂

    Regards,

    Pedro

  7. infopath says:

    Hi Pedro,

    I am sorry for the delay in responding to your post…this got misfiled. 🙁

    I agree – this would be a good post and if I can find some time to make the changes, I will do so.

    Thanks for the suggestion!

    Scott

  8. PecsIT says:

    Thanks Scott for a fantastic article. My question is: how can you show the Actual Time and Total Time in quarter hours (.25/.50/1.75). We are hoping our infopath timelog will eventually bleed over into our invoicing program which shows our clients the hours in quarter time. Any suggestions will be appreciated.

    Other than that, I did what you wrote and really got the bosses attention this morning! Thanks again!

    Sara/PecsIT

  9. infopath says:

    Hi Sara,

    I think these changes will give you what you want…but I would still make a backup copy of your working form! 🙂

    – Change TotalTime field expression to: sum(../my:group1/my:group2/my:ActualTime)

    – Change ActualTime field expression to:

    concat(substring(../my:TotalMinutes, 1, (../my:StartTime != "" and ../my:EndTime != "") * string-length(../my:TotalMinutes)), substring(0, 1, (not(../my:StartTime != "" and ../my:EndTime != "")) * string-length(0)))

    ** NOTE: If you used the same field names and data structure, you can paste the above expressions after enabling the option "Edit XPath".

    – Change TotalMinutes field expression to:

    substring(((substring-before(EndTime, ":") * 60) + substring-before(substring-after(EndTime, ":"), ":")) – ((substring-before(StartTime, ":") * 60) + substring-before(substring-after(StartTime, ":"), ":")), 1, (StartTime != "" and EndTime != "") * string-length(((substring-before(EndTime, ":") * 60) + substring-before(substring-after(EndTime, ":"), ":")) – ((substring-before(StartTime, ":") * 60) + substring-before(substring-after(StartTime, ":"), ":")))) / 60

    ** NOTE: The only change to the TotalMinutes expression was to add the " / 60" to the end of the existing expression. Be sure to include a space before and after the division operator.

    – Change ActualTime and TotalTime fields to a data type of "Decimal (double)" and set the number of decimal places to 2 (click the Format button to change this option.)

    I hope this works for you!

    Scott

  10. zullu says:

    Hi Scott,

    I was trying to create a FormID using the below format:

    [YYYYMMDDHHMMSS]

    Self explanatory!!!

    This format reads Year(YYYY), Month(MM), Date(DD), Hour(HH), Minutes(MM) followed by Seconds(SS).

    Now to do this, I was thinking of using some functions, wihich could extract the respecting values so that using a formula would give me the desired result:

    CONCAT(YEAR(now()), MON(now()), DATE(now()), HOUR(now)), MIN(now()), SEC(now()))

    Is it possible in InfoPath 2007 ???

    I know this kind of formulae have been used since ages in the microsoft products and I would not see a reason why Microsoft would not continue to support this from the Design mode.

    Any suggestions….

    Thanks in advance.

    Zullu.

  11. infopath says:

    Hi zullu,

    No – those expressions are not supported in the InfoPath Designer; however, you could write custom code, use those expressions, populate a dummy node with the result and use the value in that node for your form name.

    Another option would be to use the various "substring" functions, such as: substring-before and substring-after. As an example, the following expression would return just the year:

    substring-before(now(), "-")

    So you could build your expression in this manner.

    Scott

  12. brooks1856 says:

    Scott,

    great sample form!  This is very close to what I’m trying to accomplish.

    I also need a column for break (lunch) time.  Would it be possible to get some updated expression code showing a Start Break Time and End Break Time in addition to your sample?  I saw this question in a previous comment but we are required to show start and end times for lunch for auditing purposes instead of simply stating how many minutes were spent.  Also I tried changing the total time fields to decimal format but it doesn’t seem to work.  Is there something else I need to do to convert to decimals?

    Thanks again and great work!

  13. infopath says:

    Hi brooks1856,

    Using the same data structure as this post, here is what you would need to do:

    – Make a backup copy of your current form template!

    – Right-click in the End Time cell and chose Insert -> Columns to the right

    – Repeat the above to add another column (you should now have a new "field1" and "field2" in the table)

    – Right-click on field1 and choose Properties

    – Change the name to: StartBreakTime

    – Change the Data Type to: Time

    – Click Format and change the format to: *9:46 AM

    – Right-click on field2 and repeat the above steps naming this field: EndBreakTime

    – In the Data Source Task Pane, right-click on TotalMinutes and choose Properties

    – Click the "fx" button next to Default Value

    – Enable the "Edit XPath" box

    – Delete the existing expression and replace it with the following:

    substring((((substring-before(../my:EndTime, ":") * 60) + substring-before(substring-after(../my:EndTime, ":"), ":")) – ((substring-before(../my:StartTime, ":") * 60) + substring-before(substring-after(../my:StartTime, ":"), ":"))) – (substring(((substring-before(../my:EndBreakTime, ":") * 60) + substring-before(substring-after(../my:EndBreakTime, ":"), ":")) – ((substring-before(../my:StartBreakTime, ":") * 60) + substring-before(substring-after(../my:StartBreakTime, ":"), ":")), 1, (../my:StartBreakTime != "" and ../my:EndBreakTime != "") * string-length(((substring-before(../my:EndBreakTime, ":") * 60) + substring-before(substring-after(../my:EndBreakTime, ":"), ":")) – ((substring-before(../my:StartBreakTime, ":") * 60) + substring-before(substring-after(../my:StartBreakTime, ":"), ":"))))), 1, (../my:StartTime != "" and ../my:EndTime != "") * string-length((((substring-before(../my:EndTime, ":") * 60) + substring-before(substring-after(../my:EndTime, ":"), ":")) – ((substring-before(../my:StartTime, ":") * 60) + substring-before(substring-after(../my:StartTime, ":"), ":"))) – (substring(((substring-before(../my:EndBreakTime, ":") * 60) + substring-before(substring-after(../my:EndBreakTime, ":"), ":")) – ((substring-before(../my:StartBreakTime, ":") * 60) + substring-before(substring-after(../my:StartBreakTime, ":"), ":")), 1, (../my:StartBreakTime != "" and ../my:EndBreakTime != "") * string-length(((substring-before(../my:EndBreakTime, ":") * 60) + substring-before(substring-after(../my:EndBreakTime, ":"), ":")) – ((substring-before(../my:StartBreakTime, ":") * 60) + substring-before(substring-after(../my:StartBreakTime, ":"), ":")))))))

    – Click OK until you are back to the form template

    – Preview and test!

    Scott

  14. infopath says:

    Hi brooks1856,

    Using the same data structure as this post, here is what you would need to do:

    – Make a backup copy of your current form template!

    – Right-click in the End Time cell and chose Insert -> Columns to the right

    – Repeat the above to add another column (you should now have a new "field1" and "field2" in the table)

    – Right-click on field1 and choose Properties

    – Change the name to: StartBreakTime

    – Change the Data Type to: Time

    – Click Format and change the format to: *9:46 AM

    – Right-click on field2 and repeat the above steps naming this field: EndBreakTime

    – In the Data Source Task Pane, right-click on TotalMinutes and choose Properties

    – Click the "fx" button next to Default Value

    – Enable the "Edit XPath" box

    – Delete the existing expression and replace it with the following:

    substring((((substring-before(../my:EndTime, ":") * 60) + substring-before(substring-after(../my:EndTime, ":"), ":")) – ((substring-before(../my:StartTime, ":") * 60) + substring-before(substring-after(../my:StartTime, ":"), ":"))) – (substring(((substring-before(../my:EndBreakTime, ":") * 60) + substring-before(substring-after(../my:EndBreakTime, ":"), ":")) – ((substring-before(../my:StartBreakTime, ":") * 60) + substring-before(substring-after(../my:StartBreakTime, ":"), ":")), 1, (../my:StartBreakTime != "" and ../my:EndBreakTime != "") * string-length(((substring-before(../my:EndBreakTime, ":") * 60) + substring-before(substring-after(../my:EndBreakTime, ":"), ":")) – ((substring-before(../my:StartBreakTime, ":") * 60) + substring-before(substring-after(../my:StartBreakTime, ":"), ":"))))), 1, (../my:StartTime != "" and ../my:EndTime != "") * string-length((((substring-before(../my:EndTime, ":") * 60) + substring-before(substring-after(../my:EndTime, ":"), ":")) – ((substring-before(../my:StartTime, ":") * 60) + substring-before(substring-after(../my:StartTime, ":"), ":"))) – (substring(((substring-before(../my:EndBreakTime, ":") * 60) + substring-before(substring-after(../my:EndBreakTime, ":"), ":")) – ((substring-before(../my:StartBreakTime, ":") * 60) + substring-before(substring-after(../my:StartBreakTime, ":"), ":")), 1, (../my:StartBreakTime != "" and ../my:EndBreakTime != "") * string-length(((substring-before(../my:EndBreakTime, ":") * 60) + substring-before(substring-after(../my:EndBreakTime, ":"), ":")) – ((substring-before(../my:StartBreakTime, ":") * 60) + substring-before(substring-after(../my:StartBreakTime, ":"), ":")))))))

    – Click OK until you are back to the form template

    – Preview and test!

    Scott

  15. mcmurrayjl says:

    This is wonderful, and is exactly what I needed.  I have made a few modifications on it to customize it to my needs, and it is working perfectly.  However, with the addition of the new break time in the last segment, it will no longer just calculate time in and time out (if the employee doesn’t take a lunch that day).  I am feverishly learning xpath, but I am getting tied into knots on the math.  Which part do I need to add/subtract to leave the break time optional so the other fields will add up on their own?  Also, what if there is an "other" time in/out that needs to be calculated for events such as travel?  You guys are great in this forum, and has added tremendous tool to my InfoPath programming!

  16. infopath says:

    Hi mcmurrayjl,

    Let me start with your 2nd question:

    – "What if there are other time in/out needs?"

    > In this case, I would suggest you look at changing the design to make it more "relational" – for example: "1 day can have 0 or more time in/out entries." Basically it would be a 1->many design. To stay with the design shown here in this sample would be difficult at best.

    – "How can I get this sample to calculate the elapsed time with or without the break time being entered?"

    > Ok – so this gets a little more involved and I think the best way to answer this is to first show how I arrive at the final expression for the "TotalMinutes" node.

    Let’s begin by breaking down each part of the expression:

    – Part 1: Calculate the difference between only the start and end times entered:

    (((substring-before(../my:EndTime, ":") * 60) + substring-before(substring-after(../my:EndTime, ":"), ":")) – ((substring-before(../my:StartTime, ":") * 60) + substring-before(substring-after(../my:StartTime, ":"), ":")))

    – Part 2: Calculate the difference between only the start and end "break times" entered:

    (((substring-before(../my:EndBreakTime, ":") * 60) + substring-before(substring-after(../my:EndBreakTime, ":"), ":")) – ((substring-before(../my:StartBreakTime, ":") * 60) + substring-before(substring-after(../my:StartBreakTime, ":"), ":")))

    So at this point, you could replace the expression in the TotalMinutes node with either of these above and they would produce the expected results.

    Now, let’s bring the 2 parts above together so we are basically at where you are now – needing all fields completed:

    (((substring-before(../my:EndTime, ":") * 60) + substring-before(substring-after(../my:EndTime, ":"), ":")) – ((substring-before(../my:StartTime, ":") * 60) + substring-before(substring-after(../my:StartTime, ":"), ":"))) – (((substring-before(../my:EndBreakTime, ":") * 60) + substring-before(substring-after(../my:EndBreakTime, ":"), ":")) – ((substring-before(../my:StartBreakTime, ":") * 60) + substring-before(substring-after(../my:StartBreakTime, ":"), ":")))

    As you know, at this point you need both the start/end times and the start/end "break" times entered. So how do we modify this to calculate regardless of whether someone entered start/end "break" times. This is where the logic comes in from the link I referenced in the original post to provide us with "if, then, else" logic.

    The expression that we use for this is as follows:

    concat(

    substring(TrueResult, 1, (BoolCondition) * string-length(TrueResult)),

    substring(ElseResult, 1, (not(BoolCondition)) * string-length(ElseResult)))

    In other words, "concat" followed by a substring expression for the "true" result then a comma followed by an expression for the "false" result.

    So if the start/end "break" times are filled in by the user, then we know our "true" result is the expression above that works now. The "boolean" condition we will use is as follows for the "true" result:

    (../my:StartBreakTime != "" and ../my:EndBreakTime != "")

    In other words, StartBreakTime and EndBreakTime are not blank.

    The "else" portion of our "concat" statement is when the StartBreakTime and EndBreakTime are blank – so the "boolean" condition would be as follows for the "false" result:

    (../my:StartBreakTime = "" and ../my:EndBreakTime = "")

    And when this is the scenario (no break times entered) we only need to calculate the difference between the start and end times, which was our "Part 1" noted above.

    So now that we have all the pieces of the expression, le’s put it together:

    concat(substring((((substring-before(../my:EndTime, ":") * 60) + substring-before(substring-after(../my:EndTime, ":"), ":")) – ((substring-before(../my:StartTime, ":") * 60) + substring-before(substring-after(../my:StartTime, ":"), ":"))) – (((substring-before(../my:EndBreakTime, ":") * 60) + substring-before(substring-after(../my:EndBreakTime, ":"), ":")) – ((substring-before(../my:StartBreakTime, ":") * 60) + substring-before(substring-after(../my:StartBreakTime, ":"), ":"))), 1, (../my:StartBreakTime != "" and ../my:EndBreakTime != "") * string-length((((substring-before(../my:EndTime, ":") * 60) + substring-before(substring-after(../my:EndTime, ":"), ":")) – ((substring-before(../my:StartTime, ":") * 60) + substring-before(substring-after(../my:StartTime, ":"), ":"))) – (((substring-before(../my:EndBreakTime, ":") * 60) + substring-before(substring-after(../my:EndBreakTime, ":"), ":")) – ((substring-before(../my:StartBreakTime, ":") * 60) + substring-before(substring-after(../my:StartBreakTime, ":"), ":"))))), substring((((substring-before(../my:EndTime, ":") * 60) + substring-before(substring-after(../my:EndTime, ":"), ":")) – ((substring-before(../my:StartTime, ":") * 60) + substring-before(substring-after(../my:StartTime, ":"), ":"))), 1, (../my:StartBreakTime = "" and ../my:EndBreakTime = "") * string-length((((substring-before(../my:EndTime, ":") * 60) + substring-before(substring-after(../my:EndTime, ":"), ":")) – ((substring-before(../my:StartTime, ":") * 60) + substring-before(substring-after(../my:StartTime, ":"), ":"))))))

    I hope this helps!

    Scott

  17. mcmurrayjl says:

    Scott, let me start by saying YOU ARE THE MAN.  Not only did you answer the question, you broke it down so I could understand how each piece relates to the puzzle.  This is beyond what I expected, and definately put me miles down the road.  I don’t know where you work, but you deserve a raise and a promotion.  I think these xpath expressions are pretty slick, and am looking to add more functionality to my existing forms with these methods.  It is a bit confusing at first, but hopefully I will know at least 25% of what you do, and I will be happy!!  Thanks again Scott!

  18. brooks1856 says:

    Scott,

    Thank you very much for responding to my inquiry about adding break time columns to your sample.  That worked wonderfully.  The previous posting you supplied to “mcmurrayjl” is also intriguing to me.  

    Let’s say I too wanted to have two additional columns for other time taken off.  You said that staying with the current design shown in the sample would be difficult at best.  I’m pretty good building forms through the GUI of InfoPath but I don’t have much experience with C#.  Forgive me if this a stupid question but could I still keep the design you applied and use C# code to write the other two columns?  Or is it best to just start from scratch and do the whole thing in C#?  I just didn’t know if it were possible to incorporate both xPath expressions and C# in the same repeating table.

    Thank you for your information and codesmanship!!! It has been very helpful.

  19. infopath says:

    Thanks  mcmurrayjl!

    Brooks1856 – let me clarify my comments: if you were only going to add another 1-2 columns, then you could probably stay with this design. However, if you have a situation where you could protentially have multiple "time in and time out" entreies, then a better design is a relational one where , say, each new time in/out would be a new row in a repeating table.

    Now to more address your question: it is important to know that you do not have programmatic access to the controls in an InfoPath Form Template – you only have access to the nodes to which those controls are bound. So you cannot create them dynamically if that is what you were asking. This whole expression though is getting pretty outlandish <g> so another option would be to perform the "elapsed time" calculations in code and simply set the values of the corresponding nodes for the form. When doing this in code, the code would not be anywhere near what you have to do in this expression – but the whole point of this sample was how to accomplish this without code.

    Scott

  20. bentorrey@hotmail.com says:

    Hi All,

    Thanks for an informative post.

    I hope I can phrase this question constructively, but, boy, it seems like a ton of trouble to do what is so easy right in Excel VBA. The DateDiff function for example.

    Is there a function library or something we could tap into for this type of basic stuff?

  21. infopath says:

    Hi bdog,

    Yeah – that would sure be nice wouldn’t it? <G>

    Unfortunately no; however, this post was meant as a way to demonstrate how to accomplish an "elapsed time" calculation without any code. If one was comfortable using code, then this would be a bit more "cleaner" – at least from an expression point of view.

    Scott

  22. mcmurrayjl says:

    I give up!!!!  How in blue blazes do you add one more set of columns (other time in & Out)  I have been banging my head on my keyboard for a week now.  Any information would be appreciated.  "infopath" – I’ll send you something to your PayPal account!  🙂

  23. Cue thunderbolts and lightning. I figured that &quot;Infopath timesheet, no problem!&quot; and &quot;there

  24. infopath says:

    Hi everyone,

    So there seems to be some interest <g> in having a sample that allows for one or more "break time" types of entries. I am in the process of re-designing the sample to you do not have to update the expressions each time.

    I am hoping to have this done within the next few days but I am not sure yet if it will get posted here or as a new blog post…I will let you know if it comes as a new post.

    Thanks everyone!

    Scott

  25. ChrisBacon says:

    I noticed that your solution (which is great) uses "div" and "mod". I couldn’t find any mention of them in the documentation. Are there other undocumented operators?

    Thanks,

    Chris

  26. infopath says:

    Hi ChrisBacon,

    Thanks!

    The "div" operator is simply "division" – which in XML is "div." When you choose to "Show the XPath" we show you the actual operator we are using. So: 10 / 5 is the same as: 10 div 5.

    For the "mod" operator you can find some information in the Help file for InfoPath – in the Developer reference. The short answer: "mod" returns the modulus — that is, divides num1 by num2, returning the remainder.

    Scott

  27. bmorri5 says:

    Hi Scott,

    This info was GREAT and helped me a lot.  New to InfoPath and weak in coding.  I have another question.  Your basic design works great for our needs and I would like to add a couple of fields.  

    One for required hours that would be entered by the user (changes per pay period) and another for used annual/sick time.  I would like to keep these fields consistant in how they are entered with  the other fileds in your form. It does not have to calculate anything as this will be done by the user.  Each day may use sick and annual on the same day.  I have these fields in a separte repeating table.  I would also like a sum of the sick/annual fields at the end.  Is this possible using the same time code you used above?  Thanks for you help.

  28. infopath says:

    Hi bmorri5,

    I am not sure I exactly follow what you need but in theory, this should be doable. However, I am creating a new design for this sample that allows the user to enter as many "break" times as needed. This should make the process easier in general and for you as well.

    I am hoping to get this new design posted in a few days – you may want to wait to see this and see if it helps with what you need.

    Scott

  29. bmorri5 says:

    Scott,

    THANK YOU!!! I will keep checking back!!!!  Thanks again!!

  30. QLang says:

    In a note above to ugdstudios, Scott Heim noted that it is probaby easier in the long run to use custom code to perform the calculations when calculating time across days. However, in our case, we would only ever calculate time across one day barrier, since shifts start on one day and end on the next. Would it be possible to do the equivalent of inserting an IF statement that would automatically add 24 hours to a negative time? Since we use a 24 hour clock when recording time and a shift record must be entered at the end of shifts that are never longer than 12 hours, such an IF statement would solve our problem.

  31. infopath says:

    Hi QLang,

    It is not as simple as just adding an "if" statement to an InfoPath expression as this makes this already extensive expression even that much more. (For more information on using/creating "if" statements, take a look at this blog post: http://blogs.msdn.com/infopath/archive/2006/11/27/conditional-default-values.aspx)

    In short, you would need to add something like what is documented in the above referenced blog to the expression shown in this post. So – is it possible? Most likely yes but I am still of the opinion that once you get past what is shown here, it is probably easier to calculate these times using code.

    Scott

  32. QLang says:

    Not being a coder, I thought of a different approach. I broke the start and end time fields into four fields: StartHour, StartMinute, EndHour and EndMinute as well as the results field TotalHours. Under the Default Value heading in the Text Box Properties for the filed TotalHours I entered (EndHour * 60 + EndMinute – StartHour * 60 – StartMinute) / 60 . This works just fine for the regular day shift but returns a negative number when you start a shift the day before.

    To handle the exception I created a rule "Previous Day Start" in which I set the conditions "TotalHours < 0" and "TotalHours is not blank" and then ran the action "Set a field’s value: . = . +24" (where . is value in TotalHours). This works since we use a 24 hour clock.

    I only have one problem now. The minutes numbers look funny because when you enter "09" in the minutes field it truncates the display to "9". Is there a way to pad the field with a leading zero?

  33. infopath says:

    Hi QLang,

    Take a look at "Step 3 – Final Formatting" – this shows how to pad values less than 10. As you can see though, these expressions are getting quite complex so make sure you have a backup of your currently working template before you continue with modifications.

    Scott

  34. unclemartybob says:

    I read your intial postings above. The main article references a file in the snippet:   <sample, here is the .xsn file – make> , which when I click this link I am able to download the file: ElapsedTime_NoCode_NewDesign.xsn , but when I try to open this file using InfoPath (2007), I get the error message:

    "This form cannot be opened because it requires the domain permission level and it currently has restricted permission. To fix this problem, open the form from the location it was published to:

      Published location: file///C:Documents%20and%20SettingssheimDesktopElapsedTime_NoCode_NewDesign.xsn"

    Can you please send me a copy of this file as an email attachment (preferably zipped) or re-post it?

    If you have any variations of the form, those might also be helpful.

    I am trying to build a form that calculates an employees daily work time….where a daily total is calculated and grand total is also calculated…similar to the table below, but with a (day) row for the entire month.

    Thanks!

  35. unclemartybob says:

    Sorry…here is the table example I referenced in the last posting…hope it comes across (posts) in a table-like format.

      DATE   |  START-TIME |  LUNCH-START | LUNCH-END | END-TIME | ACTUAL-TIME |

    02/01/10   |        9:00 AM     |        11:30 PM       |      12:30 PM     |     5:30 PM    |       7.50                |

    02/26/10   |        9:00 AM     |        11:30 AM       |      12:00 PM     |     5:30 PM    |       8.00                |

                                                                                                              Total Hours: |      15.50               |

  36. infopath says:

    Hi unclemartybob,

    To use the sample InfoPath Form Template attached to this post you will need to do the following:

    1) Save the attached XSN to your computer

    2) Right-click on the XSN and choose Design (click OK to any messages)

    3) From the File menu choose "Save As" and re-save this over itself on your machine

    Now, it will work.

    Scott

  37. mrjstin says:

    Hi Scott –

    First off well done. I’m almost there in getting my form to working :). Except…

    I have a base rate field above my repeating table and a daily total field in my repeating table (Rate * Hours). Since implementing this it fails because my Hours field is now a string and shows as 1:25. Is there  a way to convert this Hours field to a Decimal? When I try to change the field it errors out when doing the calculation.

    Many thanks…

  38. infopath says:

    Hi mrjstin,

    Please accept my apologies but I am not clear on what is failing. Can you give me more details around what you have done with regard to design?

    Thanks…

    Scott

  39. mrjstin says:

    Sure…

    This is for a timesheet so I have a Base Rate field of a dollar amount and a repeating table with the following fields:

    Date, Start Time, End Time, Hours, Daily Total

    Using your example I get the Hours field calculated correctly from the times entered, but when I do a Daily Total calculation of Base Rate * Hours it fails. This is because the format of the field is hh:mm I’m guessing. Does that make sense?

  40. infopath says:

    Hi mrjstin,

    Hmmm…ok – I see what you are saying…what about this – would this work? Instead of using the "string" showing the hours/minutes for your expression, what if you took the BaseRate (which I am assuming for this exercise is an hourly rate) divided it by 60 (to give you a per minute rate) and then multiplied this by the "TotalMinutes" field which is the one used for the hours:minutes result?

    Here is what my DailyTotal expression looks like:

    (BaseRate / 60) * TotalMinutes

    See if this gives you the correct result…

    Scott

  41. mrjstin says:

    Yeah! That works. My life is now complete(until the next speed bump creeps up..). Thanks very very much!

  42. infopath says:

    Glad to hear that worked!

    Take care…

    Scott

  43. cruffo says:

    I’ve read through your articles and got the elapsed time to work in minutes, but I need it to work in seconds.  I’m creating a timed assessment using InfoPath and need it to start timing when they select Start Test and then stop timing when they select Stop Test.  I’ve used the option buttons for this and added rules to set the value of the Date/Time fields using the now function.  Now I need the Start and End Times to subtract themselves and then show the results in HH:MM:SS  

    I’ve used a combination of your two elapsed time articles to get it to set the Date/Time and then parse out the time from the now function by changing the binding of a second StartTime field.  what I can’t figure out is how to connect these two articles to get what I need.  Do you have any suggestions for what I can do to get this to work?

  44. swissugo says:

    hi scott,

    first, thxs for this excellent post. no my question is, if you have also an exsample with elapsed days ?

    thxs in advance

    ugo

  45. ywang says:

    Hi:

    I just have a question as to

    if I want to round the time to the nearest quater time ,

    e.g

    IF the actual time scope is between XX:00:00 AND XX:15:00,we want to round it to XX:15:00;

    IF the actual time is between XX:15:01 AND XX:30:00, we want to round it to XX:30:00

    IF the actual time is between XX:30:01 AND XX:45:00 we want to round it to XX:45:00

    IF the actual time is between XX:45:01 and XX:60:00 we want to round it to (XX+1):00:00

    Since infoPath has no coding function, I think this may be a bit tough for me, could you help me to solve the problem and offer some better idea, I've tried to use in your fomula above to change the statement,it seems not work well

    Thanks

    yw

  46. ywang says:

    Hi:

    I just used the following code to calculate the round time, if the ActualTime is 1:25 then it is billed to 1:30 , if 1:01 then billed to 1:15, if 1:46 then billed to 2:00,

    but the fomula seems not work well, could someone help me fix it, I've been figured out it for long time, I'm still not very sure of what the problem is.

    I'm new to infopath, so I'm not very proficient in it.

    concat(substring(concat(substring-before(ActualTime, ":"), ":", "0"), 1, ((substring-after(ActualTime, ":")) = 0) * string-length(concat(substring-before(ActualTime, ":"), ":", "0"))), substring(concat(substring-before(ActualTime, ":"), ":", "15"), 1, (0 < (substring-after(ActualTime, ":")) <= 15) * string-length(concat(substring-before(ActualTime, ":"), ":", "15"))), substring(concat(substring-before(ActualTime, ":"), ":", "30"), 1, (15 < (substring-after(ActualTime, ":")) <= 30) * string-length(concat(substring-before(ActualTime, ":"), ":", "30"))), substring(concat(substring-before(ActualTime, ":"), ":", "45"), 1, (30 < (substring-after(ActualTime, ":")) <= 45) * string-length(concat(substring-before(ActualTime, ":"), ":", "45"))), substring(concat((substring-before(ActualTime, ":") + 1), ":", "0"), 1, (30 < (substring-after(ActualTime, ":")) <= 60) * string-length(concat(substring-before(ActualTime, ":"), ":", "0"))))

    Thanks !!

  47. ywang says:

    Could anyone help me with this :  

    The resule is like when Actual time is 1:13 the time is 1, but I want it be 1:15

    concat(substring(concat(substring-before(ActualTime, ":"), ":", 00), 1, ((substring-after(ActualTime, ":") = 0) * string-length(concat(substring-before(ActualTime, ":"), ":", 00)))), substring(concat(substring-before(ActualTime, ":"), ":", 15), 1, (00 < (substring-after(ActualTime, ":") <= 15) * string-length(concat(substring-before(ActualTime, ":"), ":", 15)))))

    Thanks

  48. Scott Heim says:

    Hi ywang,

    At some point, these types of expressions get to be too much to 1) develop and 2) maintain. This would be much easier with code…is there any reason why you could not use code for this?

    Scott

  49. ywang says:

    Hi Scott:

    Thanks for your comment. I want to use code, but I've no idea how it works, for the field, it does not support the "if" statement as in Excel. I got stuck in this, could you offer some help?

    Thanks

    yw

  50. Scott Heim says:

    Hi ywang,

    When I refer to using "code" I am referring to using managed code (i.e. C# or VB .NET) behind the InfoPath form – not "code" isn the control.

    Is this still OK?

    Scott

  51. Scott Heim says:

    Hi swissugo,

    If you are asking if I have a sample that incorporates all of what I have here along with elapsed days, I do not. However, if you just need to get elapsed days you could use something like this:

    substring(dtEnd, 9, 2) – substring(dtStart, 9, 2)

    ** "dtEnd" and "dtStart" are my 2 Date Picker controls which are set to a "Date" data type.

    A date value is stored like this in XML: yyyy-mm-ddT00:00:00 so using the "substring" function allows us to start at the 9th position and get the next 2 digits, which would be the day.

    Now, this does not take into account if you span months – you would need to incorporate that logic as well. But as I have mentioned before, if you get too involved with what you need then code is probably easier.

    Scott

  52. Meghann says:

    Hi!  I've created an agenda with a repeating section that captures discussion items.  I'd like to have each section have a manually assigned duration (drop-down list) and have the start and end time of each section be automatically calculated based on the duration selected.  If the meeting start time is entered, then it should be automatic.  Then I'd like to have each section that is repeated be able to do the same.  IS there a way similar to this?

    Thanks!

  53. Scott Heim says:

    Hi Meghann,

    If I understand correctly you want something like this in a Repeating Section:

    – I select a Duration: 30 minutes

    – I enter in a "startTime" box: 9:00 AM

    – In an "endTime" box, I automatically get: 9:30 AM

    Now, if I add another Repeating Section, by default the "startTime" in the new section will be: 9:30 AM

    Is this correct?

    If so, follow these steps to create a sample so you can see how this works:

    – Create a new, blank XSN

    – Add an empty Repeating Section to the view

    – Inside the Repeating Section, add a dropdown box and two text boxes

    – Name those controls as follows:

        – Dropdown box = Duration

        – 1st text box = startTime

        – 2nd text box = endTime

    NOTE: Please spell the names exactly as I have above so the expressions will work for you.

    When you are done, your data source should look like this:

    myFields

      group1

         group2

            Duration

            startTime

            endTime

    – Add the following values to the Duration dropdown:

        Value: 30, Display Name: 30 minutes

        Value: 60, Display Name: 1 Hour

        Value: 90, Display Name: 1.5 Hours

        Value: 120, Display Name: 2 Hours

    – Add the following expression as the "Default Value" for the "endTime" field: (NOTE: click the "fx" button, enable the "Edit XPath" box and then paste this expression:)

    xdDate:AddSeconds(../my:startTime, ../my:Duration * 60)

    What this does is take the value selected in Duration, multiplies it by 60 to get the seconds and then sets that as the end time value.

    – Lastly we need to add a "Rule" to the "Repeating Section" itself so when you add a new repeating section, it fires the rule.

        – Select the Repeating Section and add a new Rule – no conditions

        – Add the following actions:

             Action: Set a field's value

             Field:  click the butotn and select the startTime field

             Value:  click the "fx" button, enable the Edit XPath option and paste this expression:

    preceding-sibling::my:group2[1]/my:endTime

             Action: Set a field's value

             Field: click the button and select the endTime field

             Value: leave this blank

    Preview and test!

    If my understanding was correct, this should work as you need.

    Scott

  54. bmanda says:

    Hi Scott,

    I have a question,

    I created a Time Sheet with the help of your formula, we also have the PTO column here and the form works great if I continuously enter the values but if I leave a row blank and add the PTO in between, the formula is breaking. It is not calculating the values after the blank (PTO) field.

    Can you please help me with this.

    Thanks

  55. Hi bmanda,

    Is it possible you do not have a default value (of: 0) for the PTO column? If you do, then maybe I am not clear on exactly what you are doing do generate the error…if this is the case, feel free to provide some additional details and I'll try to repro to see if there is a way to get it to work.

    Also – what "design" are you using? The new "ElapsedTime_NoCode_NewDesign.xsn"?

    Scott

  56. Robert Bristow says:

    Hello,

    Is there a way to calculate the time all the way down to the seconds?  I am working on a very simple form to track how much time is spent on each function of our jobs and some items, like phone calls, can easily be 30 seconds or less but will add up over the course of a month or a quarter so we want track the items no matter how small they seem to be.

    Thanks

  57. Hi Robert Bristow,

    Could the expressions in this post be modified down to the second? Sure – but have you seen what is required just to get to the minute? These would be even more heinous to get to the second. In my opinion, the best way to do that would be using code as it would be a lot cleaner.

    Scott Heim

  58. neiruke says:

    Helo,

    i need help!!

    how to calculate time difference between two times. I need to calculate the time (eg 10 minutes before endData), then change endData +10.

    Enddata= 2012-10-11  15:00,

    if Enddata=2012-10-11 14:50 ( -10min), then Enddata=Enddata+ 10min, else enddata=endadata.

  59. B Watson says:

    Are there instructions anywhere that tell you step by step?  The above to me is confusing after let the fun begin!  parse the Hours and Minutes, is this an action or just formula to added to startTime and endTime field….

  60. Bamidele Ariwodola says:

    Hello,

    I have followed the steps in this blog but I get a NaN value in the StartTime and EndTime fields. This prevents me from entering a time. I entered the expressions for the fields in the default value section of field properties. Could that be the issue?

    Please help