Count If Date is Between, Rolling Dates

jasonp

I have a process that will all automatic rolling of dates. So -2 is two months ago, -1 one, 0 current month etc. So I need a formula that I can use this automatic rolling to check projects on another sheet to count resources during that month that are used by role.

My logic:

Count if Role matches, Count if "rolling month" is between the start and finish dates.

These pictures show the sheets I am working with.

Resource Planner.jpg

Resource Metrics.jpg

Mike Wilday

This formula should do the trick for you!

=COUNTIFS(Start:Start, <=DATE(2022, 1, 31), End:End, >=DATE(2022, 1, 1))

You'll obviously have to convert that to a cross sheet formula, but that will count every start date that is before or equal to 1/31/2022, and every end date that is after or on 1/1/2022 and it will give you solid count.

I set it up in this sample sheet if you want to take a look.

https://publish.smartsheet.com/8b3c126b6838497189870f79ac35c5a6

jasonp

@Mike Wilday Yes, the trick I am trying to go after here is not hard coding that date range since it will dynamically change from month to month. Today -2 is Nov 2021, next month it will be -3 is Nov 2021. With the hard coded dates, I will have to adjust each month. How do I get that <=DATE(2022, 1, 31), End:End, >=DATE(2022, 1, 1)) so I can reference that rolling month.

-2 Months is Nov 21 for now something like <=DATE(this looks at the column heading), End:End, this looks at column heading))

-3 Monthis is Nov 21 in Feb 22 <=DATE(this looks at the column heading), End:End, this looks at column heading))

This is where I was and hit a wall. This not exactly as I adjusted the columns here for simplicity. This would be for Month -2. Then I could run this for the whole 18 month time frame once, and not have to update monthly.

=COUNTIFS({othersheetRole}, ="PM", {Start}, >=DATE(YEAR(Month -2), MONTH([Month -2]@row), {Finish}, <=DATE(YEAR([Month -2), MONTH([Month -2))))

Mike Wilday

Hmmm. Interesting. I've seen a few people help with this kind of issue. @Paul Newcome do you have an instinctive way to count for the rolling date when it expands years without using days?

You may want to check out this thread which is doing something similar.

https://community.smartsheet.com/discussion/comment/316083#Comment_316083

Paul Newcome

I do, but I will need to dig through some notes. I'll get back to you.

Paul Newcome

You will need a column called "Number" where you manually enter +/- how many months you want to go forward/back on each row. For example, if I wanted to go back 6 months and forward 11, My "Number" column would look like this (current month = 0):

-6

-5

-4

-3

-2

-1

0

1

2

3

4

5

6

7

8

9

10

11

Then the column formula for the first of the month on each row is:

=IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + Number@row, 1), IF(Number@row <= 0, DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) - Number@row, 1), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - (12 - Number@row), 1)))

So the zero row would be showing Jan 1, 2022, -1 is Dec 1, 2021, and 1 is showing Feb 1, 2022. Next month those dates will automatically shift so that Feb 1, 2022 is on the zero row.