+ Reply to Thread
Results 1 to 6 of 6

Want to select only date of a column and if date not weekend then delete

  1. #1
    Registered User
    Join Date
    03-26-2013
    Location
    Canada
    MS-Off Ver
    ms365
    Posts
    79

    Want to select only date of a column and if date not weekend then delete

    Hello all,
    Hope all is going well. I am trying to modify an excel worksheet using VBA. In a cell I have date and time showing in one cell (ex: 04-09-2018 8:06:21am) but I only want to extract the date out of the cell.
    I would like to do this with the entire column (column Q starting Q11 and down). After the entire column is selected, I would like to only extract the date out of that column and if the date of is NOT a weekend then I would like to delete that entire row. any help would be appreciate it. Thank you
    Capture.JPG

  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Want to select only date of a column and if date not weekend then delete

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

    We do not know: where you want the date extracted to?
    if you dont need to extract the date but just want to determine if the date is a weekend
    If the date and time column is a date or text

  3. #3
    Registered User
    Join Date
    03-26-2013
    Location
    Canada
    MS-Off Ver
    ms365
    Posts
    79

    Re: Want to select only date of a column and if date not weekend then delete

    Thank you for the guidance. Attached is the sample workbook. First worksheet is how it is (before) and the second worksheet is how it should be (after). The results doesn't need to be on a new worksheet, I just made an other worksheet (after) to show how it should look after. Thank you.
    Attached Files Attached Files

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Want to select only date of a column and if date not weekend then delete

    PHP Code: 
    Option Explicit

    Sub weekends
    ()
        
    Dim i As Longlr As Long
        lr 
    Range("A" Rows.Count).End(xlUp).Row
        
    For lr To 11 Step -1
            
    If Weekday(Range("D" i)) <> And Weekday(Range("D" i)) <> 7 Then
                Range
    ("D" i).EntireRow.Delete
            End 
    If
        
    Next i
    End Sub 
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  5. #5
    Registered User
    Join Date
    03-26-2013
    Location
    Canada
    MS-Off Ver
    ms365
    Posts
    79

    Re: Want to select only date of a column and if date not weekend then delete

    That did the trick. Thank you

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Want to select only date of a column and if date not weekend then delete

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Delete entire row if date in column A is a weekend day or holiday
    By sv1996 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-12-2018, 11:04 AM
  2. NETWORKDAYS.INTL issue when start date on weekdays end date on weekend
    By Faridwahidi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-07-2017, 11:15 PM
  3. [SOLVED] Need FORMULA for Date subtract Date equal bussiness hour (don't count weekend)
    By tuongtu3 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-24-2015, 03:29 AM
  4. [SOLVED] Finding the weekend date of a particular date
    By Sandr54 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-30-2014, 04:12 AM
  5. If hire date (column D) is greater than revision date (column F) then delete row
    By bruinsrme in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-14-2013, 07:25 PM
  6. [SOLVED] Determine last date of attendance as we considered weekend, public holiday and absent date
    By ashburnadam in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2012, 12:08 AM
  7. Select a Date between Two Date Rows, then the Column State and return Table Matrix Values
    By InNeedofHelpASAP in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-12-2012, 11:03 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1