Counting dates not working

Samelizsmi · 04/12/23

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

Samelizsmi · 04/13/23

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 · 04/13/23

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 · 04/13/23

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 · 04/13/23

I’m not understanding the need for the HAS. Are all of your columns of type Date?

Samelizsmi · 04/13/23

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.

Counting dates not working

Best Answer

Answers

Help Article Resources

Categories