locked
Converting a working Excel formula to InfoPath rule RRS feed

  • Question

  • Hi,

    I've used this formula as the default value in a SharePoint list column to give the date of the current week's Saturday.

    =Today+(7-MOD(WEEKDAY(Today),7))

    But, I've now had to customize the form in InfoPath and, of course, the formula no longer works.

    How would this be handled in InfoPath 2010?

    Any help appreciated

    Thanks,


    • Edited by ScarePoint Monday, September 1, 2014 4:10 PM typo
    • Moved by star.wars Tuesday, September 2, 2014 1:59 AM
    • Changed type Hemendra Agrawal Wednesday, September 3, 2014 5:19 AM a
    Monday, September 1, 2014 4:09 PM

Answers

  • Hi ,

    I understand that you use the above calculate formula to set a column default value as current week's Saturday date when the list item is created.

    You can add the following function formula in the default value for that column in InfoPath form template, it will work.

    http://www.makdeniz.com/find-the-day-of-week-by-infopath-rules-functions/

    addDays(today(), 6-((number(substring(today(), 9, 2)) + number(number(substring(today(), 1, 4))-(floor((14-number(substring(today(), 6, 2))) / 12))) + floor(number(number(substring(today(), 1, 4))-(floor((14-number(substring(today(), 6, 2))) / 12))) / 4)-floor(number(number(substring(today(), 1, 4))-(floor((14-number(substring(today(), 6, 2))) / 12))) / 100) + floor(number(number(substring(today(), 1, 4))-(floor((14-number(substring(today(), 6, 2))) / 12))) / 400) + floor((31 * number(number(substring(today(), 6, 2)) + 12 * (floor((14-number(substring(today(), 6, 2))) / 12))-2)) / 12)) mod 7))

    Thanks,
    Daniel Yang
    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Daniel Yang
    TechNet Community Support

    • Marked as answer by ScarePoint Wednesday, September 3, 2014 3:19 PM
    Wednesday, September 3, 2014 1:32 PM

All replies

  • Hi ,

    I understand that you use the above calculate formula to set a column default value as current week's Saturday date when the list item is created.

    You can add the following function formula in the default value for that column in InfoPath form template, it will work.

    http://www.makdeniz.com/find-the-day-of-week-by-infopath-rules-functions/

    addDays(today(), 6-((number(substring(today(), 9, 2)) + number(number(substring(today(), 1, 4))-(floor((14-number(substring(today(), 6, 2))) / 12))) + floor(number(number(substring(today(), 1, 4))-(floor((14-number(substring(today(), 6, 2))) / 12))) / 4)-floor(number(number(substring(today(), 1, 4))-(floor((14-number(substring(today(), 6, 2))) / 12))) / 100) + floor(number(number(substring(today(), 1, 4))-(floor((14-number(substring(today(), 6, 2))) / 12))) / 400) + floor((31 * number(number(substring(today(), 6, 2)) + 12 * (floor((14-number(substring(today(), 6, 2))) / 12))-2)) / 12)) mod 7))

    Thanks,
    Daniel Yang
    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Daniel Yang
    TechNet Community Support

    • Marked as answer by ScarePoint Wednesday, September 3, 2014 3:19 PM
    Wednesday, September 3, 2014 1:32 PM
  • No surprise I couldn't work this out myself :)

    Works exactly as the excel original

    Many thanks

    Wednesday, September 3, 2014 3:19 PM
  • Hi Daneil,

    I am using this formula in Excel, can you convert this to infopath formula for me please?

    IF(E16>200,"Critical",IF(OR(AND(E1=30,E3=30,E4=30,E5=30),(AND(E1=30,E3=30,E4=30,E7=30)),(AND(E1=30,E3=30,E5=30,E7=30)),(AND(E1=30,E4=30,E5=30,E7=30)),(AND(E3=30,E4=30,E5=30,E7=30))),"Critical",IF(OR(E16>=170, E5=30, E6= 10 ), "High",IF(E16>=100,"Medium","Low"))))

    Thanks,

    Vishal


    • Edited by Vishalgdk Wednesday, June 28, 2017 2:03 PM
    Wednesday, June 28, 2017 2:02 PM