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 today’s date
put a 1 in cell B2, sheet “Data�
If the date in column H is 2 days less than today’s date
put a 1 in cell B3, sheet “Data�
If the date in column H is 3 days less than today’s date
put a 1 in cell B4, sheet “Data�
If the date in column H is 4 days less than today’s date
put a 1 in cell B5, sheet “Data�
If the date in column H is 5 days less than today’s date
put a 1 in cell B6, sheet “Data�
If the date in column H is 6 days less than today’s date
put a 1 in cell B7, sheet “Data�
If the date in column H is 7 days less than today’s date
put a 1 in cell B8, sheet “Data�
If the date in column H is 8 or more days less than today’s 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 today’s date
from the date in column H. Can you help me do that?

Please ignore the cell references for this formula
=IF(COUNT(K4:L4)<>2,"",NETWORKDAYS(K4,L4)-1), I included it to give a general
idea of my goal, which is to leave out the weekends from the calculation.

"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 today’s date
> put a 1 in cell B2, sheet “Data�
> If the date in column H is 2 days less than today’s date
> put a 1 in cell B3, sheet “Data�
> If the date in column H is 3 days less than today’s date
> put a 1 in cell B4, sheet “Data�
> If the date in column H is 4 days less than today’s date
> put a 1 in cell B5, sheet “Data�
> If the date in column H is 5 days less than today’s date
> put a 1 in cell B6, sheet “Data�
> If the date in column H is 6 days less than today’s date
> put a 1 in cell B7, sheet “Data�
> If the date in column H is 7 days less than today’s date
> put a 1 in cell B8, sheet “Data�
> If the date in column H is 8 or more days less than today’s 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 today’s date
> from the date in column H. Can you help me do that?
>

On Tue, 20 Oct 2009 11:24:11 -0700, Buddy <(E-Mail Removed)>
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 today’s date
>put a 1 in cell B2, sheet “Data”
>If the date in column H is 2 days less than today’s date
>put a 1 in cell B3, sheet “Data”
>If the date in column H is 3 days less than today’s date
>put a 1 in cell B4, sheet “Data”
>If the date in column H is 4 days less than today’s date
>put a 1 in cell B5, sheet “Data”
>If the date in column H is 5 days less than today’s date
>put a 1 in cell B6, sheet “Data”
>If the date in column H is 6 days less than today’s date
>put a 1 in cell B7, sheet “Data”
>If the date in column H is 7 days less than today’s date
>put a 1 in cell B8, sheet “Data”
>If the date in column H is 8 or more days less than today’s 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 today’s 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

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 didn’t seem to help. I’ll keep trying thanks
for your input.


"Ron Rosenfeld" wrote:

> On Tue, 20 Oct 2009 11:24:11 -0700, Buddy <(E-Mail Removed)>
> 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 today’s date
> >put a 1 in cell B2, sheet “Data�
> >If the date in column H is 2 days less than today’s date
> >put a 1 in cell B3, sheet “Data�
> >If the date in column H is 3 days less than today’s date
> >put a 1 in cell B4, sheet “Data�
> >If the date in column H is 4 days less than today’s date
> >put a 1 in cell B5, sheet “Data�
> >If the date in column H is 5 days less than today’s date
> >put a 1 in cell B6, sheet “Data�
> >If the date in column H is 6 days less than today’s date
> >put a 1 in cell B7, sheet “Data�
> >If the date in column H is 7 days less than today’s date
> >put a 1 in cell B8, sheet “Data�
> >If the date in column H is 8 or more days less than today’s 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 today’s 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
> .
>

On Thu, 22 Oct 2009 13:51:02 -0700, Buddy <(E-Mail Removed)>
wrote:

>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 didn’t seem to help. I’ll keep trying thanks
>for your input.

You'll have to search for how to enable it. I thought that if you set that
reference to atpvbaen.xls, that you could just use it in your VBA routine. But
I don't have 2003 so can't double check that.

How did you try to use the function after you selected the reference?

It should be something like:

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

--ron

Hello,

If you do not need to take into account holidays you can use a direct
formula:
http://sulprobil.com/html/date_formulas.html

[I suggest to take the fourth entry: Count of working days]

Regards,
Bernd