| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Work Days
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Counting Work Days
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 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Counting Work Days
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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Counting Work Days
On Thu, 22 Oct 2009 13:51:02 -0700, Buddy
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Counting Work Days
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting no of days of a specific range of days from a list | Excel Discussion (Misc queries) | |||
| Counting Days | Excel Worksheet Functions | |||
| counting days? | Excel Discussion (Misc queries) | |||
| Counting dates in multiple work sheets and work books | Excel Discussion (Misc queries) | |||
| Counting work days | Excel Discussion (Misc queries) | |||
Linear Mode
