Hi Ron,

Thank you for your help. I have an older version of excel so I tried your
recommendation to check of atpvbaen.xls on the reference menu in VBA Editor.
Perhaps I made a mistake but I didnt seem to help. Ill keep trying thanks
for your input.


"Ron Rosenfeld" wrote:

On Tue, 20 Oct 2009 11:24:11 -0700, Buddy
wrote:

Sub DateCounting()
Dim LR As Long, NewSht As Worksheet
Dim Rng As Range, cell As Range

Sheets("Sheeet5").Activate
LR = Range("H" & Rows.Count).End(xlUp).Row
Set Rng = Range("H2:H" & LR)

If Not Evaluate("ISREF(Data!A1)") Then
Worksheets.Add(After:=Worksheets(Worksheets.Count )).Name = "Data"
Set NewSht = Sheets("Data")

For Each cell In Rng
Select Case Date - cell
Case 1: NewSht.Range("B2") = NewSht.Range("B2") + 1
Case 2: NewSht.Range("B3") = NewSht.Range("B3") + 1
Case 3: NewSht.Range("B4") = NewSht.Range("B4") + 1
Case 4: NewSht.Range("B5") = NewSht.Range("B5") + 1
Case 5: NewSht.Range("B6") = NewSht.Range("B6") + 1
Case 6: NewSht.Range("B7") = NewSht.Range("B7") + 1
Case 7: NewSht.Range("B8") = NewSht.Range("B8") + 1
Case 8 To 100: NewSht.Range("B9") = NewSht.Range("B9") + 1
End Select
Next cell

End Sub


Right now, as is, this code
Creates a new worksheet named €œData€�
Looks into column H, Sheet5; (its contents are dates).
If the date in column H is 1 day less than todays date
put a 1 in cell B2, sheet €œData€�
If the date in column H is 2 days less than todays date
put a 1 in cell B3, sheet €œData€�
If the date in column H is 3 days less than todays date
put a 1 in cell B4, sheet €œData€�
If the date in column H is 4 days less than todays date
put a 1 in cell B5, sheet €œData€�
If the date in column H is 5 days less than todays date
put a 1 in cell B6, sheet €œData€�
If the date in column H is 6 days less than todays date
put a 1 in cell B7, sheet €œData€�
If the date in column H is 7 days less than todays date
put a 1 in cell B8, sheet €œData€�
If the date in column H is 8 or more days less than todays date
put a 1 in cell B9, sheet €œData€�

I want to include this formula =IF(COUNT(K4:L4)<2,"",NETWORKDAYS(K4,L4)-1)
in the macro so that it only counts workdays when subtracting todays date
from the date in column H. Can you help me do that?

Why not just use the built-in NETWORKDAYS function?

Depending on your version of Excel, NETWORKDAYS will be a member of either the
worksheetfunction or the Analysis ToolPak.

If your version is 2007+, you could use

Select Case WorksheetFunction.NetworkDays(c.Value, Date) - 1

(The '-1' is for compatibility with your current math. The networkdays
function includes both the starting and ending date in its count).

If you have an older version, you could set a reference to atpvbaen.xls (See
Tools/References on the main menu on top of the VBA Editor, and scroll down
until you find it).
--ron
.