Counting dates not working
I have 2 sheets. A Master data sheet (sheet 1) and a 2023 blackout calendar(sheet 2).
What I am trying to achieve is a formula that counts how many times a date has been scheduled in my master data sheet 1 [Installation Date], which matches/references the 2023 calendar in sheet 2 [Conversion Date], and bring the count into [Slots Used] in sheet 2.
I have tried countless formulas (countifs, index/match, count/join/collect, distinct) and many of them only populate a 1 or a 0 - which is not correct. Columns are date columns and not referencing text fields - [Lookup] satisfies this.
The extra columns [Lookup Date] & [Helper] were created to try other methods. Previously I've come across issues with referencing columns with previous formulas or a date column has through the formulas off - hints the [Lookup Date]. I really need some assistance if anybody can help figure this out.
I was able to total the [Helper] column in sheet 1, but was not able to add up the count in the other sheet by referencing. =COUNT(INDEX({2023 Blackout Calendar Lookup}, MATCH([Installation Date]@row, {2023 Blackout Conversion Date})))
Best Answer
-
Samelizsmi ✭✭
Thank you Ryan for trying. This was the first formula I tried because that's how it usually is laid out. After 2 full days and about 50 formulas, I found one! And it does not just return 1 or 0. It looks like if you are referencing from the 2nd sheet, and not the master, and both columns have dates, then this formula needed to include HAS.
Here is the formula that works for anybody that comes across this problem.
=COUNTIF({Lookup Date}, HAS(@cell, [Lookup Date]@row))
Answers
-
Ryan Sides ✭✭✭✭✭✭
Hey! Are you looking to count the Dates in the Installation Column for each Conversion Date?
If so, here's the formula for the Slots Used column ...
=COUNTIFS({2023 Blackout Calendar Lookup}, [Conversion Date]@row)
-
Samelizsmi ✭✭
Thank you Ryan for trying. This was the first formula I tried because that's how it usually is laid out. After 2 full days and about 50 formulas, I found one! And it does not just return 1 or 0. It looks like if you are referencing from the 2nd sheet, and not the master, and both columns have dates, then this formula needed to include HAS.
Here is the formula that works for anybody that comes across this problem.
=COUNTIF({Lookup Date}, HAS(@cell, [Lookup Date]@row))
-
Ryan Sides ✭✭✭✭✭✭
-
Samelizsmi ✭✭
Yeah, me neither, but it was the only formula that brought everything across correctly. All columns are date-type columns. They are also columns that have formulas in them. (you'll see the fx next to the lookup columns).
Maybe because all the dates being referenced were in sheet 2 and the formula I was creating was also in the same sheet 2? Just needing to add sheet 1s dates over to sheet 2. It could just be a funky Smartsheet nuance. They have a few I've come across before that just makes formulas not act right.
Help Article Resources
Categories
<\/p>
=COUNTIF([Tools & Resources (SE-2)]8:[Tools & Resources (SE-2)]12, \"Often\")<\/p>"},{"commentID":382723,"body":"
Thank you, as always I was trying to make it too hard!!!!<\/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":106939,"type":"question","name":"How to extract part of a cell and remove the rest","excerpt":"Hello I have a very long column with various texts in every cell containing for example 'Data change request from John Smith', every cell contains different types of requests and I want to extract to another column only the text before the word 'request '. Could you please help me how to do this? Thank you so much!","snippet":"Hello I have a very long column with various texts in every cell containing for example 'Data change request from John Smith', every cell contains different types of requests and…","categoryID":322,"dateInserted":"2023-06-27T09:11:14+00:00","dateUpdated":null,"dateLastComment":"2023-06-28T09:51:15+00:00","insertUserID":143328,"insertUser":{"userID":143328,"name":"Christiana Gkini","url":"https:\/\/community.smartsheet.com\/profile\/Christiana%20Gkini","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtf-hNm8WhOgG9NXB7Zou6oQaUyDHZZ2iwrHmrrz=s96-c","dateLastActive":"2023-06-28T10:00:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-28T09:51:21+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3375804749,"url":"https:\/\/community.smartsheet.com\/discussion\/106939\/how-to-extract-part-of-a-cell-and-remove-the-rest","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106939\/how-to-extract-part-of-a-cell-and-remove-the-rest","format":"Rich","lastPost":{"discussionID":106939,"commentID":382775,"name":"Re: How to extract part of a cell and remove the rest","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382775#Comment_382775","dateInserted":"2023-06-28T09:51:15+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-28T09:51:21+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-28T09:27:10+00:00","dateAnswered":"2023-06-27T11:27:01+00:00","acceptedAnswers":[{"commentID":382494,"body":"
Hi @Christiana Gkini<\/a>,<\/p>
If the format of the entries is consistent then you can use a formula similar to this:<\/p>
=LEFT([Column to check]@row, (FIND(\"request\", [Column to check]@row) - 2))<\/p>