Comparing Dates

Rocky0201 · Sep 30, 2009

Hi…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><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…

texasalynn · Sep 30, 2009

could you show what the formula would look like in Excel (not vba)

Rocky0201 · Sep 30, 2009

The formula is:

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

Epected results from the formula:

if G110 = 9/29/2009 the formula returns a -2 my cell where the formula is shows 1
if G110 = 10/1/2009 the formula returns a 2 my cell where the formula is shows 2

This is the same return values that i would expect from the vb code.

Is there any functions in vb that would replace the now() and Networkdays?

Thanks...

texasalynn · Sep 30, 2009

Date will replace now()
I don't know about networkdays - have you done a search. I think I've seen that before. Barry H always has some great posts - so maybe search for networkdays and user barry houdini

Norie · Sep 30, 2009

Eh, replace Now? That is a VBA function as well as a worksheet function.

Date(), as texasalynn suggested, will return just the date ie no time - is that what you want/need.

As for NETWORKDAYS I don't know of any direct replacement, other than what I suggested above about creating a reference to atpvbaen.xla.

If you do that then you'll be able to access all the functions from the Analysis ToolPak just as you would other VBA functions.

Don't know how they would behave in VBA compared to on the worksheet, even some 'standard' functions like Mod can behave differently.

Rocky0201 · Sep 30, 2009

Here's what I found searching for a solution... Works exactly as I had hoped...

In my Sub --

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CUsers%5CBrenden%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CUsers%5CBrenden%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CUsers%5CBrenden%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoPapDefault {mso-style-type:export-only; margin-bottom:10.0pt; line-height:115%;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> EndDate = Cells(i, RRptHD(RRptHDC7))
Call Get_Net_Working_Days(EndDate, WrkDays)

If WrkDays < 1 Then
OIRGroupCount(2) = OIRGroupCount(2) + 1
Else
If WrkDays <= Sheets("Lists").Cells(RptDueDate(RptDueDateR1), RptDueDate(RptDueDateC1)) Then
OIRGroupCount(1) = OIRGroupCount(1) + 1
End If
End If
Here's the Function --

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CUsers%5CBrenden%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CUsers%5CBrenden%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CUsers%5CBrenden%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoPapDefault {mso-style-type:export-only; margin-bottom:10.0pt; line-height:115%;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> Function Get_Net_Working_Days(EndDate As Date, WrkDays As Integer)
<o

> </o

>
Dim StartDate As Date
<o

> </o

>
StartDate = Now
<o

> </o

>
WrkDays = WorksheetFunction.NetworkDays(StartDate, EndDate)
<o

> </o

>
[FONT=&quot]End Function[/FONT]

Thanks to all....

Norie · Sep 30, 2009

Which version of Excel are you using?

If it's 2007 then I think you can use that sort of syntax because the functions from the Analysis ToolPak have been integrated to that.

I'm afraid I'm not able to afford upgrading so can't test, but that's what I've heard.

Rocky0201 · Sep 30, 2009

You're correct Norie.. 2007 it is...

barry houdini · Oct 1, 2009

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

Don't know if this helps but this formula will do the same...

=LOOKUP(SUM(INT((WEEKDAY(TODAY()-{2,3,4,5,6})+G110-TODAY())/7)),{-9.9E+307,1,4;1,2,""})

Comparing Dates

Rocky0201

Active Member

texasalynn

Well-known Member

Excel Facts

Rocky0201

Active Member

texasalynn

Well-known Member

Norie

Well-known Member

Rocky0201

Active Member

Norie

Well-known Member

Rocky0201

Active Member

barry houdini

MrExcel MVP

Similar threads

Forum statistics

Share this page

Comparing Dates

Active Member

Well-known Member

Excel Facts

Active Member

Well-known Member

Well-known Member

Active Member

Well-known Member

Active Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock