COUNTIFS date range in Date Column with blanks
Hi,
I have a date column with blanks that I'm trying to get a count of cells that contain dates by quarter (Jan-March, April-June etc.)
I tried this:
=COUNTIFS([Helper Date]:[Helper Date], AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 3, 31)))
But the result is "0"
I looked through the community and I tried playing with including a CONTAINS @ cell but couldn't figure out the syntax. Any help much appreciated!
Answers
-
Hi @John Stanik ,
At first glance, it looks like your Helper Date column may not be a date column. Will you check in the column properties for that column and see if the column type is set to Date?
-
-
@John Stanik Oh! It didn't look like it - only because the 12/1/21 date would show up as 12/01/21 if it's formatted as a date. (You may want to check that cell.)
Try this:
=COUNTIFS([Helper Date]:[Helper Date], >=DATE(2021, 1, 1), [Start Date]:[Start Date], <=DATE(2021, 3, 31))
The AND is unnecessary because it is a COUNTIFS. I think that should do the trick. Let me know!
-
I'm glad you said that! It looks like all of them are showing up as today's date when I click on them. Not sure how that happened. I put in a =Primary@cell formula and I assumed it would convert it to a date if I had the Helper Date column set up as "date".
Anyway, I converted all of the Helper Date cells to an actual date and that worked. Thanks so much for your help.
Any suggestion on how to create a helper column that would be Date specific off of a Primary column that has date-like text (not a date column)?
-
@John Stanik, do you know why I love answering questions in the Community? Because I learn new things ALL THE TIME. So, I found the answer to your question! Try doing this:
="" + [Helper Date]@row
The "" + makes the column accept it as text.
Don't give me credit for it, though - I got the answer here: https://community.smartsheet.com/discussion/29136/excel-text-functionality-in-smartsheet
-
@Heather Duff Thank you so much for checking on that! I'm still getting the text version only...
It plugs in the correct date, but when you click on the little calendar icon, it actually shows today's date??? So weird.
-
@John Stanik My apologies, I completely misunderstood you. I was trying to go the opposite direction (turn date into text, rather than turn text into date). I don't think there's a easy way to make a sheet recognize that a text column should be read as a date.
I'm sure there's a way to create a formula - likely with added helper columns - that could convert it, but it would take some deep thought.
How is the data for the primary column input? You may want to consider having the date entered in the Helper Date column, then using the =" + [helper column]@row formula in the Primary column to convert it to text.
-
@Heather Duff "How is the data for the primary column input?" --> It's just a copy/paste from an excel sheet. I don't think you can "paste special" from a copy from Excel to keep the date formatting when pasting into Smartsheet, but I'll give it a go. Thanks so much for the follow-up!
Best,
John
-
@John Stanik You're welcome! Let me know how it goes. You may want to see if you can get the Excel column formatted to short date (mm/dd/yyyy). Smartsheet should recognize it as a date if you're pasting it into the Helper Date column in that format.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 495 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!