Alec Pojidaev's InfoPath Blog
Stuff published for no reason (cool codeless infopath solutions mostly)

Infopath codeless programming (walkthrough) 2

Other parts: 1, 3.
Bookmark that post

Tested for SP2007 web forms, SP2010 list and library forms (note: the form has to be redesigned significiantly for list form because of different data fields structure).

Updated: 5 more forms added at the bottom: “work days calculation (excluding weekends and holidays)”, “work days calculation in the list form”, “day of the week calculation”, “Days since form was created” and “Hours Calculations”.

Special thanks: Karl D. Swartzendruber, Karin Edmondson

So what about so called “codeless programming” in InfoPath? As we all probably know, InfoPath can performed only 16 calulations without user interaction. But what if your business rules require you to iterate through 17 repeating elements? You will get a message box from InfoPath with phrase “The rules or code may be causing an infinite loop”. Yes, in InfoPath 16 is a very big number and 17 is considered infinite. To you and I this might seem stupid and arbitrarily restrictive, but Microsoft’s wisdom is greater than ours. Perhaps they know that 17th iteration would give us developers too much power. It would break the fine balance of Microsoft applications, make other development tools obsolete, and become unbearable for non InfoPath developers. But let’s drop the light irony and just accept that InfoPath already has more codeless programming power than mere mortals should ever need.

Today’s challenge is to find the difference in days between 2 datepicker controls disregarding the absence of datediff() function in InfoPath. We are going to show that the 16 iterations limit just can’t stop the true codeless programmer.

Disclaimer: During developing cycle author has been having in mind “finite automates” principles as well as primitive binary search.

Anyway, lets get to the business. Date Difference Calculation. Form to download.

Our recipy is the following: We will need 3 fields. 2 of them are date type fields with datepicker controls (startDate, endDate) and one (dayDifference) is a text type field represented by a simple textbox. All rules have exactly 1 action.

startDate rule:

condition expession: ../my:endDate != ""
field: dayDifference
string value: 0;4096

endDate rule:

condition expession: ../my:startDate != ""
field: dayDifference
string value: 0;4096

dayDifference rules:

MainRule:

condition expession:
xdDate:AddDays(../my:startDate, substring-before(., ";")) != ../my:endDate and contains(., ";")

field: dayDifference
xPath expression: concat(substring-before(., ";") + substring-after(., ";") * ((translate(xdDate:AddDays(../my:startDate, substring-before(., ";")), "-", "") < translate(../my:endDate, "-", "")) * 2 – 1), ";", substring-after(., ";") div 2)

finalize rule:

condition expession: contains(., ";")
field: dayDifference
xPath expression: substring-before(., ";")

second

Your form is ready!

What else can be done by applying same principles:

Here is form that shows amount of days since it was created
Form that shows current day of the week and day of the week from Date Picker control.
Same but with Hours: Hours calculation
The tricky one: Work days calculation (business days)
Work days implemented in the list form. This form was kindly provided by blog reader Karin Edmondson. The form won’t open in preview mode because of the obvious reason – the connections are pointed to Karin’s server. So your only option is to examine his formulas and try to reproduce them in your own form. Also another nice thing about this form is it takes into account half days.

That’s it. Happy codeless programming!




Posted under tags: Infopath, Date Picker Control

About these ads

