+ Reply to Thread
Results 1 to 8 of 8

Counting holidays

  1. #1
    Forum Contributor
    Join Date
    05-16-2012
    Location
    Latina, ITALY
    MS-Off Ver
    Excel 2010--2016
    Posts
    947

    Counting holidays

    Good evening
    I would need to modify a formula I use that counts on Saturdays even on holidays.
    In the example attached to B1 I choose the month
    I should count the max between columns E and F that contain at least one number 1
    on Saturdays + holidays.
    The result with just the Saturdays in cell E33 (4) I found I need the one in F33 including
    Saturdays (4) + 2 Holidays Day 1 and Day 6 as from Holiday List in Column J.
    Thank you
    Attached Files Attached Files

  2. #2
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Counting holidays

    Hi Berna11,
    not sure if I fully understand what you need, but I think these formulas should help.

    Formula for E33 (counts the number of saturdays in the selected month)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula for F33 (counts the number of saturdays and holidays in the selected month)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Notice that the formulas are enclosed in brackets ({}). This means you need to close the formula by pressing [ctrl]+[shift]+[enter] simultaneously.
    Cheers!
    Tsjallie




    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,141

    Re: Counting holidays

    i wasn't 100% sure about your requirement. but, is this formula correct??

    =NETWORKDAYS.INTL(B1,EOMONTH(B1,0),"1111101")+COUNTIFS($J$4:$J$15,">="&$B$1,$J$4:$J$15,"<="&(EOMONTH($B$1,0)))
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  4. #4
    Forum Contributor
    Join Date
    05-16-2012
    Location
    Latina, ITALY
    MS-Off Ver
    Excel 2010--2016
    Posts
    947

    Re: Counting holidays

    thank you Tsjallie, Glenn K
    for your answer but, you both have neglected a particular one.
    I try to explain better I do not just want to count how many Saturdays and holidays
    there are in the attached sheet but, I have to count the sabbaths and holidays of the
    sheet that have in column E-F a n.1
    In the example if we remove the n.1 in E28 in F28 and then empty the result F33
    From 6 passages to 5.
    I hope it is clearer.
    Thanks again

  5. #5
    Forum Contributor
    Join Date
    05-16-2012
    Location
    Latina, ITALY
    MS-Off Ver
    Excel 2010--2016
    Posts
    947

    Re: Counting holidays

    I attach you other files to clarify better
    The account to be made is:

    E
    F
    G
    36
    E4
    1
    Holidays
    37
    E9 o F9
    1
    Holidays
    38
    E10
    1
    Saturday
    39
    F28
    1
    Saturday
    40
    4
    Total = 4


    Also my formula in E33 is wrong from 1 instead of 2 Saturday.
    Attached Files Attached Files
    Last edited by Berna11; 04-26-2017 at 02:35 AM.

  6. #6
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Counting holidays

    Hope I get it now
    Formula for cell F40:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    First part calculates the dates which have 1 in column E or F and are a holiday.
    Second part calculates the dates which have 1 in column E or F and are a saturday.
    The results of both are then added together.

    Close the formula with [ctrl]+[shift]+[enter]

  7. #7
    Forum Contributor
    Join Date
    05-16-2012
    Location
    Latina, ITALY
    MS-Off Ver
    Excel 2010--2016
    Posts
    947

    Re: Counting holidays

    Great, good,
    That's what I was looking for
    Thanks endless
    A greeting to the next

  8. #8
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Counting holidays

    You're welcome. Thx for the rep

+ 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. counting weekdays and excluding national holidays
    By campelliann in forum Excel General
    Replies: 2
    Last Post: 09-26-2015, 08:30 AM
  2. Number of Mondays (Tuesdays, etc.) in a month, not counting holidays
    By ratkins in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-24-2015, 04:12 PM
  3. Replies: 1
    Last Post: 12-12-2014, 05:43 PM
  4. counting difference in days but need to exclude holidays and weekends.
    By SnowBrian in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-21-2014, 03:26 PM
  5. Counting Holidays
    By Subhash Sarker in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-22-2013, 03:58 AM
  6. Counting Weekdays + Saturday, omit holidays
    By bryceowen in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-20-2008, 11:29 AM
  7. Counting calendar days, omitting holidays
    By Jackal in forum Excel General
    Replies: 5
    Last Post: 05-01-2008, 03:20 PM

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