How to count a Calculated Date column

jasonp

I have a sheet with a start, finish, and calculated recheck date. That recheck column is 3 years out, so that column is Finish date + 1095 to get the recheck. So I need to show what the future volume of new requests and Recheck Date by month.

In my metric sheet, I am doing a count of Recheck but is shows the same count for many date ranges, what am I missing?

Paul Newcome

What formula(s) are you using? Can you provide a screenshot of the other sheet? Possibly of some rows that are supposed to be counted but aren't?

jasonp

Where the formula used is below, so I can project when I need to check back on this row. In that view above when I am trying to count what falls between a date range, it is counting that 16 every time.

=[Close Date]@row + 1095

Here is the formula for trying to count the number of shelf life within a date range

Paul Newcome

You also mentioned you were getting incorrect counts. What formula(s) are you using to generate the counts?

jasonp

Sorry, I missed a paste -

=COUNTIFS({JP - RM Compliance Data Struc Shelf Life}, >=DATE(YEAR([P Start]@row), MONTH([P Start]@row), DAY([P Start]@row)), {JP - RM Compliance Data Struct Created}, <=DATE(YEAR([P End]@row), MONTH([P End]@row), DAY([P End]@row)))

Paul Newcome

And what about some source data that isn't getting counted?

jasonp

The starting point is the RM Compliance - where based on the date closed, that is an automation of date capture. Then Shelf Live or next touch is just Close + days (

=[Close Date]@row + 1095

Then on the metric sheet running that calclation to see if that Shelf Life date falls between the start and finish start of the Period (P)

=COUNTIFS({JP - RM Compliance Data Struc Shelf Life}, >=DATE(YEAR([P Start]@row), MONTH([P Start]@row), DAY([P Start]@row)), {JP - RM Compliance Data Struct Created}, <=DATE(YEAR([P End]@row), MONTH([P End]@row), DAY([P End]@row)))

In that first picture you will see that 16 repeats for all even though we only have testing data that would put the date as 1/30/28, so that is where I would expect to see that 16 in 2028, not for every period until then. The dates are calculated also, adding 28 days to the row above.

=[P End]3+28

jasonp

Paul Newcome

It looks like your formula is referencing two different dates though. Your formula is counting rows where the Shelf Life is grater than [P Start] and the Created is less than [P End].

jasonp

Correct. I am trying to see how many rows meet that criteria between those dates. Then I can show what is the projected volume in the future from that counted number of rows.

So if I have three rows, created this week, the Shelf Life date will be Feb 2028. So in that count I would expected to see those previous 16 in JAN 2028, then FEB would show these three new rows.