Counting Work Days

  • Thread starter Thread starter Buddy
  • Start date Start date
B

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?
 
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.
 
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?
Click to expand...

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.
 
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.
Click to expand...

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
 
Back
Top