Fiscal Year Calculation
Hello,
I am trying to have a column that calculates a fiscal year automatically based on whether the parent Completed Date falls before or after July 1.
Something that follows this logic: If the parent "Completed Date" month is less than 7 (July) display "FY 'Parent Year'", otherwise display "FY 'Parent Year + 1'", if there is no parent "Completed Date" show "No Completion Date"
I have the equation below working, but I am not sure where to add ANCESTORS to each argument. Whenever I have added it I either receive an error that the syntax isn't quite right or that there is a "No Completion Date" in every row.
=IFERROR(IF(MONTH([Completed Date]@row) < 7, "FY " + (YEAR([Completed Date]@row)), "FY " + (YEAR([Completed Date]@row) + 1)), "No Completion Date")
Would love to hear any ideas that you all might have for this.
Thank you!
Best Answer
-
I see where the issue is. I was counting the number of children and assuming that if the count was zero then it was a child row that had a parent row. This isn't always true. Lets try this instead. If it is on the first level, pull the completed date, otherwise pull the parent.
=IFERROR("FY" + (YEAR(IF(COUNT(ANCESTORS([Completed Date]@row)) = 0, [Completed Date]@row, PARENT([Completed Date]@row))) + IF(MONTH(IF(COUNT(ANCESTORS([Completed Date]@row)) = 0, [Completed Date]@row, PARENT([Completed Date]@row)))>= 7, 1)), "No Completion Date")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Try something like this...
=IFERROR("FY" + (YEAR(PARENT([Completed Date]@row)) + IF(MONTH(PARENT([Completed Date]@row))>= 7, 1)), "No Completion Date")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks so much for taking a look at this and working towards the solution!
This is working except the parent task is inaccurately showing "No Completion Date", sometimes there are no sub-tasks so we would want the date to still show up if possible. The "No Completion Date" helps us when there is old data where a completion date was not added.
-
Are you able to provide a screenshot for reference?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Let me know if this doesn't make sense!
-
Try this:
=IFERROR("FY" + (YEAR(IF(COUNT(CHILDREN([Completed Date]@row)) = 0, [Completed Date]@row, PARENT([Completed Date]@row))) + IF(MONTH(IF(COUNT(CHILDREN([Completed Date]@row)) = 0, [Completed Date]@row, PARENT([Completed Date]@row)))>= 7, 1)), "No Completion Date")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi @tcanning
I see you marked Paul's answer as not resolving your question. Are you getting an error message or an incorrect result?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
My apologies for the delay I am trying to figure out the best way to word what still isn't working.
I am hoping to have each task read the date of the parent task and base the calculation on that.
I have put in the formula suggested and there are a few instances where it is still not calculating correctly.
- This is the parent task and it should have the completed on 2/14/2020 and the date calculated as FY 2020
- The parent task for this has been completed 2/14/2020 and should be calculated as FY 2020
- This is the parent task and it should have the completed on 2/20/2020 and the date calculated as FY 2020
- This is the parent task and it should have the completed on 4/02/2020 and the date calculated as FY 2020
I think we are almost there. I truly appreciate all the time and effort you are putting in to help solve this!
-
I see where the issue is. I was counting the number of children and assuming that if the count was zero then it was a child row that had a parent row. This isn't always true. Lets try this instead. If it is on the first level, pull the completed date, otherwise pull the parent.
=IFERROR("FY" + (YEAR(IF(COUNT(ANCESTORS([Completed Date]@row)) = 0, [Completed Date]@row, PARENT([Completed Date]@row))) + IF(MONTH(IF(COUNT(ANCESTORS([Completed Date]@row)) = 0, [Completed Date]@row, PARENT([Completed Date]@row)))>= 7, 1)), "No Completion Date")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Yes that did the trick, thank you so much for your time with this!!!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!