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 6 and type the answer here:
  • Post
  • 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

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

  • 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

  • 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

  • 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

  • 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

  • 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

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

  • 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

  • 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

  • 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

  • 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

  • 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

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

Page 4 of 4 (59 items) 1234