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 8 and 2 and type the answer here:
  • Post
  • PingBack from http://little.xmtp.net/blog/2007/02/26/with-lots-of-unreadable-code/

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

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

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

    Thankyou sooo much!!

    Keep up the good work

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

  • 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

  • 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

  • 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

  • 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

  • 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

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

  • 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

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

  • 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

  • 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

Page 1 of 4 (59 items) 1234