COUNTIFS date range in Date Column with blanks

John Stanik

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!

Heather Duff

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

Hi @Heather Duff ,

It is a date column.

Heather Duff

@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!

John Stanik

@Heather Duff

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)?

Heather Duff

@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

John Stanik

@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.

Heather Duff

@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.

John Stanik

@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

Heather Duff

@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.