Counting Work Days

[Buddy]

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?

 

[Buddy]

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.

 

[Ron Rosenfeld]

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

 

[Buddy]

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]

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

 

[Bernd P]

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