Office - Infopath and Excel formulas

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.

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-infopath-rules-excel-services.htm
---
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
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?

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/f7d4601dfbd793bf/cd6b89a1b037be0d?hl=en&lnk=gst&q=calculated+field+sharepoint#cd6b89a1b037be0d
---
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
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

Alan

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-asked-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

Thanks.  You are a great help.  I will digest this and let you know how it
turned out.

No worries. I will monitor this thread until you solve your issue.
---
S.Y.M. Wong-A-Ton

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

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?

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

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.

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

Will do

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"

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

Can you look at my post titled Sharepoint list data source?