Check if project start/end dates fall within specific time frame.

Options
gunnell
gunnell ✭✭
edited 06/08/23 in Formulas and Functions

My team works on projects that typically take 6-12 months. I'd like to create a roll-up sheet that identifies which quarters in which a project was worked on. I'm creating a new row for each project and linking in the start and end dates from each of the projects' sheets. Then I've created a new column for each quarter (e.g., 2022Q1, 2022Q2, 2022Q3, 2022Q4, 2023Q1...)

Let's say "Project 1" started on Oct 4, 2022 and ended on May 15, 2023.

I'm trying to create a formula that would go in those quarter cells that asks if the dates of the quarter (e.g., 1/1/2022 through 3/31/2022) overlap at all with the dates of the project. If it does, throw a 1 in the cell for that quarter, of not, 0.

So for the Project 1 row, the following columns would have a 1: 2022Q4, 2023Q1, 2023Q2 since the project dates overlap with those quarters. The rest of the columns (for the other quarters) would all be 0.

It seems simple, but I can't seem to get the IF(AND()) formulas working.

Thanks!

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Try something like this:

    =IF(AND([End Date]@row>= DATE(quarter start date), [Start Date]@row<= DATE(quarter end date)), 1)


    Using the proper syntax to input the appropriate quarter start and end dates should get you what you are looking for.

    DATE(yyyy, mm, dd)

  • gunnell
    gunnell ✭✭
    Answer ✓
    Options

    The problem I was having with something similar to what you have is that it wasn't picking up projects that started before the quarter and ended after the quarter. So if project started in 2022Q4, continued in 2023Q1, and ended during 2023Q2, I would want a "1" for all three quarters but it was only giving me one for 2022Q4 and 2023Q2 (skipping 2023Q1).

    I actually just got this working late last night (with the help of ChatGPT). Here is what I ended up with. This one checks for 2022Q1.

    =IF(OR(AND($Start@row <= DATE(2022, 3, 31), $End@row >= DATE(2022, 1, 1)), AND(DATE(2022, 1, 1) <= $End@row, DATE(2022, 3, 31) >= $Start@row)), 1, 0)

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!