Solved

Delete rows based on X "network" days old

Posted on 2014-11-04
8
150 Views
Last Modified: 2014-11-04
Hi,

I have a spreadsheet with a date in column G.

I want to delete every row where that date is less than 9 network days old.

In other words, what I want left are only rows where that date is more than 8 network days old.

Thanks in advance,

swjtx99
0
Comment
Question by:swjtx99
  • 4
  • 3
8 Comments
 
LVL 20

Expert Comment

by:Russ Suter
ID: 40422856
Deleting rows older than 8 calendar days is simple enough but what is a "network day"?
0
 

Author Comment

by:swjtx99
ID: 40422881
Hi Russ,

Network day is a Workday. I do not want the 8 days to include weekends.

Thanks,

swjtx99
0
 
LVL 20

Expert Comment

by:Russ Suter
ID: 40422891
Ah, gotcha.

You can use the NETWORKDAYS() Excel function for this.

=NETWORKDAYS(A1, TODAY()) -- where cell A1 contains the date you want to check against.
0
ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

 

Author Comment

by:swjtx99
ID: 40422906
Hi Russ,

Yes but I need a VBA solution as it's a step I want done in a macro.

Thanks,

swjtx99
0
 
LVL 20

Expert Comment

by:Russ Suter
ID: 40422917
Function GetWorkDays(StartDate As Long, EndDate As Long) As Long
' returns the count of days between StartDate - EndDate minus Saturdays and Sundays
Dim d As Long, dCount As Long
    For d = StartDate To EndDate
        If WeekDay(d, vbMonday) < 6 Then
            dCount = dCount + 1
        End If
    Next d
    GetWorkDays = dCount
End Function

Open in new window


The only thing is this function won't count holidays
0
 

Author Comment

by:swjtx99
ID: 40422930
Hi Russ,

Looks like it is counting days but I don't see where it's looking at Column G or deleting anything?

I found this on a different thread which looks like what I'm trying to do except it's not working and I haven't figured out why.

Dim lstRow As Long, Idx As Long
lstRow = Range("G" & Cells.Rows.Count).End(xlUp).Row
For Idx = lstRow To 1 Step -1 ' change 1 to the first row with dates
    If Cells(Idx, 1) < Application.WorksheetFunction.WorkDay(Date, -8) Then
        Cells(Idx, 1).EntireRow.Delete
    End If

Thanks,

swjtx99
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40422997
Two things:
1) The code you just posted isn't using the correct argument for today's date ("Date" instead of "Now()") in order to calculate the number of net workdays.  
2) The code is using column G to determine the number of rows, but is testing cells in column A ( Cells(Idx, 1) ).
Revise the code to this:
    Dim lstRow As Long, Idx As Long
    lstRow = Range("G" & Cells.Rows.Count).End(xlUp).Row
    For Idx = lstRow To 1 Step -1 ' change 1 to the first row with dates
        If Cells(Idx, 7) < Application.WorksheetFunction.WorkDay(Now(), -8) Then
            Cells(Idx, 7).EntireRow.Delete
        End If
    Next Idx

Open in new window


Regards,
-Glenn
0
 

Author Closing Comment

by:swjtx99
ID: 40423002
Hi Glenn,

Thanks! I was almost there realizing it was looking at Column A. Appreciate the help!

Regards,

swjtx99
0

Featured Post

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question