MS Office Forum / General MS InfoPath Questions / February 2008
Infopath and Excel formulas
|
|
Thread rating:  |
Alan - 08 Jan 2008 02:00 GMT I have an excel formula that calulates the second tuesday of the month based on the user selecting the month (numericly) and the year from a drop down list.
Use the WEEKDAY and DATE functions as shown in the following formula: =DATE(B2,A2,1+((1-(2>=WEEKDAY(DATE($B$2,A2,1),2)))*7+(2-WEEKDAY(DATE(B2,A2,1),2))))
I have tried to get the formula to work without success. Does anyone know of a way to do this.
S.Y.M. Wong-A-Ton - 08 Jan 2008 04:42 GMT Excel is "smart"; InfoPath is not that far yet. It is best to re-create this formula by writing code, because there is no weekday function in InfoPath and trying to recreate this formula with what's currently available in InfoPath would become too complex. Alternatively, if you do not want to write code, and you have MOSS with Excel Services, and want to re-use formulas in Excel workbooks, you can do something like I did in this article: http://enterprise-solutions.swits.net/infopath2007/calculate-date-difference-inf opath-rules-excel-services.htm --- S.Y.M. Wong-A-Ton
> I have an excel formula that calulates the second tuesday of the month based > on the user selecting the month (numericly) and the year from a drop down [quoted text clipped - 5 lines] > I have tried to get the formula to work without success. Does anyone know > of a way to do this. Alan - 08 Jan 2008 12:04 GMT I already have the calulation working in a SP list. The dropdown list is pulling it's values from a SP list. In the SP list there are month, year and a calulated date columns. The calulated date column contains the formula. So the SP list contains two years worth of calulated dates.
So what I need is to populate a field on the infopath form (or a column in the form library), with the calulated date, associated with the row month-year row that the user selected. Any suggestions?
> Excel is "smart"; InfoPath is not that far yet. It is best to re-create this > formula by writing code, because there is no weekday function in InfoPath and [quoted text clipped - 16 lines] > > I have tried to get the formula to work without success. Does anyone know > > of a way to do this. S.Y.M. Wong-A-Ton - 09 Jan 2008 03:21 GMT Someone in the group once proposed a trick to get calculated fields appear in InfoPath. I myself have never tried it out, but you can give it a go. Read this thread: http://groups.google.com/group/microsoft.public.infopath/browse_thread/thread/f7 d4601dfbd793bf/cd6b89a1b037be0d?hl=en&lnk=gst&q=calculated+field+sharepoint#cd6b 89a1b037be0d --- S.Y.M. Wong-A-Ton
> I already have the calulation working in a SP list. The dropdown list is > pulling it's values from a SP list. In the SP list there are month, year and [quoted text clipped - 25 lines] > > > I have tried to get the formula to work without success. Does anyone know > > > of a way to do this. Alan - 10 Jan 2008 15:21 GMT I've decided the best route is to use event handler to perform the calculation that the SP list is being used for. I have a excel formula that calculates the second tuesday of the month when the user enters the month (numeric) and the year.
=DATE(Year,Month,1+((2-(2>=WEEKDAY(DATE(Year,Month,1),2)))*7+(2-WEEKDAY(DATE(Year,Month,1),2))))
Do you know the best way to convert this formula to c# with the "Year" and "Month" being selections from a dropdown or text field?
Alan
> Someone in the group once proposed a trick to get calculated fields appear in > InfoPath. I myself have never tried it out, but you can give it a go. Read [quoted text clipped - 32 lines] > > > > I have tried to get the formula to work without success. Does anyone know > > > > of a way to do this. S.Y.M. Wong-A-Ton - 11 Jan 2008 06:49 GMT First you need to retrieve the values from the month and year fields. Check out this article http://enterprise-solutions.swits.net/infopath2007/infopath-2007-frequently-aske d-questions-faq.htm#how-to-retrieve-field-value-from-code if you need code to do it. Here field1 would be either the month or year field. You can quickly find out what the XPath expression for the month or year is by going to the "Data Source" pane, right-clicking on the month or year node, and selecting "Copy XPath".
I don't know if this is the best way, but what I would do is use the values from the month and year fields to determine how many days are within the month. You can use DateTime.DaysInMonth (see http://msdn2.microsoft.com/en-us/library/system.datetime.daysinmonth.aspx) for this. Once you know how many days are within the month, you can create a for-loop that loops over the amount of days in the month. For each day in the loop you can then construct a DateTime object and use its DayOfWeek property (see http://msdn2.microsoft.com/en-us/library/system.datetime.dayofweek(VS.80).aspx) to check whether the day is a Tuesday. If it is, you can increase a counter (declare and initialize the counter outside the loop). Once the counter has been increased twice, you must save the last date you checked, break the loop, and then you have the date for the second Tuesday of the month.
Again, there might be a better/quicker way of calculating it. It is best to post your question in one of the .NET development newsgroups if you want to know what the best way is. --- S.Y.M. Wong-A-Ton
> I've decided the best route is to use event handler to perform the > calculation that the SP list is being used for. I have a excel formula that [quoted text clipped - 44 lines] > > > > > I have tried to get the formula to work without success. Does anyone know > > > > > of a way to do this. Alan - 11 Jan 2008 12:14 GMT Thanks. You are a great help. I'll digest this and let you know how it turned out.
> First you need to retrieve the values from the month and year fields. Check > out this article [quoted text clipped - 72 lines] > > > > > > I have tried to get the formula to work without success. Does anyone know > > > > > > of a way to do this. S.Y.M. Wong-A-Ton - 12 Jan 2008 23:58 GMT No worries. I'll monitor this thread until you solve your issue. --- S.Y.M. Wong-A-Ton
> Thanks. You are a great help. I'll digest this and let you know how it > turned out. [quoted text clipped - 75 lines] > > > > > > > I have tried to get the formula to work without success. Does anyone know > > > > > > > of a way to do this. Alan - 17 Jan 2008 11:33 GMT Here is the code that i used:
EventManager.XmlEvents["/my:ExpRFR/my:Contract/my:BrdMtgYear"].Changed += new XmlChangedEventHandler(BrdMtgYear_Changed); EventManager.XmlEvents["/my:ExpRFR/my:Contract/my:BrdMtgDate"].Changed += new XmlChangedEventHandler(BrdMtgDate_Changed); }
public void BrdMtgYear_Changed(object sender, XmlEventArgs e) { //// calculate the 2nd Tuesday of the month // based on the user entering the month and year numerically //Set Variables //These variables will be input from the form XPathNavigator BrdMtgMonth = this.MainDataSource.CreateNavigator().SelectSingleNode("/my:ExpRFR/my:Contract/my:BrdMtgMonth", NamespaceManager); XPathNavigator BrdMtgYear = this.MainDataSource.CreateNavigator().SelectSingleNode("/my:ExpRFR/my:Contract/my:BrdMtgYear", NamespaceManager); XPathNavigator BrdMtgDate = this.MainDataSource.CreateNavigator().SelectSingleNode("/my:ExpRFR/my:Contract/my:BrdMtgDate",NamespaceManager); //int intMonth = 6; //int intYear = 2008; //These variables are used in the code int intDay = 1; int tuesdayCount = 0; DateTime scndTuesdayDate = DateTime.MinValue; //Loop until we have found the second Tuesday while (tuesdayCount < 2) { //Create our test date -- this starts at the beginning of the month scndTuesdayDate = Convert.ToDateTime(Convert.ToString(BrdMtgMonth) + "/" + Convert.ToString(intDay) + "/" + Convert.ToString(BrdMtgYear)); //See if our test date is a Tuesday if (scndTuesdayDate.DayOfWeek == DayOfWeek.Tuesday) { //If our test date is a Tuesday, //then up the count of the number of Tuesdays we have found tuesdayCount = tuesdayCount + 1; // and increase the date by a week to find the next Tuesday intDay = intDay + 7; } else { //If out test date is not a Tuesday, //then increase the date by a day and check it again //in order to find the first tuesday intDay = intDay + 1; } } { //scndTuesdayDate is your return value for the second tuesday of the month } BrdMtgDate.SetValue(scndTuesdayDate.ToShortDateString()); }
public void BrdMtgDate_Changed(object sender, XmlEventArgs e) { // Write your code here to change the main data source. } } }
> No worries. I'll monitor this thread until you solve your issue. > --- [quoted text clipped - 79 lines] > > > > > > > > I have tried to get the formula to work without success. Does anyone know > > > > > > > > of a way to do this. Alan - 17 Jan 2008 17:29 GMT I have a new issue. As you can see I set value of he field BrdMtgDate to the scndTuesdayDate which is a string. I want the BrdMtgDate to be formated as a date. Any suggestions?
> Here is the code that i used: > [quoted text clipped - 149 lines] > > > > > > > > > I have tried to get the formula to work without success. Does anyone know > > > > > > > > > of a way to do this. S.Y.M. Wong-A-Ton - 18 Jan 2008 02:01 GMT If the data type of BrdMtgDate is date, use BrdMtgDate.SetValue(scndTuesdayDate.ToString("yyyy-MM-dd"));
If the data type of BrdMtgDate is dateTime, use BrdMtgDate.SetValue(scndTuesdayDate.ToString("yyyy-MM-ddThh:mm:ss"));
For an explanation, see http://enterprise-solutions.swits.net/infopath2003/date-time-basics-infopath.htm --- S.Y.M. Wong-A-Ton
> I have a new issue. As you can see I set value of he field BrdMtgDate to the > scndTuesdayDate which is a string. I want the BrdMtgDate to be formated as a [quoted text clipped - 153 lines] > > > > > > > > > > I have tried to get the formula to work without success. Does anyone know > > > > > > > > > > of a way to do this. Alan - 18 Jan 2008 12:09 GMT That worked.
My next challange is to populate a new instance of the from with data from the SP list. Then allow the user to update the data and save the updates as a new record.
> If the data type of BrdMtgDate is date, use > BrdMtgDate.SetValue(scndTuesdayDate.ToString("yyyy-MM-dd")); [quoted text clipped - 164 lines] > > > > > > > > > > > I have tried to get the formula to work without success. Does anyone know > > > > > > > > > > > of a way to do this. S.Y.M. Wong-A-Ton - 19 Jan 2008 05:03 GMT Great! Glad it worked.
If you have issues with your new challenge, it's best to create a new thread, since it's unrelated to your Excel formula issue, which I assume has now been resolved. Good luck! --- S.Y.M. Wong-A-Ton
> That worked. > [quoted text clipped - 170 lines] > > > > > > > > > > > > I have tried to get the formula to work without success. Does anyone know > > > > > > > > > > > > of a way to do this. Alan - 19 Jan 2008 12:37 GMT Will do
> Great! Glad it worked. > [quoted text clipped - 178 lines] > > > > > > > > > > > > > I have tried to get the formula to work without success. Does anyone know > > > > > > > > > > > > > of a way to do this. Alan - 20 Jan 2008 13:37 GMT I thought this form was finished. However I had not tested the form in MOSS. When I did the I noticed that the event changed code does not run the same. I the browser when the user selects the year from the dropdown the event changed code does not run. But if you select the submit button a dialob box displaying the message "You have made changes to the form that have not been processed. We will process the changes and show the form again. After that you have to click the "Submit" again." The event changed then code runs.
When the "submit" button is selected again an error message displays "An error occurred while the from was being submitted"
> Will do > [quoted text clipped - 180 lines] > > > > > > > > > > > > > > I have tried to get the formula to work without success. Does anyone know > > > > > > > > > > > > > > of a way to do this. S.Y.M. Wong-A-Ton - 20 Jan 2008 22:38 GMT To make the changed event always run: On the Properties dialog box for the year dropdown, go to the Browser Forms tab and select "Always" under the postback options. To get a little bit wiser about what kind of error is taking place, go to your SharePoint server and look into the log files (under the 12 hive) for any errors that may have occurred after you tried submitting the form; the error might not be related to the date calculation, but something else. --- S.Y.M. Wong-A-Ton
> I thought this form was finished. However I had not tested the form in MOSS. > When I did the I noticed that the event changed code does not run the same. [quoted text clipped - 191 lines] > > > > > > > > > > > > > > > I have tried to get the formula to work without success. Does anyone know > > > > > > > > > > > > > > > of a way to do this. Alan - 21 Jan 2008 00:04 GMT I found the problem. The form was created by someone else and there was a rule running under the Submit button. The rule submitted to a different library.
> To make the changed event always run: On the Properties dialog box for the > year dropdown, go to the Browser Forms tab and select "Always" under the [quoted text clipped - 201 lines] > > > > > > > > > > > > > > > > I have tried to get the formula to work without success. Does anyone know > > > > > > > > > > > > > > > > of a way to do this. Alan - 05 Feb 2008 01:22 GMT Can you look at my post titled Sharepoint list data source?
> To make the changed event always run: On the Properties dialog box for the > year dropdown, go to the Browser Forms tab and select "Always" under the [quoted text clipped - 201 lines] > > > > > > > > > > > > > > > > I have tried to get the formula to work without success. Does anyone know > > > > > > > > > > > > > > > > of a way to do this.
|
|
|