Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / General MS InfoPath Questions / February 2008

Tip: Looking for answers? Try searching our database.

Infopath and Excel formulas

Thread view: 
Enable EMail Alerts  Start New Thread
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.  
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.