COUNTIFS formula using dates excluding weekend days
I'm trying to figure out the correct formula for counting up the # of entries for a particular location with a "YES" or "NO" data entry for the previous date, TODAY (-1), that doesn't include weekends.
For instance, if today=Monday, then the formula would need to search for the previous Friday date and count the # of entries for that location on that Friday date that have a "YES" or "NO" data entry.
I have a need to see the trend for each of the past 7 days (in separate columns) for the total # of entries for each location. I've figured out how to do this for today's data using TODAY(0) using a COUNTIFS formula (see below.) How can I add the second column for the previous day such that it will perform a similar count, but taking into account if TODAY(-1) is a Sunday then it will instead count the # of entries for the Friday date (there are no date entries in the file with a Sat or Sun date, so if I use TODAY(-1), it will calculate 0 for the previous date which is not correct given my data file). Similarly, if TODAY(-2) is a Sunday or Saturday, then it will instead count the # of entries for the Friday date, and similar calculation for the -3, -4, -5, -6.
Basically, I want to see the # of entries for each date by location in 8 columns such that the dates correspond to the following columns - Today, Yesterday, The day before Yesterday, Two days before Yesterday, Three days before yesterday, Four days before yesterday, Five days before yesterday, Six days before yesterday.
Here is my COUNTIFS formula for the Today column which works:
=COUNTIFS({reference sheet date column}, =TODAY(0), {reference sheet location column}, "location name", {reference sheet data column}, OR(@cell = "YES", @cell = "NO"))
Comments
-
Try setting up a table similar to below (column headers are in bold)...
Weekday Adjustment
1 -2
2 0
3 0
4 0
5 0
6 0
7 -1
You can then use the following formula (change column names and x-sheet references as necessary)
=TODAY() + INDEX(Adjustment1:Adjustment7, MATCH(WEEKDAY(TODAY()), Weekday1:Weekday7, 0))
What this will do is look at the weekday for today (or yesterday or the day before etc based on the number you have in the TODAY function). If that weekday is a Sunday, it will add a -2 which will put it at the Friday before. Monday through Friday will not adjust the date as they are weekdays (2 - 6 in the table), and if the weekday is a Saturday (7 in the table above) it will subtract 1 to put it at the Friday before.
This will give you the date for TODAY(), TODAY(-1), etc. to be used in your COUNTIFS formula. From there you can either have this formula as a standalone cell and reference the cell in your formula, or you can replace the =TODAY(#) section of your formula with the formula above.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you. I'm trying the suggestion and getting "unparseable" as the response and not able to figure out what may be wrong. Here is my formula in the destination sheet including the suggestion using -1 for the previous day:
=COUNTIFS({Lab MA Leadership Standard Work - MAIN Date}, =TODAY(-1), + INDEX(Adjustment1:Adjustment7, MATCH(WEEKDAY(TODAY(-1)), Weekday1:Weekday7, 0))), {Lab MA Leadership Standard Work - MAIN Clinic Name}, "ALT", {Lab MA Leadership Standard Work - MAIN PT ID}, OR(@cell = "YES", @cell = "NO"))
Can you spot what I may be doing incorrectly in the formula? I am attempting to count the # of entries with a yes or no for location "ALT" on the previous day in the reference sheet, and taking into account if it's a Saturday or Sunday to use the Friday date to perform the count. I set up the 2 columns (Adjustment and Weekday) as suggested in the destination sheet where the count value's from the reference sheet will be saved.
-
Try removing the comma between TODAY(-1) and INDEX so it reads as
TODAY(-1) + INDEX
instead of
TODAY(-1), + INDEX
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you, again! I sincerely appreciate your help. I've tried removing the comma and it still remains as "unparseable". Sigh.... I feel like I'm so close to having this....do you have any other suggestions?
-
You have an extra parenthesis at the end of your INDEX/MATCH.
=COUNTIFS(................................Weekday1:Weekday7, 0)), {Lab MA Leadership....................)
You should only have two there, but you have three.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hah, I thought so too and already tried that - there are 6 left parenthesis and 6 right parenthesis. I believe I have figured out the problem, it was the "=" in front of TODAY. Here is the formula that seems to now be working:
=COUNTIFS({Lab MA Leadership Standard Work - MAIN Date}, TODAY(-1) + INDEX(Adjustment$1:Adjustment$7, MATCH(WEEKDAY(TODAY(-1)), Weekday$1:Weekday$7, 0)), {Lab MA Leadership Standard Work - MAIN Clinic Name}, "ALT", {Lab MA Leadership Standard Work - MAIN PT ID}, OR(@cell = "YES", @cell = "NO"))
-
If you count the parenthesis out, you will see that you are closing the COUNTIFS early. Removing the extra parenthesis in the middle of the formula is necessary. If that still does not work then there is an issue elsewhere.
=COUNTIFS(1 open......................, =TODAY(2 open......) 1 open, + INDEX(2 open.............., MATCH(3 open......WEEKDAY(4 open......TODAY(5 open......)4 open.........)3 open........, Weekday1:Weekday7, 0)2 open..........)1 open..........) 0 open......., {Lab MA Leadership Standard Work - MAIN Clinic Name}, "ALT", {Lab MA Leadership Standard Work - MAIN PT ID}, OR(1 open.........."YES", @cell = "NO")0 open.......) -1 open...
Below is the formula corrected...
=COUNTIFS({Lab MA Leadership Standard Work - MAIN Date}, =TODAY(-1), + INDEX(Adjustment1:Adjustment7, MATCH(WEEKDAY(TODAY(-1)), Weekday1:Weekday7, 0)), {Lab MA Leadership Standard Work - MAIN Clinic Name}, "ALT", {Lab MA Leadership Standard Work - MAIN PT ID}, OR(@cell = "YES", @cell = "NO"))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Yes, there was an extra one. Thank you. I found by removing the "=" in front of TODAY(-1), it seems to have solved the problem. I will be able to validate the total when I get to the end and do a summation of all locations.
-
Happy to help.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
#No MATCH Error
Good morning Im Getting the following error M,T,W,T work Ok but for some reason Friday it show NO MATCH
=COUNTIFS({DATE1}, TODAY(-1) + INDEX(Adjustment1:Adjustment7, MATCH(WEEKDAY(TODAY(-1)), Weekday1:Weekday7, 0)), {Type2}, "FE"
-
Hi,
Can you describe your process in more detail and maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!