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
-
@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
-
@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
-
@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!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!