Microsoft InfoPath 2010
The official blog of the Microsoft InfoPath team

Calculating Elapsed Time…without code!

Calculating Elapsed Time…without code!

Rate This
  • Comments 59

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

Attachment: ElapsedTime_NoCode_NewDesign.xsn
Leave a Comment
  • Please add 7 and 8 and type the answer here:
  • Post
  • 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.

  • 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

  • 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?

  • 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

  • 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%20Settings\sheim\Desktop\ElapsedTime_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!

  • 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               |

  • 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

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

  • 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

  • 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?

  • 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

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

  • Glad to hear that worked!

    Take care...

    Scott

  • 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?

  • 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

Page 3 of 4 (59 items) 1234