# Converting a working Excel formula to InfoPath rule • ### 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 Monday, September 1, 2014 4:10 PM typo
• Moved by Tuesday, September 2, 2014 1:59 AM
• Changed type Wednesday, September 3, 2014 5:19 AM a
Monday, September 1, 2014 4:09 PM

• 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 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 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 Wednesday, June 28, 2017 2:03 PM
Wednesday, June 28, 2017 2:02 PM