Formula help - How to return which Financial Quarter a project Start Date falls into

Hi,

I'm not sure if I should be using an INDEX or IF(AND) formula or some combination of the two.

I have a sheet that is a Project Tracker with 1 row per project. It contains a [Start Date] column which we've filled out with the date we intend to kick each project off.

It also conaints a [Kick-Off Quarter] column in which I would like to write a formula that references another sheet and returns which Financial Quarter the Start Date for each project falls into.

Where do I start?

Best Answer

  • Brian Wilson DC
    Brian Wilson DC ✭✭✭✭
    Answer ✓

    @Tara Factor I also thought of a more elegant solution that will allow you to use your second sheet and keep adding new quarters as you identify them that way you don't have to keep modifying the formula on either sheet. For this you will want to keep your setup exactly as you have it. One sheet with your Quarter Formulas ("Start Date of Quarter", "Finish Date of Quarter" and "Quarter Name") and the second sheet you want to reference that information to depending on what day the project falls in. Again, please make sure you adjust your Q1 Date range so it ends in 2025.

    Sheet 1 - Quarter Sheet Information (add new quarters to this as you identify them)

    Sheet 2 - Your Project List Sheet
    By using the formula shown below you won't need to ever change it. It will always work with your "quarter sheet" as long as you input correct date ranges.

    I hope this helps!

Answers

  • Brian Wilson DC
    Brian Wilson DC ✭✭✭✭

    @Tara Factor Hey, Tara! You can actually achieve this without having to reference a second sheet where you've identified quarter. You can do this with one equation on your main sheet and label that the "Kick Off Quarter" column. I've provided my sheet example below and then I've shown the formula I'm using to drive that response. Please let me know if you have any questions! Also, in your table above the ending "year" for Quarter 1 should by 2025 not 2024.


    Regards,

    Brian

  • Brian Wilson DC
    Brian Wilson DC ✭✭✭✭
    Answer ✓

    @Tara Factor I also thought of a more elegant solution that will allow you to use your second sheet and keep adding new quarters as you identify them that way you don't have to keep modifying the formula on either sheet. For this you will want to keep your setup exactly as you have it. One sheet with your Quarter Formulas ("Start Date of Quarter", "Finish Date of Quarter" and "Quarter Name") and the second sheet you want to reference that information to depending on what day the project falls in. Again, please make sure you adjust your Q1 Date range so it ends in 2025.

    Sheet 1 - Quarter Sheet Information (add new quarters to this as you identify them)

    Sheet 2 - Your Project List Sheet
    By using the formula shown below you won't need to ever change it. It will always work with your "quarter sheet" as long as you input correct date ranges.

    I hope this helps!

  • Tara Factor
    Tara Factor ✭✭✭

    Thank you Brian! The second solution was exactly what I was looking for and worked perfectly. While I completely follow the logic on the first optoin, it returned '#INVALID DATA' for some reason. Anyways, thanks for your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!