352 Responses to “Infopath codeless programming (walkthrough) 2”

  1. Hello,

    i like your steps and it is exact what i need. However when an user is filling out my form in our intranet the dayDifference field shows: NaN;NaN

    I ma using infopath 2007.

    Do you have an idea what i am doing wrong?

    M.

  2. When you do copy/paste XPath expressions (not values like 0;4096) into action vindow, make sure you’ve pressed fx button and then checked “Edt XPath(advanced)” checkbox.

  3. Hi very thanks for this cool sharing. I follow the step but was encountered with the error: Some rules were not applied

    AFter i click on the OK button.
    day diff = 0.5;0.25
    for Start Date=14-Jan-09, End Date=15-Jan-09

    I though this should return me 1 day in different? Any thing wrong?

  4. Hi,
    Is there anyway we can calulate months between two dates?
    Please help if you know.

    Thanks
    IA

    • >Please help if you know.

      Knowing is overrated. But I can help you even if I don’t initially know.

      It’s somthing like (substring(date1,1,4) -substring(date2,1,4))*12 +substring(date1,6,2)-substring(date2,6,2)

  5. Alecpojidaev thanks a lot!

  6. Thanks for your reply alecpojidaev.
    Can you point me where should I have to put this code. I tried in rules, condition. I might doing somethign wrong if you can point me will be realy helpfull.

    Thanks again
    Imran

  7. To: Imran
    Actions inside rules are the right places.
    You will need to replace date1 and date2 with the actual field names on your form as well.

  8. Hi alecpojidaev,
    I tried in it but no luck. If its possible can you send me the file if you test on your end.

    Thanks again for you help
    Imran

  9. Hi alecpojidaev,

    is it possible to exclude weekends? I only want calculate workdays.

    Thanks for help.
    plimp

    • To: Plimp
      I’m not sure about that. It would be posible if we have know information about days of the weeks of both date picker controls.

  10. Hi,

    is it possible to get informations from datepickers?

    When i have to calculate the date from picker 1 with 01/21/09 and date from picker 2 with 01/28/09 then i have 7 day´s between the picker. In this case it should strip 2 day´s. When i have 14 day´s it should strip 4 day´s.

    Did this work with rules?

    • To:Plimp
      Ya thats how it should be calculated. But without knowing at least one day of the week at either one date picker you cant be sure how many weekends between 2 dates. the formula (date1-date2)*5/7 gives you the result with precision +/-2 working days.

      • Hi Alec,

        I do have the same problem, when using your form it works 100% without the weekends but when I use mine it adds the weekends. Any thoughts, thanks

      • If you copied everything correctly the fact the form doesn’t work means someone did cursed your form and you need help of powerful magician to remove the curse. Sorry, can’t help you here.

  11. Thanks alecpojidaev.
    It works like a charm. Only issue getting is when I entered 01/01/2009 to 31/12/2009 it calculates 11 months but if you see these are 12 months.
    Do you have any solution?

    Thanks
    Imran

  12. Hi,

    could it work when the format of the date pickers are “wed 01/21/09″.
    If this would work, how?

  13. I am in the same boat as Imran, the weird thing is I can see how yours works, but I can’t get mine to work. Something to do with the set field to in the second rule on the date2 has a set fields value: y = concat(substring(., 1, 4), substring(../my:date2, 1, 4), substring(., 5, 2), substring(../my:date2, 5, 2))

    I’m not sure how you are pointing to the field called ../my:y in your rule. how is that accomplished.

    • To:murphy tha dog
      You can remove action where ..my:y is involved. It’s not used anymore (Sorry, hanging wires of fast developing).

  14. But you do need both rules in both date fields right?

    • In that design yes. But you can reduse amount of actions to just 1 by transferreng action to a button and removing all actions from controls. In that case you will need to press button to perform calculation. The paths to the fields has to be updated respectfully.

  15. I like how yours is automatic so I will keep it that way, but my result is NAN in the Nmonths field I can’t seem to reproduce the correct calc.

  16. i did however get it to work in a new clean xsn file. so i will try again tomorrow. thanks

  17. Can you post the latest XSN file to review how you accomplished removing the weekends. I have a form very similar to this and have yet to get this to work. Thanks

    • I hadn’t accopmplished it. You can’t do it without knowing the day of the week on at least one datepicker. I don’t know how to get that info without using external data sources (i.e. sharepoint).

      Update: Now I know how to do that. Still need to fight my laziness to finish the thing.

  18. Is there a way to make is so that when you click on Monday and then on Friday and the difference will show 5 days. Right now I get 4 days.

  19. These are pretty good. :) Hope there are more coming.

  20. very very good arcticle, exactly matching for my application. But this is not supported with Infopath 2003. Is it possible to open in Infopath form 2003.

  21. Is there an updated place to download the xsn files? They show that they are unavailable. thanks

  22. Sorry, looks like Microsoft constantly changing links to the files in SkyDrive. I replaced direct file downloads with links to the folders.

  23. Hello. For some reason day difference returns 0;4096, any idea?

  24. To: Hector Cuevas
    Nope. No idea. I even don’t know have you created the form from scratch or you talking about form available for download.

  25. can you please publish the code for the daysOfTheWeek xml file used in the demonstration for showing day of the week in the data connection in the demo form?

    I have an immediate need for this application. Thanks!

    Jared

  26. Sorry… I finally see where you used the xml data source file… still need the code for the xml file, if you can, please.

    Thank you!

    Jared

    • Whats the problem to extract secondary data source XML file from the template? Are you getting any errors when you doing
      Manage Data connections -> Modify daysOfTheWeek -> Resource Files -> Export?

      Or just save as Source Files.

  27. Ah… thanks for the tip… I’ve never noticed the export feature before.

    Thanks!

  28. I’ve just downloaded the form you’ve created to calculate the day of the week and have been trying to put the formula’s from the downloaded form into my other one, i think i’ve got all the little bits and pieces required for it to work yet when i preview the page it only comes up with 0;4096. Is there anything commonly left out, or have you created a step by step in setting this up?

    Thanks heaps for all your codeless solutions, they really help with taming Infopath 2007

  29. Dear Alec,
    Thank you for this nice tool…
    Im new to the world of InfoPath and I have tried following your “step by step” above. However, I run into a small problem. No matter which dates i select in my date picker, the resault always end at 0;4096

    What am I doing wrong?

    Your help will be appriciated :-)

  30. I did that… I copy/pasted every thing…

  31. Well… if you copy/pasted everything then we are witnesses of a miracle.

  32. If I add a simple calculation field to your form, dayDifference * 3.33, I get an error message that reads ” A calculation in the form has not been completed. The number of calculations used for a single action exceeded the maximum limit.”

    Any ideas

  33. Thats how it suppoused to be. dayDifference is already almost at the limit of allowed amount of calculations. Adding one more field with calculation involved dayDifference stops things working right away. Same error because of the same reason you will see if you’ll try to mesure amount of days between dates with day difference more then 23 years on unchenged form.

  34. Thanks for the response. Is there any way to include the diffDays field in a caluculation? Or, is the diffDays calc the only one I can have on the form?

    Thanks again for your help.

    Rob

  35. If you need to do some after calculations use “finalize” rule action.

    You can have as many calculating controls as you want on the form. The only limitations is the amount of automatic calculations after user action (16).

  36. Thanks for the info. It works like a champ.

    Rob

  37. Great post this has been really useful. I’ve used your technique to work out the working days in a holiday request form but I have an issue if the begin date is a Sunday. The calculation comes back with decimal number eg 6.7142857. I’ve downloaded your example form and it does the same in there, everything else is fine. Any ideas what’s causing this.

  38. Thanks for uploading your form, there are helpful. I’ve got a problem when i try to do the workdayscalculation form. When i have to make the last rule for the workday, i can’t do

    “compte(hday[traduire(BeginDate; "-"; "") = traduire(@hdayn; "-"; "")]) “,

    and error occured there is the error :

    “xdXDocument:GetDOM(“daysOfTheWeek”)/somestuff/holydays/my:BeginDate » ne pointe pas vers le chemin d’accès valide d’un champ ou d’un groupe.”

    It’s in french but it’s saying that doesn’t point to a validated path of a field or a group, i don’t know what i do wrong, everything else seems ok, thanks in advance

    Ced

  39. Have you included XML file with French holydays in your form? You can extract copy of it using instructions in my previous responses.

    • I included the same XML file you used to my form, i extract it to you form …. something strange, if in your form i change compte(hday[traduire(BeginDate; “-“; “”) to compte(hday[traduire(@name; “-“; “”)

      it’s working to, but in my form it still calculte the number of days but it doesn’t take care about workday or not

      thanks in advance

      Ced

  40. Well… at that point it’s getting kind of hard to say what is wrong. Looks like all your troubles are related to secondary data source. So I would sugest maybe better solution in your situation would be using InfopPath UI for manualy picking up fields from secondary datasource.

    • I’m doing this aswell, i always use InfoPath UI for avoiding synthax error, thanks anyways i’ll try to find out where is the problem.

  41. Also, why i can’t remake your form ? it’s the same error as me when i try to do the

    “compte(hday[traduire(BeginDate; "-"; "") = traduire(@hdayn; "-"; "")]) “,

    i pick field from infopath ui etc … but it’s still getting me the same error, i think it’s because in Holidays/hday/ there is no BeginDate, so it can’t work, i really don’t know how you do this.

    • At that point you have to use absolute path not relative. Thats why it’s always better to copy when ” edit avanced” check box is checked.
      In my advanced view BeginDate looks like:
      xdXDocument:get-DOM()/my:myFields/my:BeginDate
      and because of error yours path structure is different probably.

  42. Thanks a lot, i can compile my form now, but there is another problem. Now the weekend are determinate by the startdate,

    example : if start date is a monday -> weekend = sunday and monday
    example2 : if startdate is a sunday -> weekend = saturday and sunday

    Any thought about this please ?

    Ced

  43. Sorry I can’t understand what your problem is.

  44. well i have 2 datepicker, one for the begindate and one for the enddate, in workdaycalculation you don’t count weekends (saturday and sunday) into date calculation.

    My problem is the weekends aren’t saturday and sunday, the 2 days which are considered as weekend are determinated by the datepicker of begindate. this means if i choose today ( 8 june ) as begindate, the calcul will be like this :

    enddate 9 june -> workday = 2
    enddate 10 june -> workday = 3
    enddate 11 june -> workday = 4
    enddate 12 june -> workday = 5
    enddate 13 june -> workday = 6 saturday it should be 5 here
    enddate 14 june -> workday = 6 sunday it should be 5 here
    enddate 15 june – > workday = 6

    i wish you understand my problem,
    thanks in advance

    Ced

  45. I can’t reproduce that. My form shows
    enddate 13 june -> workday = 5 as it should be.

  46. I really don’t know why my form does this error, i’ve exactly the same codeless programming of your form but it’s doing the thing i described in my previous post, anyways if you have any ideas of what can be wrong for doing this error, thanks to share me.

    Ced

    • Ok i resolve this problem, now the form calculate workdays like your. But now i’ve a new problem … my form doesn’t count the holidays.

      Basically, if in your form i extract the xml file and i rename this. when i create a new connection to the new xml file and change BegDateWN condition to rule 1 xdXDocument:GetDOM(“daysOfTheWeek”) to xdXDocument:GetDOM(“newnameofXML”), and i change the path of the basepoint of this rule to the new path basepoint. Then i also change in rule 2 of workdays the path to hday and @hdayn to the new path.

      The result is it don’t count the holidays of the XML file, what I am doing wrong ?

      thanks in advance

  47. hello
    i ve jus copy pasted this from ur form.
    when i add a rule for the workday textbox i get the error as

    Node-test expected.
    (substring-before(/my:myFields/my:txtHiddendayscount, “;”) + /my:myFields/my:FromDateValue) mod 7 – ((substring-before(/my:myFields/my:txtHiddendayscount, “;”) + /my:myFields/my:FromDateValue) mod 7 = 6) + 1 – /my:myFields/–>my:FromDateValue- <–(/my:myFields/my:FromDateValue= 0) + (substring-before(/my:myFields/my:txtHiddendayscount, ";") – ((substring-before(/my:myFields/my:txtHiddendayscount, ";") + /my:myFields/my:FromDateValue) mod 7 – /my:myFields/my:FromDateValue)) * 5 div 7 – count(/somestuff/holydays/hday[translate(/my:myFields/my:datLeaveFrom, "-", "") = translate(/somestuff/holydays/hday/@hdayn, "-", "")])

  48. I found my error, it’s when i’ve to do

    compte(hday[traduire(BeginDate; "-"; "") = traduire(@hdayn; "-"; "")])

    for doing this i enter

    compte(xdXDocument:getDOM(“daysOfTheWeek”)/somestuff/holydays/hday[traduire(/my:myFields/BeginDate; "-"; "") = traduire(xdXDocument:getDOM("daysOfTheWeek")/somestuff/holydays/hday/@hdayn; "-"; "")])

    there is no error for the form but when i enter this it doesn’t count the holidays, what is your method for enter this please ?

  49. Just in the case you don’t understand my problem, delete compte(hday[traduire(BeginDate; "-"; "") = traduire(@hdayn; "-"; "")])

    and explain how do you do for rewrite this,

    thanks

    Ced

    • To: Ced and Prasanna
      For some reason the formula in my form in that fragment looks a bit different:

      count(hday[translate(BeginDate, "-", "") <= translate(@hdayn, "-", "") and translate(EndDate, "-", "") >= translate(@hdayn, "-", "")])

      Same with XPath:
      count(xdXDocument:GetDOM(“daysOfTheWeek”)/somestuff/holydays/hday[translate(xdXDocument:get-DOM()/my:myFields/my:BeginDate, "-", "") <= translate(@hdayn, "-", "") and translate(xdXDocument:get-DOM()/my:myFields/my:EndDate, "-", "") >= translate(@hdayn, "-", "")])

  50. Hello its working.thanks a lot and i ve one more doubt.the form is all about excluding the weekends.but dont want to exclude first and last saturday of the month.can u help me out with this.

    thanks in advance

  51. Hey–can you adapt this so that it returns the number of specific days in the date range? (i.e., the number on Mondays between the start time and end time?) Or even can you choose Monday as a options and only populate a drop down that shows dates in the specified range that are Mondays?

    I tried, but since I’m not familiar enough with xpath functions, I couldn’t figure it out.

    Thanks!

  52. I was wondering what changes I need to make to use this in a repeating table?

    • Edit: Check my new DateCheck form with repeating table primer.

      In repeating table it shouldnt be too different. Just use

      preceding-sibling
      .
      following-sibling

      functions instead of hardcoded field names. You also have to avoid linked calculations (i.e recalculating all fields in repeating table on some event) because you can easily get over the limit of allowed calculations.

      • Hey there!

        The “What Day is Today/Anyday” code works but I have the fields on a repeating table and when I add a new row, the “day of the week” field is left blank. Is there a way to make the code operational on the added rows?

        Thanks!!!

      • The main example DateCheck.xsn has XPath adopted to the repeating table. You will have to combine both XPaths from 2 forms to get what you need.

  53. Hi – thank you for the code – the concept is great, especially doing it without code. When I followed your instructions and previewed the form it looks/works great. However, when I publish the form to a form library in Sharepoint and launch the form via browser – I get the following error. Any ideas?

    “An error occurred in the form’s rules or code. The number of rule actions or the number of calls to the OnAfterChange event for a single update in the data exceeded the maximum limit. The rules or code may be causing an infinite loop. To prevent this, ensure that the rule action or event handler does not update the data which causes the same rule action or event handler to execute. “

    • It would be nice to know what form are you talking about. That post has 4 of them.

      • Hi sorry – the question was in respective to counting the number of days between two date pickers (inclusive of weekends). Specifically the instructions abover about startDate rule, endDate rule, and dayDifference rules. Like i mentioned in my original question – the form works perfectly until I publish the form to a browser-enabled forms library in Sharepoint. When I launch the form from that location, I get the error mentioned above.

      • Edit: Unsupported function problem fixed.

      • Great and extremely valuable post, i’m currently working on a Sharepoint implementation of a Leave Management System and we are thinking about using InfoPath forms as forms for leave application, the business days calculation form looks very useful in my case. My problem is that it works well in the infopath client and preview mode but when i publish it to a sharepoint forms library, i get the error:
        “An error occurred in the form’s rules or code. The number of rule actions or the number of calls to the OnAfterChange event for a single update in the data exceeded the maximum limit. The rules or code may be causing an infinite loop. To prevent this, ensure that the rule action or event handler does not update the data which causes the same rule action or event handler to execute. ”
        Any ideas why? I have by the way changed the data source to xml file stored on disk by the same name.

      • Try to retrieve the data of external xml file on clean without any functionality web enabled form. If you won’t be successful learn how and where to store you xml data.

  54. I’m interested in the “TodayIs” field and funciton alone where it auto sets today as it’s value then it formats itself as the day of the week. I downloaded the “WhatDayIsToday” template and see how with one resource file, and then 2 rules with conditions this deal is done. I’ve created the very same field named “TodayIs”, I uploaded the resource file on my form and added it as a dataconnection, I then using the fx button have copied the formula (while having the edit xpath pressed) to simply recreate via copy paste the same funtion but mine never works.

    The Rule 1 if left alone on mine gives me no value, and if I enter into the field “;” I get “nan,nan”. The second rule after copy/paste always is upset about the formula, the very same one working right in my face… AhHH..lol…. What step might I be missing?

    I make a field called “TodayIs”, I extract then upload the resource file, add it as datasource, I copy/paste the conditions and rules using the xpath advanced while copy and while paste and no dice.

    Thanks for you help on this.

    • It’s hard to tell whats wrong with your form. Probably data types are mismatched. Make sure all field types are the same as in my form.

      Also may be you could try to modify the form from the other end? Use my form as starting point then add the logic you need. I’ve tested that form in browser enabled forms it works just perfect.

      • Hello again.. :)

        We’ll I’m trouble shooting and narrow it down and left my text field “TodayIs” with just the first rules copied expression “cut/paste” and the if “TodayIs” contains “;”, then in testing I had the aciton set my date field to “1”. This did not work making me see that the expression when copied and pasted does not work for me. I do have the resource file as a secondary data connection to recieve also.

        So, regarding the query:
        xdDate:AddDays(xdXDocument:GetDOM(“daysOfTheWeek”)/somestuff/basepoint, substring-before(., “;”)) != xdDate:Today()

        I’m just choosing for my rule’s condition the option of “The Expression” and pasting in the expression, the creating the simple if the field includes the “;”, and it does not work for me. What can I do with this expression to duplicate it on my form please?

      • Make sure your secondary datasource has the same name (case sensitive).

  55. When i enter start date as Friday and End date as Monday then my field only calculates 2 days of my working day. Dont calculates the sat and sun. how can i perform this.. thnX Advance

  56. Dear Alec,
    just wanted to know from where did u get this info regarding 16 iterations,i mean that the maximum iterations possible in an INfopath form is 16 and 17 is infinity for infopath form as u’ve said.How relevant is this info.I’m checkng that because except your blog i never get a chance to see any such comment made by anyone or accepted by microsoft themselves.Any link or help would be of a great help,i’m caught in a issue that involves help from Microsoft and i cant recah them without checking the relevancy of this message.
    Sorry to bother you
    Thanks in Advance

  57. Hi Alec,
    just wanted to know,from where u get this information that there are only 16 iterations possible in an infopath form,and can u be specific in terms of telling me,what exactly this iteration means.Does that mean if i have a repeating control and i’m iterating it’s nodes or something else.It is kind of urgent.As it involves me raising an issue with Microsoft for Infopath forms.So please if u can provide me some links that can prove that this statement is genuine,it will be very helpful for me.
    Thanks in Advance
    Sorry to bother you..

    • More presisely 16 is not a iteration limit. The limit is a number of assignements (not just any assignements but assignements actually changing the value of a field) to the infopath fields after an user action.

  58. i have downloaded the form on this website which shows calculation of workdays between two dates selected. i tried to copy the same rules on my form and preview but it didn’t work. The mome nt i select the start date i am getting the error pasted below:

    A call to XDocument.GetDOM failed. The DataObject “daysOfTheWeek” does not exist.

    Error occurred during a call to property or method ‘GetDOM’.

    Kindly tellme what am i doing wrong here or if i have missed any step to be followed.

    Thanks in Advance….!

  59. This is all very useful, but I’m using the reply on this discussion to ask a bit of a different question. I’m a non-code writing developer (I use that term losely). Anyway, I am trying to develop a number of InfoPath forms to be used on SharePoint. I would like managers to be able to complete a form for an employee and have Profile Data autofil by using the employee’s account name not the “user”. The employee would be selected from a list or search at the top of the form. Hope this makes sense. I am a bit frustrated on how to obtain this kind of help from Microsoft. Everything is so wizard driven with no explanation as to what you are really doing.

  60. I am trying to get the workdaysform to work and I have the same kind of problem as dev. But when I aplly the 2nd rule to my BegDateWN I get this message (in danish) “xdXDocument:GetDOM(“daysOfTheWeek”)/somestuff/holydays/my:BeginDate” refererer ikke til en gyldig sti til placeringen af et felt eller en gruppe
    But it says that the path to BeginDate is not a valid reference. I have tried to pick out the BeginDate, so I can see that it is actually there but why does it then say that it is unvalide?

  61. I am trying to calculate time in InfoPath. I would like to be able to total the amount of hours/minutes as well as average the hours/minutes reported for each day. Is this possible? I would really appreciate your help.

  62. Hai..

    I downloaded ur form its working.
    But when am creating new form its not working it giving error.
    “Function ‘AddDays’ did not return a value, or it returned a value that cannot be converted to an XSL data type.”
    I am using infopath 2010
    Please help me.
    Thanks in advance
    iqbalkmk

    • IP 2010 is not an official release yet. So i’m not going to investigate what is going on here. I would recommend to seek some help on prerelease Microsoft Office forums.

  63. I downloaded your form, and its working fine.
    However, when I’m creating a new form its not working. I get the error. “Function ‘AddDays’ did not return a value, or it returned a value that cannot be converted to an XSL data type.”
    I am using infopath 2007
    Help!! Thanks in advance
    Andy

  64. Hi Alec.
    I’ve finally managed to get my form working in IP but now that I’m publishing it to my forms library I’m getting an error…
    “There was a filure loading WorkDayCalc.dll. Details: The specified user does not have a valid profile. Unable to load ‘WorkDaysCals, Version=1.0.3793.17538, Culture=neutral, PublicKeyToken=f8a230bbef5ac874′..

    Can you help with this?
    Have you come across it before?

  65. Hi Alec,

    Thanks so much for this solution it’s been a huge help. I do have one question although it’s not a major issue. I have been able to get all the rules to work and the work days calculates correctly however, if I change the “BeginDate” field to a date in the past I get an error message that “A calculation in the form has not completed. Number of calculations used exceeded the maximum limit” – any idea what might be causing this? The Work Days still calculate correctly but I’m worried this error popping up will cause confusion with users of the form.

  66. i was doing the tutorial for the difference date and i got the following error message when I try to add formula for the Main rule in Add difference … please let me know if you have a clue thx

    Unexpected character.
    concat(substring-before(., “;”) + substring-after(., “;”) * ((translate(xdDate:AddDays(../my:startDate, substring-before(., “;”)), “-“, “”) –<– 1), ";", substring-after(., ";") div 2)

  67. I did couple times but it’s returning this error modifying the code do you have another idea what could be wrong

  68. Hi Alec

    Great information thank-you – I am new to InfoPath and when I try to download your sample I cannot get it to open in design view – is this an option and if so how do I do it. I am using the 2010 version?

    Thanks RJ

  69. Hi Alec – no design option probably something new in 2010 and/or Windows 7. Lots of other “Open with” options.

    Thanks RJ

  70. when i want to use this form, with data from a sharepoint list not with the xml.
    can the date format from sharepoint give an issiue?

  71. i think i understand very good.
    i have changed the second rule in the beginDateWN en endDateWN the count function with my own date.
    and my holidays that stands in a list wont be counted down from the total.
    this is the ownly rule i must change i think i’m i wright?

  72. when i check my listdate the date that stands there is yyy-mm-dd uu-mm-ss, in the sharepoint list it stand like yyyy-mm-dd.
    this was my first mistake i thouht.

    do within my data connection i have placed a rule on the date like this: concat(substring(date,1,4),”-“,substring(6,2),”-“,substring(9,2))
    wo now i think that my date will be proced properly.
    or i’m i wrong?

    • I would use translate(substring-before(../my:endDate,” “), “-“, “”) that formula removes “uu-mm-ss” part

      • it has been a while since i continued working on it.
        when i recieve my list of data from sharepoint.
        how can i iterate over every item in this list?
        must i write this in code or can this be done with rules?
        and how?

  73. hi, i’m trying to use the workdaysCalculation

    when i use the action:
    concat(substring-before(., “;”) + substring-after(., “;”) * ((translate(addDays(basepoint, substring-before(., “;”)), “-“, “”) < translate(@VM_DATUM_VAN, "-", "")) * 2 – 1), ";", substring-after(., ";") / 2)

    the BegDateWN i got this error:

    Node-test expected.
    concat(substring-before(., ";") + substring-after(., ";") * ((translate(addDays(basepoint, substring-before(., ";")), "-", "") 2<–)

    can someone help me with this?

  74. Hi thanks for the post
    I am getting the error below when I publish the form or if I use it in customize form in SharePoint 2010. It would be great if you could tell me the reason?
    An error occurred in the form’s rules or code. The number of rule actions or the number of calls to the OnAfterChange event for a single update in the data exceeded the maximum limit.

    The rules or code may be causing an infinite loop. To prevent this, ensure that the rule action or event handler does not update the data which causes the same rule action or event handler to execute.

  75. Thanks for the quick reply. But my current scenario is if I create a custom list in SharePoint 2010, and click on Customize form under the List, and use the same fields and rules correctly and publish, then it won’t work.
    When I try to add a new item in the list it throws the following error

    Default values, rules or code may be running in an infinite loop. To prevent this, ensure that the default value, rule action or event handler does not update the data which causes the same rule action or event handler to execute.

    But if I create an InfoPath form based on SharePoint form library template and publish to a form library then it works fine.
    Why is it not working for Custom List?

    • There is no difference in behaivior regular forms and customizable list item for. Even if there is some difference you have to contact MS support and report it as bug. But most probably you just missed some condition/formula.

  76. I appreciate your help, I tried creating the form again in the Custom List, that error repeats. There must be some bug with it.
    I have one question about the formula that we are using here. Could you clarify my doubt
    for the first iteration,
    StartDate = 2010-10-19
    EndDate = 2010-10-22
    concat(0 + 4096 * ((20101019 < 20101022) * 2 – 1), ";", 4096 / 2)
    gives -4096;2048
    My understanding is, it calls recursively and comes to the result 3 at some point. My question here is
    1. How can the Concat() function gives a result with out ';'
    2. In the above Concat expression, what is the value of (20101019 < 20101022). It should be a boolean value right? Thanks in advance.

    • 1. It’s done by the second rule is called “finalize”
      2. If boolean expression enters in arithmetical one it converts on a fly to the 0 or 1 depending on true/false

      • I understand that the Main Rule condition will become false eventually and second rule gets triggered. How will the Main rule condition becomes false? Could you explain the algorithm behind? I tried to calculate the formula in the rule it seems like it would never violate the condition on Main rule. Please correct me if I am wrong.

      • Look at the condition:

        xdDate:AddDays(../my:startDate, substring-before(., “;”)) != ../my:endDate and contains(., “;”)

        If both start date and enddate are the same the rule wont be triggered at all – AddDays function will add 0 to the begin date so the output of the AddDays function will be equal to the endDate and because of that the condition will fail. If the dates are different the formula ensures that changing the dayDifference field will eventually yelds the figure adding which to the statrtDate will produce result exactly equal to the endDate and this will make condition to fail.

  77. First of all, sorry for bothering you much. You said, if the dates are different the formula ensures that changing the dayDifference field will eventually yields the figure adding which to the statrtDate will produce result exactly.

    My question was how will it eventually yields to the result?
    If I calculate the formula in the rule the sequence goes as below
    -4096;2048

    -6144;1024

    -7168;512

    -7680;256

    -7936;128

    -8064;64

    -8128;32

    -8160;16

    -8176;8

    -8184;4

    -8188;2

    -8190;1

    -8191;0.5

    -8191.5;0.25

    -8191.75;0.125

    -8191.875;0.0625

    -8191.9375;0.03125

    -8191.96875;0.015625

    -8191.984375;0.0078125

    -8191.9921875;0.00390625

    -8191.99609375;0.001953125……..

    This sequence does not seem to give the result.

    Thank you so much for all your replies.

    • Your very first iteration is incorrect.
      Copy this expression to the Expression box:
      4096 * ((20101019 < 20101022) * 2 -1)
      it will produce 4096 not -4096 as in your sequence.

      The second iteration would be
      2048 * ((20220105 < 20101022) * 2 -1)
      it will result to the -2048

  78. Is there a codeless way to add only workdays and exclude holidays from the total. I need to enter a date and add 29 business days to that date to produce a date in the future. The 29 business days would exclude holidays and weekends. Thank you for any assistance you can provide.

  79. I adjusted this to work with DateTime fields by adding substring-before(datetimefield, “T”) in all references to date fields. Also dayDifference can be an Integer field and it still works.

  80. Hi, thnx for this good example!

    I have only one question, if I want to add 1 day to the daydifference, what formula can I use in the Finalize rule, do you have an example?

    Thnx in advance!

    kind regards,

    Sunil

  81. Hi Alec,
    i have been trying to use your workdays calculation.
    but in my case i want to use it in a repeating section.

    so when the user load the form his dates he once have added will load. and he can add a new date. but whenever he add a new date, it gives me this error:

    An error occurred in the form’s rules or code. The number of rule actions or the number of calls to the OnAfterChange event for a single update in the data exceeded the maximum limit.

    The rules or code may be causing an infinite loop. To prevent this, ensure that the rule action or event handler does not update the data which causes the same rule action or event handler to execute.

    if i use the workdays calculation not in a repeating section it works fine.
    how can i fix this so it works in a repeating section?

    thnx

    • In the example form there is a repeating section that calculates date difference. Replace date difference calculation with work days calculation.

      • Sorry, but i dont understand what you are saying.

        i am using the work days calculation (WorkDay’sCalc)example form

      • ok, i think i know what you mean.
        but can you explain to me. how i replace the date difference calculation with workdays cal?

      • You have to analyse in what way day difference XPath in non repeating section is different from the same XPath in repeating part. Then apply this pattern in your form. I will add that code to the form in 2-3 days (when I’ll have time for that) in case if this task is too complex for you.

      • ok, many thnx for your reply

  82. Hi ASlec, first of all great blog ypu have.Second i have a issue too it is posible to have this days calculation on Infopath 2003 ? nd how i had search on www but still no luck so far for asolution for 2003 Infopath.Thanks

  83. Здравствуйте!
    Подскажите пожалуйста, а русскоязычного описания данного вопроса у Вас нет? :)
    Для меня это очень важно. Буду очень признателен! спс

  84. Hello!
    Tell me how to do sorting in ascending order in a repeating table on a particular field?
    Thank you:)

  85. Hello!
    Prompt Please, I form a query that displays data in a repeating table, I need to sort ascending line at the specified column, how to do this?

  86. Another question. It is possible to build queries on a single date from the calendar. Is there any possibility to query from the database by month, or set a date range?

  87. Сдраствуйте увожаемий,

    помогите пожалуста вставит филтр в “Work days calculation” для не полных рабочих дней, таких как новий Год например. Надо чтобы шеталосъ как половина виходного.

    За ранее благодарю.

    • Добавте дополнительное поле halfday в XML файл к holydays:

      hday name=”Holyday1″ hdayn=”2009-05-25″ halfday=”1″/

      модифицируйте эту часть формулы:

      count(xdXDocument:GetDOM(“daysOfTheWeek”)/somestuff/holydays/hday[translate(xdXDocument:get-DOM()/my:myFields/my:BeginDate, "-", "") = translate(@hdayn, "-", "")])

      чтобы выглядела так:

      count(xdXDocument:GetDOM(“daysOfTheWeek”)/somestuff/holydays/hday[translate(xdXDocument:get-DOM()/my:myFields/my:BeginDate, "-", "") = translate(@hdayn, "-", "") and (@halfday="1")]) – 0.5*count(xdXDocument:GetDOM(“daysOfTheWeek”)/somestuff/holydays/hday[translate(xdXDocument:get-DOM()/my:myFields/my:BeginDate, "-", "") = translate(@hdayn, "-", "") and (@halfday="0.5")])

  88. Добрий день,

    спасибо за помошь, но чегото я делаю не правилно. Если ето возножно поправте пожалуста.
    Так я изменил XML
    hday name=”Holyday1″ hdayn=”2009-05-25″ /
    hday name=”Holiday2″ hdayn=”2010-12-24″ halfday=”1″/
    но как нужно изменить формулу и какую из них, я незнаю.
    Я так думаю что нужно в “beginDayWE” и “endDayWE” во второй формуле менят, но у меня невыходит.
    Вот формула из “BeginDay”:
    (substring-before(../my:bisWE, “;”) – substring-before(., “;”) + substring-before(., “;”) mod 7 – substring-before(../my:bisWE, “;”) mod 7) * 5 div 7 + substring-before(../my:bisWE, “;”) mod 7 – substring-before(., “;”) mod 7 – (substring-before(../my:bisWE, “;”) mod 7 = 6) – (substring-before(., “;”) mod 7 = 0) + 1 – count(xdXDocument:GetDOM(“daysOfTheWeek”)/somestuff/holydays/hday[translate(xdXDocument:get-DOM()/my:meineFelder/my:von, "-", "") = translate(@hdayn, "-", "")])
    что здесь нужно поменять?
    Прошу извенить меня за мою тупость, так как я в етом деле полний чайник. Но формуларчик очень нужен.
    Спасибо.

    • Извините, потерялось часть XML при копировании:

      hday name=”Holyday1″ hdayn=”2009-05-25″ halfday=”0.5″/ (половинный день)
      hday name=”Holiday2″ hdayn=”2010-12-24″ halfday=”1″/ (полный день)

      Менять нужно вот эту часть:
      count(xdXDocument:GetDOM(“daysOfTheWeek”)/somestuff/holydays/hday[translate(xdXDocument:get-DOM()/my:meineFelder/my:von, "-", "") = translate(@hdayn, "-", "")])

      а как – посмотрите в предыдущем посте.

      • Добрий день,

        еше раз спасибо за помошь я всё сделал так как вы сказали, но чтото до сих пор работает неправилно. Если я выбираю с 24.12.2010 по 24.12.2010 тоесть всего один день то формула вышитывает мне правилно 0,5 дней но если например с 20.12.2010 по 27.12.2010 то я получаю 5 полних дней в ответ хотя должно быть 4,5 дней. Что мне нужно потправить.

        Спасибо

      • Это потому что WordPress убирает символы в формуле :( , но попробую исхитриться:

        count(xdXDocument:GetDOM("daysOfTheWeek")/somestuff/holydays/hday[translate(xdXDocument:get-DOM()/my:myFields/my:BeginDate, "-", "") <= translate(@hdayn, "-", "") and translate(xdXDocument:get-DOM()/my:myFields/my:EndDate, "-", "") >= translate(@hdayn, "-", "") and (@halfday="1")])-
        0.5*count(xdXDocument:GetDOM("daysOfTheWeek")/somestuff/holydays/hday[translate(xdXDocument:get-DOM()/my:myFields/my:BeginDate, "-", "") <= translate(@hdayn, "-", "") and translate(xdXDocument:get-DOM()/my:myFields/my:EndDate, "-", "") >= translate(@hdayn, "-", "") and (@halfday="0.5")])

  89. ок спасиба,

    попробую в понеделник и отпешусь.

  90. hi
    i am using this concept in my infopath i am getting below warning.
    There has been an error while processing the form.

  91. спасибо болшое!

    вы гений!!! всё работает так как я себе предстовлял.

  92. I am using your calculations for a timecard and your example is great, my only trouble is recreating your “hours” repeating table. When I add yours to a form it repeats to 23, mine just stays at 00

  93. My Time fields are set as dropdowns to “get choices from fields in this form” and point to the “hours” Xpath like yours

  94. So you do not know how to recreate the “hours” repeating table from your form?

    • I do. But it depends on what version of InfoPath designer you are using. Also the questions like “how to use InfoPath designer?” is kind of out of the scope of this blog. Yet another reason to encourage you to do some homework ;)

      • I am actually pretty good with designer and am using 2010. I have changes all the fields rules in your form to include the Xpaths in my new form so I have a fiar idea of how to use the tool. I think the issue is that you were using an older version of Infopath.

      • >I think the issue is that you were using an older version of Infopath
        Nope. The form was designed in IP2010

      • I found the information by going into the advances settings of the form. Seems like it woudl have been easy to direct me where to add the 00, 01, 02. But I found it. Thanks for all your calculations though – that stuff is very tricky.

  95. I did a search on your string and am following some instructions now- thanks

  96. On the hours calculations sheet it says that the minutes would not be hard to add if needed. Could I possible get some instructions on how to do that. I need to calculate the minutes as well.

  97. Hi Alec:

    How can I convert this Excel formula into SharePoint 2010 formula for a vacation request form I am creating?

    Hannu

    • Sorry I dont understand your request. There are no Excel formulas in this blog.

      • I do understand there are no Excel formulas on this blog but what I am asking of you is what the formula would be in SharePoint to do what I have done in Excel. That formula is what i need to use in my vacation list to calculate remaining status on leave?

      • Do the search. I think I saw column formula for work days calculation somewhere.

  98. Hi Alec,

    I want to use your WorkdaysCalc and you are talking about the XML, where can I find this file and is there any information to be changed?

    Hannu

    • Please read my replies to others. The question is already answered

      • Thanks Alec I have figured it out, my next question is on the dates in the XML file, i see you are using the year how can we have this as concurrent and not specified?

      • In your XML file under Holidays you specify the dates in full like Hol1 = 2009-05-25 and Hol2 = 2009-05-26, what happens now when it is 2010, do you need to change the year in the file every new year? Hope this makes more sence

      • No, you don’t need to change dates every new year. Usually people for whom common sense is not just empty sound prefer to fill XML with all holidays for let say 10 years and then document this. In enterprise enviroment it shouldn’t be XML file at all. It should be a web service connection to the HR system because holidays are different in different countries and also there is such thing as firm holidays.

  99. To Hannu: Sorry, I don’t understand your question. Could you please rephrase?

  100. A lot of good comments. I am trying to figure out how to count a specific date. I have a SharePoint list with class dates and normally these dates are on a Friday. On my InfoPath Web enabled form, I have a text field called class_date. I want to count all the dates on the SharePoint list that match the class_date on my form. Is this possible. I can do it using code but it does not work when the form is Web enabled.

  101. This is just great for a non code person as me :-D

  102. Thanks for the great post. Question: I need to calulate the number days it takes to complete a task while the task is in progress (i.e. today-startDate) and only once there is a value in endDate do I want to calculate days to complete (i.e. endDate-startDate). Both results need to appear in the same field dayDifference. My fields are:
    startDate
    endDate
    today
    dayDifference

    Pls help!
    Much thanks!

    FL Newbie

    • Why are you limiting yourself with just one dayDiffererence field? What I would do is calculate endDate-startDate on submit event then use different result field to show days to complete that is calculated on open event.

  103. Alec, Thank you very much for your code and examples! I’m having one issue though. I have to show the number of hours instead of days, Example 1 day would = 8 hours. I tried create a new field and just enter a default value of (daysWorked *8) but as you commented to another post adding any other cauculation causes an Error. In that post you said to use the “Finalize” action to stop this from happening. But I can not figure out how.

    • Finalize in this case is just the last action in a sequence.

      • Yes I see that now. It’s just an added action that stops the rule. I apologize beforehand that I have very little experience with rules. I’m just starting to pick it up through blogs like yours.

        Can you explain to me how, when using the WorkDays calculation I can multiply the sum of the days worked by 8 and not get the error?

      • Alec please respond. I really need help with this, please. I just need to know how I can use the sum of daysWorked and multiply it by 8 without getting an error.

      • Just download the form that calculates hours difference and multiply by 8 in the similar spot of WorkDaysCalc form.

      • Alic, I asume you mean to add a new rule to WorkDays that says.. If workDays contains “;” then substring-before(., “;”) * 24 + substring(EndDate, 12, 2) – substring(StartDate, 12, 2) * 8. But this throws the same error.

        P.S. I’m sorry to keep bugging you about this. I promise that once I get this fixed I will never post again! :)

      • Nope. I mean you should edit the existing rule not to edit a new one. If you’ll replace my formula with yours everything should work.

      • here is a copy of the file it that at all helps. Thanks.

      • But what rule do i modify?

      • The last one that is on the field that calculates business days.

      • I appologies, I’m not a programer, from your discription I have no idea what field or expression I need to use. All I want to do is take the sum of daysWorked and mutilpy it by 8.

      • The stuff here is quite advanced. May be you should ask programmers in your organization to help you. As for myself I have troubles to make myself doing something that is too simple but requires quite noticable amount of time.

  104. I have mirrored your settings in my web-based form and still get ‘0;4096′ displayed in my DateDiff field. Changes were to field names to be usable by my form. Date Pickers take input correctly, but the DateDiff field displays the input from the endDate field rule rather than the calculation from the rules withinthe control.
    First question would be, what does the ‘0;4096′ represent? Second, why is it being sent to the DateDiff control with no designation of origin? Maybe I am just not getting it. I really appreciate your help with this stuff. I am in an environment that does not allow ‘custom code’ without a great deal of review.

    All help is greatly appreciated!
    Dale

    • You havent copied everything. Couple rules at DateDiff control are missed.

    • Also if you’ll try to read comments above here there is discussion with explanation of what ‘0;4096′ does mean.

      • Alec,

        Did not see the explanation of the ‘0;4096′, just a reference to it in the calculation and an iterative table of the person’s calculation steps.

        I have built the form from scratch and still get the same ‘0;4096′ result in the dateDiff field.

        I copied and pasted your inputs from the tutorial. My rules look like yours, the fields are set up like yours (dateDiff field differs slightly) I am obviously missing a crucial part, but do not see it.

        Tried this alternative;

        http://www.bizsupportonline.net/infopath2007/calculate-date-difference-infopath-rules-formulas.htm

        It works, but seems very cumbersome and I cannot follow it to do any edits.

        Thanks in advance for your help,
        Dale

      • If you’ve copied 2 rules at dateDiff field and you still can’t make it work that means the condition on rule is failing. Fix the error in condition formula.

      • >Did not see the explanation of the ’0;4096′, just a reference to it in the calculation and an iterative table of the person’s calculation steps.

        Exactly. He is demonstrating whats happening in form after dateDIff field got assigned with 0;4096 value

  105. Hey,

    Can i use the same principal to calculate the week number, if yes how?

    thank you in advanced guys

  106. This is asking a lot Alec, but could you please put it in a form and upload the form please.

    I would really appreciate it

  107. This is probably the best post for working with text type fields in infopath .Even the templates are available for download .

    Please keep the good work coming …:)

  108. Alec,
    After copying your exact formulas to calculate the day difference between datepickers in my own template, all runs O.K but sometimes, after changing the startdate or enddate values I get the message error of : ”A calculation in the form has not been completed. The number of calculations used for a single action exceeded the maximum limit.”
    I have checked all the rules, formats, expressions but I don’t find the error. It seems to be in the finalizerule.
    Please, help!

    Thanks in advance,
    Jesus

  109. Alec,
    Continuing with my last post:
    There is no error when selecting in both date pickers odd or even date values. But if you mix odd and even values, you get the error.
    I hope it helps.
    Thank you.

  110. This has saved my hide, is it possible to work backwards and set the end date based on the a specific number of workdays? i can find examples of code for this, but I can’t find any real specific instructions on how to make that code work (and since I’m not a great coder…)

    Thanks for the great solutions!

  111. Hi Alec – thanks for this posting! I downloaded your Working days/business days sample file and added my own holidays to the daysofTheWeek resource file, but it doesn’t calculate correctly and I’m not sure why. Some holidays work and others do not – the only thing I can pick out is that holidays crossing a Friday or weekend seem to have an issue. Any ideas why that sample file isn’t calculating correctly? Thanks!

  112. Hi, I have been looking at the posts and are indeed right on target. I seem to have a problem.

    I have two dates in my IP 2007 xsn both showing up with Date Time format. I need to autodistribute the form and wish to use the date from the above field as Due Date.

    Earlier assistance will be much appreciated.

  113. Hi,
    Ur steps r too good for what i expected..
    But i got a value o;4096 in both date difference control named starting and ending date……
    i didnt get a correct value for these control…
    im expecting the value for these controls like 45,56…….
    so plz do reply as soon as possible

  114. I saw a few comments about this but didn’t see a clear response. I’m getting an error on a browser based form using the workdaycalc template. It works fine if the form is opened in the client but not the browser. I have verified the code for all the fields, and am at a loss. I have this solution in use in a few places in my environment, but am only having problems on this one form:

    An error occurred in the form’s rules or code. The number of rule actions or the number of calls to the OnAfterChange event for a single update in the data exceeded the maximum limit. The rules or code may be causing an infinite loop. To prevent this, ensure that the rule action or event handler does not update the data which causes the same rule action or event handler to execute.

  115. Hello there!

    your work is truly amazing and it helped me a lot, though, I have a very small problem, which is just I need to specify that the weekend is “Friday and Saturday” instead of “Saturday and Sunday”, I have searched a lot but didn’t get a way in till now.

    Thank you in advance.

  116. Btw, I even tried changing the value of each week day in the secondary data source, replacing Saturday by Friday, and Sunday by Saturday, and keeping the same values for the rest of the week days but it’s still the same.

  117. Yes I do notice that dude :)), you didn’t get my point as in my first post, I want to specify that the weekend is Friday and Saturday instead of Saturday and Sunday. how to do this?

  118. I’ve followed the instructions exactly. The result that I get in the dayDifference field says “true” rather than a number. The data type of the field is “Text” as per the instructions. I’m using IP2010 but forced to save in IP2007 format due to company restrictions.

    • >I’ve followed the instructions exactly.

      No you are not. The formula output is numerical not boolean. If you’ll copy my formula and yours side by side the difference will be obvious.

  119. dayDiff example

    Alec, Like the others I thank you for your contributions. However, after following your steps and verifying all values and formulas match your example, I receive the “infinite loop” error. The data in my dayDiff field after cycling through the error is -8191.5;0.25.

    Do you have any suggestions how I can work through this?

    Thanks

  120. Hi Alex,

    Would you know how to sort a list box using just xpath? I can’t use the owssvr method.

    Thanks,

    Sara

  121. Fantastic help, and brilliant files! Thank you!

    Just one thing: I am using the “workdaysCalc.xsn” for a holiday requisition form at work, but every time anyone visits to the form, the BeginDate defaults back to “today’s” date, irrespective of what the person who submitted the form set it to (sometimes as far as months in advance). Is there any way of stopping this (i.e. not have today’s date as the default value)?

  122. Awesome stuff. Many Thanks!

  123. I could not get the following to work in my form, any suggestions?

    field: dayDifference
    xPath expression: concat(substring-before(., “;”) + substring-after(., “;”) * ((translate(xdDate:AddDays(../my:startDate, substring-before(., “;”)), “-“, “”) < translate(../my:endDate, "-", "")) * 2 – 1), ";", substring-after(., ";") div 2)

  124. Down loaded the working version of this but I still get: 0;4096 as the day difference, what now?

  125. Very nice solutions for date difference calculations in InfoPath.

    But I get an “possible infinite loop” error in the repeating section example of the WorkDaysCalc.xsn. The normal calculation works fine.

  126. I was able to replicate the recipie but I had to explicitly match fields using the Rules Inspector. Once I adjusted everything, it worked as displayed in the example.

  127. Hi Alec
    Thanks a lot for sharing you knowledge and for patience answering all these question. That is very helpful.
    The problem I have is with holidays.
    For example I have

    If I choose
    Start 10-5 End 10-05 –> 1 requested day
    Start 10-5 End 10-06 –> 0 requested day
    Start 10-5 End 10-07 –> 1 requested day and so on…

    On the other hand with 2011-10-12
    If I choose
    Start 10-11 End 10-11 –> 1 requested day
    Start 10-11 End 10-12 –> 2 requested days
    Start 10-11 End 10-13 –> 3 requested days and so on…

    All things except holidays (weekends )work perfect
    Any advice
    Thanks

  128. Sorry forgot to mention about chosen holidays in a file
    hday name=”Holyday1″ hdayn=”2011-10-06″
    hday name=”Holiday2″ hdayn=”2011-10-12″

    • Sorry, I’m not able to reproduce this. if I assign the holidays as it is in your example the sequence is the following:
      Start 10-5 End 10-05 –> 1 requested day
      Start 10-5 End 10-06 –> 1 requested day
      Start 10-5 End 10-07 –> 2 requested day and so on…

      same with second date:
      Start 10-11 End 10-11 –> 1 requested day
      Start 10-11 End 10-12 –> 1 requested days
      Start 10-11 End 10-13 –> 2 requested days and so on…

  129. hello every one.

    please explain how to does this in infopath 2010.

  130. Hi Alec,

    Sorry, I know you mentioned if someone is receiving the following error message in the date difference field as 0;4096. How can I get the form not to assign the dateDiff field with the value 0;4096? Do I need to turn on Microsoft Visual Studio Tools for Applications?

    Respectfully

  131. it worked greatly, thanks a bunch sir

  132. HI Is there a way I can use this to get the return date(next working day)

    great post by the way

  133. i am always impressed with you work. I have a similar requirement. The difference is i want to compare my enddate, in the repeating table to my start date , not in the repeating table. somewhat of a combo between your example form. When I tried to implement this IP errors with max number of calculations exceeded. Althought once the error is acknowledged, the day difference is correct. Is there another way to compare the dates if one is in a repeating table and the other is not?

    • In addition, it also errors with max calc exceeded with the end date is before the start date. I am trying to figure out a way to circumvent this.

  134. Hi Alecpojidaev,
    Thank you very much for sharing your work with us. I need calculate Task due date by adding Business days to task start date, some tasks has 30 minut so I used AddSecound, for the working day part , I converted your example by deleting End date( Date picker )and EndDateWN add text field called Task due date, in total I have two text field one date picker.

    for example: Start Date is 24/11/2011 and we have 3 working day to finish the task, it should be display the 28/11/2011 for Task due date.

    I copied the EndDateWn rules to taskDueDateWN :
    the condition expression : xdDate:AddDays(xdXDocument:GetDOM(“daysOfTheWeek”)/somestuff/basepoint, substring-before(., “;”)) = ../my:workDays

    Set value to:
    (substring-before(., “;”) – substring-before(BegDateWN, “;”) + substring-before(BegDateWN, “;”) mod 7 – substring-before(., “;”) mod 7) * 5 / 7 + substring-before(., “;”) mod 7 – substring-before(BegDateWN, “;”) mod 7 – (substring-before(., “;”) mod 7 = 6) – (substring-before(BegDateWN, “;”) mod 7 = 0) + 1 – count(hday[translate(BeginDate, "-", "") = translate(@hdayn, "-", "")])

    But I am getting errors
    Could you please help.

    • It’s not practical to use increments less than 1 day in this method. Also it’s not clear from your post what are the actual changes you’ve made.

      • Hi Alec,
        What I am goining to do is add working days to A date fild and find Due date in Infopath, upside down to your example, I manage to do it on Calculated column and workflow but could handel bank holidays, want to do it Infopath.

        Thanks

  135. Hi, I test this rules and formulas and works greate but when I put them on a web-browser Infopath 2010 form i get the loop error, con you tell me if I need to modify something

  136. Hi Alecpojidaev,
    I have downloaded your file and it does work perfectly.
    I am using IP 2010 and when I want to add other fields to your form and do a lookup to an existing list with a filter, it tells me what I am trying to do is not valid in 2007 and I have to remove it. Is there a way to convert the form to a 2010 version?
    I am trying to copy all of your formulas over to a 2010 form, but I am running into an issue on the BegDateWN rule 2. It is telling me that I am referencing a field that is not valid. I will try again.
    Please let me know. I appreciate all your hard work.
    Karin

    • I’m not aware of the process of converting forms to 2010 version. But when I had have to redo everthing in IP 2010 I hit absolutly no problems (implemented same datadifference in list form)

      • I tried to move everything over, but I am getting the same error that others have gotten about a loop. If you have done the same in a 2010 version, is it possible for you to post that form along with the others?

      • This error is urelated to the version of InfoPath. It’s just matter of carefully following instructions.

  137. Would you be willing to look at my form if I sent it to you?

  138. can you send me an email to [removed] and I will reply back to you.

    • [removed] is the link to the file on our website

      • Ok. Here is the problem. In sharepoint field there is no just date field as it is in my example. It’s always date and time field. And this means that internal representation of date field always will be yyyy-mm-ddT00:00:00. So to adjust your form you will need to remove the time part in every formula. For example condition at Rule 1 at BegDateWN field should be looking like this :
        xdDate:AddDays(xdXDocument:GetDOM(“daysOfTheWeek”)/somestuff/basepoint, substring-before(., “;”)) != substring-before(../my:beginDate,”T”). There are like 4 or more places in form that needs to be adjusted.

      • Thank you so much for looking at that. I will make the changes and try it again. I was pretty sure I had followed all those instructions and I read every comment in this post twice to see if someone else had the same issue. I will let you know what happens. Again thank you soooooo much!!!

      • No problem. Don’t forget to add test on empty BeginDate at EndDate field. If you not prepopulating it as it is in my form you will need it.

  139. Hello alecpojidaev:

    I have success — no more loop error message.

    It is calculating without any errors the correct # of days, but it is not subtracting the holidays in the xml file. I used the same xml file with your form and it does calculate without the holidays. I believe it has to do with the date field in the second rule on the EndDateWN and BegDateWN fields. I am not sure how to structure the filter used in the -count portion of the formula.

    translate(xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:beginDate, “-“, “”) substring-before(beginDate,”T”) and the same for the endDate. I then changed the formula to use these fields.

    translate(xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:BegDateOnly, “-“, “”) <= translate(@hdayn, "-", "")

    I'm sorry to be such a pain, but I am so close now. Can you help me one more time. Let me know if you want me to post the form so you can review again.

    Thanks again for all your help,
    Karin

    • Part of my post is missing:

      second paragraph should be:

      translate(xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:beginDate, “-”, “”)

      I created 2 new fields that calculated to jus the date —> substring-before(beginDate,”T”) and the same for the endDate. I then changed the formula to use these fields.

      • Creating new fields might cause an error. The prefferable way is in my previous post. If you still want to use additional fields use 0;2048 value instead 0;4096. It will buy you additional step in calculations without excesing calculation limit.

    • Instead of
      translate(xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:beginDate, “-”, “”) substring-before(beginDate,”T”)

      you should use
      substring-before(xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:beginDate, “-”,””), “T”)

      • It did not like that:

        [translate(–>substring-before(xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:beginDate, “-“, “”)<–, "T") <= translate(@hdayn, "-", "")

        I took out the new date fields I added. Back to only your code.

      • The quotes are not right ones.
        also tails of functions are incorrect
        here is how it shoud be:

        [translate(substring-before(xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:beginDate, “T”), “-”, “”) <= translate(@hdayn, "-", "")

  140. That did it, it is working perfectly now.

    Just want to say “Thank You”. You have been wonderful in walking me through this. If anyone else is having an issue with IP2010, I would be happy to share my form with them so they can see the changes that needed to happen.

    Thanks again for everything, have a GREAT weekend and holiday season!!

  141. This is an awesome resource for InfoPath codeless programming! Google keeps taking me here. However, I have requirement for vacation time to display the business days requested between two date fields. As an example, if the start date for vacation is 12/27/2011-12/30/2011, HR needs to see that what is requested are 12/27, 12/28, 12/29, 12/30. Anyway to tweak your date calculation to display the days in the middle? Any assistance is greatly appreciated!

    • I really don’t know

      • I will attempt this, but my initial thought is to start with the quasi-pseudo/real code to create the codeless programming in InfoPath:

        while (endDate < startDate)
        addDays to startDate
        display new +1 Day
        increment 1

      • It can done. But the info you want have to be displayed in precreated expression boxes with rules that hides/unhides them depending on AddDays condition.

      • Any idea on how this may look in codeless programming. I cannot figure this out.

      • Create as many expression boxes as maximum you expect ever have. The expressions shoud be AddDays(BeginDate,expressionBoxnumber). The visibility shoud be based on (begDateWn + ,expressionBoxnumber) mod 7 expression

  142. If there a way to calc half day if adding one more fields AM/PM?

    • Yes there is. Although please don’t expect me to provide a solution to you. This blog purpose is to describe and discuss general concepts.

    • Alec – I did this on the days off application I did. If you provide me with an email, I can send you the form to upload. It also has all the changes required for 2010.
      Thanks, Karin

      • Thanks Alec and Karin. I am a newbie to InfoPath. Karin could you send me the form by email? My email address is []. Thanks very much.
        Edited by Alec Pojidaev (email address is removed)

      • Karin – I get your form in this post, thanks. It seems that your form cannot calc the following format:
        Start Date AM/PM – End Date AM/PM.
        Alec’s algorithm is amazing. I will try to improve my form with his rules. Once done, I can send the form to upload.

  143. William – we either have half day (AM or PM) or full days. The form works for what we need. The calculations for the full days is totally based on Alec’s code and works for SP2010. Can’t wait to see what you do with it!

  144. dateCheck.xsn is great! I used the rules to calculate 3 sets of start date and end date differences. I need to now get the sum of the 3 sets of differences. When I try my:dateDiff1 + my:dateDiff2 + my:dateDiff3 in a separate calculated field I get “A calculation in the form has not been completed. The number of calculations used for a single action exceeded the maximum limit.” Any help is appreciated.

    • Use 2048 value instead of 4096. Another posibility is to present this sum in an expression box and if you need also submit the sum, not just present do the sum operation at submit event.

  145. hi, thanks for the info. But, I am having similar problem and getting the ‘an error occurred in the form’s rules or code.”. I tried disabling each rule in my form and the one that created this error message was the mainrule. I tried typing the mainrule and finalizerule manually vs. copy/paste but I cant get it work. I’m customizing a form from an existing SharePoint list. I dont have any other rules just these 4. I also downloaded the datecheck.xsn which works perfectly, but the same rules does not work on my customized form. Any help is greatly appreciated. Thanks.

    • >but the same rules does not work on my customized form
      Sorry, but the only possible explanation here is they are not the same.
      The problem usally is in the condition of the rule. Double check conditions.

  146. Hi Alex,

    I was wondering if you have successfully managed to do this the other way round? As opposed to finding out how many working days (not weekend/holidays) between two dates, start with a given date and add a number of working days to it.

    I keep butting up against the number of rules limit in trying to achieve this. It seems that, without an obscenely large formula (I’m not sure what the limit are on the length of infopath formulas is) this needs to be done in stages;
    1) calculate number of actual days to account for weekends
    2) find out if there are holidays in between the two dates
    3) use method in 1 but instead of original work days, use work days + result of 2) but this might push the end date on to another bank holiday (e.g. easter weekend)
    4) similar to 2)
    5) similar to 1)… ad infinitum (or at least enough that you can hit the rule execution limit.)

    Have you had a go at this? Do you think this is possible/achievable? I was able to reduce the amount of rule executions by shifting the basedate based on the start date so that the number of rule executions to determine the offset can be reduced but I’m still using too many rule executions (also, the formulas are beginning to get silly in length…).

    Any suggestions would be greatly appreciated.

  147. If anyone needs to check the difference between two dates in order to hide/disable a section of a form after a specific deadline, I found the following approach to work:

    Specify the expression below inside a condition:

    translate(“2012-02-21″, “:-T”, “”) <= translate(xdDate:today(), ":-T", "")

    In regard to the method outlined in this article does anyone know the significance of setting dayDifference to 0;4096 . I gather that it somehow tricks infopath's "16 calculations" limitation but I don't really understand how it accomplishes that.

    • This approach is not tricking “16 calculations” limit. It just makes this limit bearable :). Try to google “binary search”. The algorithms utilizing binary search are very savvy in terms of number of calculations compearing to any other type of algorithm.

  148. I am trying to download the CheckDate Form for your link but when i try to edit it it gives me an error for invalid URL.
    Any ideas?!?

  149. This is a great post! I am not a developer and it helped me with the date diff but now I need to do the same for hours diff using date&time picker and not as in the example you added where the time is in a seperate control. is there a way to do the same using date&time and the result will give me the hours diff?

  150. Your demo form works perfectly! Unfortunately, when I try to use it in mym own form I keep getting the errors below whenever I click on the StartDate field…any idea what I’m doing wrong?

    “An error occurred in the form’s rules or code. The number of rule actions or the number of calls to the OnAfterChange event for a single update in the data exceeded the maximum limit.

    The rules or code may be causing an infinite loop. To prevent this, ensure that the rule action or event handler does not update the data which causes the same rule action or event handler to execute.”

  151. you are brilliant. I have successfully implemented your formulas with great success. I am using them in a time off request form. I want to increment and decrement hours used with each request. So I added another field, and when workdays changes I multiply that value by 8, for 8 hours in a day. when workdays = 1 i get an rule error. When I change the enddate do so workdays is = to 2, it calculates perfectly (16). Changing it back to 1 I get error. I tried to just set the hours to 8 when workdays = 1, still get error. I am using 2010, but dont think that should impact it. I also changed workdays field type to number.

    Any suggestion would be greatly appreciated.

    • Upon further investigation, I can’t replicate this error on your form. I’m guessing there is something on my form causing the issue

  152. Dear Alec..am getting this error when i enter the value in the action rule
    Node-test expected.
    concat(substring-before(.,”;”)+substring-after(., “;”)*((translate(addDays(xdXDocument:GetDOM(“test1″)/somestuff/basepoint,substring-before(., “;”)), “-“, “”)2<–)
    what should i do..please tell me where am wrong..

    • Looks like you lost multiply sign in front of number 2.

      • Dear Alec,
        well..i did a demo by creating a form for calculateing workdays and it worked.now when i try to put it in my original form am getting the following eroor.please guide me on what is wrong and what i have to do.i saved the xml file under the same name and also named the fields in the same way.
        “xdXDocument:GetDOM(“daysOfTheWeek”)/somestuff/holydays/hday/@hdayn” does not point to a valid location path of a field or group.

      • The path looks correct. Try to paste the same path as calculated value in the expression box. If it does’t work something wrong with your xml file.

  153. Hello Alec,
    I am using your WorkDaysCalc form as a template to create my company’s form in InfoPath 2010. I am using it against a database form and I am getting this error when trying to do Rule 2 for BegDateWN:
    “xdXDocument:get-DOM()/my:myFields/my:BeginDate” does not point to a valid location path of a field or group.

    The folder structure is as follows:
    myFields/my:Requestor/BegDateWN

    I have already spent an entire day on this with no results. I would appreciate any light that you can shed on this issue. Thanks.

    • I’m not able to add more to the error you getting – the path to BeginDate field is incorrect.

      • Hello Alec,

        To add more to the error I mentioned earlier. I created a form using the database form from InfoPath 2010. Any folder that is created under the myFields folder is given a prefix of (my:)

        I created a folder under myFields called Requestor. The folder is now my:Requestor. I placed all of the files located in the WorkDaysCalc in that folder. So the files are as follows:

        myFields/my:Requestor/BeginDate
        myFields/my:Requestor/BegDateWN
        …..and so on.

        I have also tried putting the files directly under myFields, but I get the same error.

        I hope this sheds some more light on my problem

  154. very Nice post. I have used this same logic in my leave application form.now I have a requirement that if a person takes leave from fri to mon or anydates which consist of sat and sun in between the leave days .Eg: Begin date :20/4/2012 enddate:24/4/2012 no of workdays is:3 ,but i need to get that as 5 including sat and sun.I know we have to add +2 to the formula.but the problem is this action should run only when leave dates
    consist of sat and sun in between the leave days ,otherwise the formula which u gave should run..could you suggest me pls

  155. could you pls tell me the name of that form..so that i can download..
    Thanks

  156. Thanks for u r reply.I will use it.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 40 other followers

%d bloggers like this: