Comparing Dates

[Rocky0201]

Hi…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I am comparing two dates— the first date is the current system date and the second date is in column G starting at row 2 through row x. I need to determine if the system date is past the date in Gx or if the system date is within a “configurable” number of business days to the date in Gx. During run-time, I am receiving a Type mismatch on the Evaluate statement.
<o> </o>
The code below:
<o> </o>
RRptHD() – an array (Long) that has the column information.
RRptHDC7 – is the offset into the array that points to the column number that is stored in the array.
At debug: this value is, as expected 7 for the column number.
<o> </o>
OIRGroupCount() is an array where I build my counts for use of displaying later.
<o> </o>
RPTDueDate() an array (Long) that has the row and column information of a configurable date in the sheet “Lists”.
RptDueDateR1 and RptDueDateC1 are offsets into the array where at the offset, the row number and column number of the “configurable” number of days is store in the sheet “Lists”.
At debug: the values are, as expected RptDueDateR1 is 1 for the row number, RptDueDateC1 is 17 for the column number.
<o> </o>
If Evaluate("NETWORKDAYS(Now()," & Cells(i, RRptHD(RRptHDC7)) & ")") < 1 Then
OIRGroupCount(2) = OIRGroupCount(2) + 1
Else
If Evaluate("NETWORKDAYS(Now()," & Cells(i, RRptHD(RRptHDC7)) & ")") <= Sheets("Lists").Cells(RptDueDate(RptDueDateR1), RptDueDate(RptDueDateC1)) Then
OIRGroupCount(1) = OIRGroupCount(1) + 1
End If
End If<o></o>
<o> </o>
If there is a different way of doing a Now() and/or Networkdays, please let me know..
<o> </o>
Thanks for the help…

 

[Norie]

Are you sure the problem lies with the dates and/or the comparison?

Have you checked out your arrays/variables?

 

[Rocky0201]

I did Norie... The arrays and variables check out... If I were to remove the "If" and remove the "Then" from the Evaluate statement, i do not get any run-time errors. with this in mind, how do I test the results of the statement? I tried Varibale = Evaluate..., String = Evaluate... If Evaluate .... = True Then...

All of those cause a type mismatch.

Any help would be wonderful Norie...

 

[Norie]

I don't use either NETWORKDAYS or Evaluate much, the former neither on a worksheet or in code, so I can't do much testing.

But my next thought was does the actual formula work?

eg could it be throwing an error?

Is it even possible to use NETWORKDAYS with Evaluate? It probably is but stranger things have been know to happen.

 

[Rocky0201]

This is what I have in a cell in one of my sheets:

=IF(NETWORKDAYS(NOW(),G110)<1,1,IF(NETWORKDAYS(NOW(),G110)<=3,2,""))

This works perfectly.

I am trying to do the same thing in vb as this is the last calulaction that i need to perfrom to eliminate any formula's in my sheet and eliminate the sheet altogether. All of my other "count gathering" work is done in vb code. The other nice thing is I do not have to be concerned with having a formula in a fixed number of rows since vb allows me to "do until" the last row of the sheet.

On other thing for me to try-- It just occurred to me that the first cell I am referrencing in my cells(i, ...) is a null. I will put a value in that cell to see if it makes the run-time work...

 

[Rocky0201]

That was the problem Norie... the test did not like a null in the cell that I am testing. No more run-time error. Now I'll work on my logic...

Thanks so much Norie...

 

[Norie]

Well I'm afraid I can't help you much further - I rarely if ever use NETWORKDAYS and I'm not about to install the Analysis Toolpak Add-In.

I do know however that you can use the functions from the add-in via VBA just like you can use other functions.

You just need to set a reference to the correct library which I think is called something like atpvbaen.xla.

To access that you'll need to have the Analysis... VBA Add-In installed.

I've no idea if that's going to help but it might be worth a punt.

 

[texasalynn]

you asked about now() and something different in VB. Do you need the date to stay static or change each time the sheet is opened?

 

[Rocky0201]

Thanks texasalynn...

now() needs to represent the current system date everytime the code is executed. That would be at sheet opened and whenever a specific cell is doubleclicked.

I have gotten this far:

If Cells(i, RRptHD(RRptHDC7)) <> "" Then
If Evaluate("NetworkDays(Now(), " & Cells(i, RRptHD(RRptHDC7)) & ")") < 1 Then
OIRGroupCount(2) = OIRGroupCount(2) + 1
Else
If Evaluate("NETWORKDAYS(Now()," & Cells(i, RRptHD(RRptHDC7)) & ")") <= Sheets("Lists").Cells(RptDueDate(RptDueDateR1), RptDueDate(RptDueDateC1)) Then
OIRGroupCount(1) = OIRGroupCount(1) + 1
End If
End If
End If

My problem is-- when i stop at the first if statement and then step into, the result from the Evaluate is -28633. I am not certain where this is coming from.

Any thoughts?

 

[Rocky0201]

by the way texasalynn, the value at cells(i, ...) is 10/1/2009