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
-
Paul Newcome ✭✭✭✭✭✭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.
-
JEarley02 ✭✭✭
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:
马=条件统计({实验室工作,主要领导标准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.
-
Paul Newcome ✭✭✭✭✭✭
Try removing the comma between TODAY(-1) and INDEX so it reads as
TODAY(-1) + INDEX
instead of
TODAY(-1), + INDEX
-
JEarley02 ✭✭✭
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?
-
Paul Newcome ✭✭✭✭✭✭
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.
-
JEarley02 ✭✭✭
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:
马=条件统计({实验室工作,主要领导标准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"))
-
Paul Newcome ✭✭✭✭✭✭
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...
马=条件统计({实验室工作,主要领导标准Date}, =TODAY(-1), + INDEX(Adjustment1:Adjustment7, MATCH(WEEKDAY(TODAY(-1)), Weekday1:Weekday7, 0)), {Lab MA LeadershipStandard Work - MAIN Clinic Name}, "ALT", {Lab MA Leadership Standard Work - MAIN PT ID}, OR(@cell= "YES",@cell= "NO"))
-
JEarley02 ✭✭✭
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. -
Paul Newcome ✭✭✭✭✭✭
-
badman242 ✭✭✭
#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"
-
Andrée Starå ✭✭✭✭✭✭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:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
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.
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
