+ Reply to Thread
Results 1 to 2 of 2

Need to exclude weekends from total COUNTIF FORMULA

  1. #1
    Registered User
    Join Date
    12-08-2022
    Location
    Canada
    MS-Off Ver
    Microsoft 365
    Posts
    1

    Need to exclude weekends from total COUNTIF FORMULA

    I need help with two things on an attendance tracker spreadsheet I'm working on.
    1. I need to include holidays
    2. I need the totals to exclude weekends and holidays.

    2022 Annual-Leave-Tracker SAMPLE.xlsx

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Need to exclude weekends from total COUNTIF FORMULA

    At first I was going to say, "Oh no, not another HR spreadsheet." Then I saw that you had your data normalized in a table on the Mark Leave spreadsheet. Good Job! That makes life a lot easier.

    I didn't have to do much because your data is so well organized.

    Since you have the Mark Leave Data in a table, you don't have to add space holder blank rows after the data. When you enter an employee ID on the line immediately below the table, it becomes part of the table and it copies down the formulas, formats and validations automatically. I added several extra columns: Weekend, Holiday and Use.

    Weekend has the formula: =WEEKDAY([@Date],2)>5 this works out to be True if the date is a Saturday or Sunday.

    Holiday has the formula =ISNUMBER(MATCH([@Date],Holidays,0)) this works out if the date is in the named range Holidays. This range "points to" a "perpetual holiday calendar" on the holidays tab. Unfortunately, these are U.S. Holidays. So you can look at some of the logic and rewrite it as a Canadian Holiday calendar and if you do, I'd like to see it. Or you can make a list of "hard dates" and repoint the name to the new range.

    Finally, there is the Use column =NOT(OR(Table_Leave[@[Weekend]:[Holiday]])) this works out to be true if the date is NOT a weekend or holiday.

    You had the right formulas on the summary sheet (I made this a table too - it makes the formulas easier to read). All you needed to add was the USE criteria.

    There may be a way to use XLOOKUP on the Monthly Summary page, but I am not going to touch it since what you have apparently works just fine.

    Once again, congratulations for using normalized data!
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ 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. Seeking Formula to exclude Weekends
    By marmar89 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-06-2020, 03:37 AM
  2. [SOLVED] Excel Date formula exclude weekends
    By mkostin48 in forum Excel General
    Replies: 4
    Last Post: 03-22-2017, 12:55 PM
  3. Exclude Weekends in formula
    By linganit in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-12-2016, 01:06 PM
  4. [SOLVED] Formula to Exclude Holiday but not weekends
    By [email protected] in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-03-2015, 12:35 PM
  5. Formula to exclude weekends-that will calculate the due date
    By ieatbred in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-16-2011, 06:46 PM
  6. Exclude weekends and holidays from a formula.
    By Stevey in forum Excel General
    Replies: 5
    Last Post: 08-28-2009, 06:55 PM
  7. Formula to exclude weekends
    By bton24 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-11-2006, 06:00 PM

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