+ Reply to Thread
Results 1 to 6 of 6

Count if formula with workday?

  1. #1
    Registered User
    Join Date
    04-10-2014
    Location
    Denver, CO
    MS-Off Ver
    Excel 2010
    Posts
    4

    Question Count if formula with workday?

    My current formula is this:

    =COUNTIF($A$5:$A$336,TODAY()-1) - where column A has dates (Monday thru Saturday dates entered Monday through Friday).

    But if yesterday was a Sunday, my formula is this:

    =COUNTIF($A$5:$A$336,TODAY()-2)+COUNTIF($A$5:$A$336,TODAY()-3)

    I have to pass this spreadsheet on to someone with very little formula (or excel) knowledge. Explaining how to change the formulas every Monday and change it back on Tuesday is not an option. What is a more permanant solution?

    Thank you in advance,
    Ambrosia

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Count if formula with workday?

    I can't quite figure out what you're trying to calculate.
    However, I *think* the WORKDAYS.INTL function (available beginning with Excel 2010) will do what you want.
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,683

    Re: Count if formula with workday?

    do an IF statement to test what day of the week you are in

    =IF(WEEKDAY(TODAY(),2)=1, COUNTIF($A$5:$A$336,TODAY()-2)+COUNTIF($A$5:$A$336,TODAY()-3), COUNTIF($A$5:$A$336,TODAY()-1) )

    tests to see if today is a monday and if it is does your monday formula - if not does the normal formula
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Registered User
    Join Date
    04-10-2014
    Location
    Denver, CO
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Count if formula with workday?

    I'm not familiar with it, but I will research and learn about it. Thank you for your suggestion!

  5. #5
    Registered User
    Join Date
    04-10-2014
    Location
    Denver, CO
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Count if formula with workday?

    I don't have any way to test the other half of it until Monday, but it returned the same results for today. Thank you for the suggestion!

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,683

    Re: Count if formula with workday?

    to test , you could tel the formula to change on a Thursday

    so change the
    WEEKDAY(TODAY(),2)=1

    so instead of looking for 1 as a Monday , change to
    WEEKDAY(TODAY(),2)=4
    4 Which is a Thursday - so now it will skip a day

    so a bit of a test

+ 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. Replies: 4
    Last Post: 03-16-2012, 06:55 AM
  2. Make Workday count 6 days in a week
    By BlastRanger in forum Excel General
    Replies: 6
    Last Post: 09-22-2010, 01:18 AM
  3. WorkDay Count Formula
    By Ziggy M in forum Excel General
    Replies: 12
    Last Post: 11-07-2006, 02:07 PM
  4. Count Workday include Saturday
    By TCLehmann in forum Excel General
    Replies: 1
    Last Post: 11-23-2005, 05:30 PM
  5. [SOLVED] Is there a WorkDay() type function that count all days except tho.
    By Dark Skunk in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-15-2005, 06:45 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