@BusinessDays trouble (repost)

HCL Domino Domino Forum
1

I’m trying to calculate the days between Estimated Completion Date and Closed date. If either are blank, I want to skip processing. If they are the same dates, I want it to be 0. If we get finished AHEAD of schedule, the return of @BusinessDays is always -1. So if it is -1, I want to flop the dates and get the difference.

With thie below formula, I get 1 when both dates are empty OR if we finish early OR if we finish late! The @All seems to make a difference as I get errors if it is not there when a script runs the ComputeWithForm.

Days := @If(EstDate = “” | DateClosed = “”;“”; EstDate = DateClosed;0;

@BusinessDays(EstDate ; DateClosed;1:7;HolidayList)) ;

@If(Days = -1;@TextToNumber(“-” + @Text(@If(EstDate = “” | DateClosed = “”;“”; @BusinessDays(DateClosed;EstDate ; 1:7;HolidayList))));Days) ;@All

2

Subject: How about…

@If(EstDate = “” | DateClosed = “”;“”; EstDate = DateClosed;0;

EstDate > DateClosed;@BusinessDays(EstDate;DateClosed;1:7;HolidayList));

@BusinessDays(DateClosed;EstDate;1:7;HolidayList)))

3

Subject: Nope … Is it in the agent ?

My agent still dies with an error “Incorrect data type for operator or @Function time/date expected” on the formula that uses the @BusinessDays…am I doing something wrong in the agent that the field isn’t actually available yet? I display a messagebox and it shows that the date field I set above is available…

Sub Initialize

Dim xlFilename As String

xlFilename = "C:\Temp\ToNotes4.xls"  

Dim's and Set's here ....

On Error Goto ErrorRoutine	

Set Excel = CreateObject( "Excel.Application" )

Excel.Visible = False 	

Excel.Workbooks.Open xlFilename		

Set xlWorkbook = Excel.ActiveWorkbook

Set xlSheet = xlWorkbook.ActiveSheet	

NumberOfRows = xlSheet.UsedRange.Rows.Count		

row = 0 

written = 0



Do While written < NumberOfRows 		

	With xlSheet			

		row = row + 1

		IPARNum = .Cells( row, 1 ).Value			

		Set view = db.GetView("All Requests\By IPAR Number")

		Set doc = view.GetDocumentByKey (IPARNum, True )

		doc.Status = .Cells(row, 2 ).Value		

		If .Cells(row, 2 ).Value	 = "Closed" Then					

			Call doc.ReplaceItemValue("DateClosed",Today )												End If



		Messagebox "Before Compute:  EstCompDate = " & doc.EstCompDate(0)  ===> 02/13/2006	



		Messagebox "Before Compute:  DateClosed = " & doc.DateClosed(0) ===> 01/13/2006



		success = doc.computewithform(False,True)  ===> Dies Here							

		If success Then

			Call doc.save(True,True)

		Else

			Messagebox "Skip the Save"		

			End If

		written = written + 1

	End With

	

Loop



Goto Done

ErrorRoutine:

Messagebox "*****Error " & Err() & ": " & Error() & "*****"

If Err() = 213 Then		

	Exit Sub

Else			

	Goto Done

End If

Done:

Messagebox "In Done" 	

xlWorkbook.Close False

Excel.Quit

Set Excel = Nothing 	

'Kill "C:\Temp\ToNotes3.xls"

End Sub

4

Subject: @BusinessDays trouble - Solution

My HolidayList was a computed for display field on the form!