?
Solved

Delete rows based on X "network" days old

Posted on 2014-11-04
8
Medium Priority
?
154 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

: 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 article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

752 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