• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula when using Advanced Filter

G

glennpc

Member
I have an Excel table that has records with a created date and time column. I want to use Advanced Filter to create a second table (and put it on another sheet)-- and this second table will only contain records from the first table that have a created date and time that is greater than 4pm the previous BUSINESS day. (So the formula has to ignore Saturdays, Sundays, and Holidays. I tried to do this but Excel does not like my formula and also I'm not sure how to get in the business days aspect-- do I somehow use the WORKDAYS function combined with the TODAY function? I figured I could use the TODAY function and subtract a day, then add 2/3 of a day-- that gets me the "last business day" at 4pm I need -- I have that in cell F2 of the attached workbook, which also has my Excel Table on Sheet1. Once I learn what the correct formula is, I think I can use the macro recorder to get all this into a VBA sub.

Can someone tell me what is wrong with my formula to use with the Advanced Filter?
 

Attachments

  • Advanced_Filter.xlsx
    12.1 KB · Views: 8
=WORKDAY(Today(),-1,MyHolidays)+16/24

Where MyHolidays is named range/list that lists holidays and/or days that you don't want counted for Workday.

FYI. Don't use formula in criteria field. Use another cell to do calculation and using VBA, concatenate with ">" and place in criteria field.
 
Hi ,

You can use formulae in the criteria cell , as in :

=">" & TEXT(WORKDAY(Today(),-1,MyHolidays)+16/24,"dd-mm-yyyy hh:mm")

Alter the format string within the TEXT function to suit.

Narayan
 
You must log in or register to reply here.
Back
Top