Counting Work Days
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 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?
|