Formula help
I need help with a formula and not sure if it is even possible. See attached screenshot
In each of the month fields on the sheet summary. For example January. If one of the parent task dates is Between the start and finish date falls in the month of January I would like it to say the phase.
Also would have to exclude the year so we dont have to redo this each year. Our projects are only 6-8 months so for the report showing jan to dec would not be an issue.
Then I would copy the same thing for feb, march, ect....
Is this possible?
What I am trying to do is show loading report for our project managers. Each job schedule is on its own sheet. This way I can see months from jan to dec what loading is for each project manager.
Here is a rough example.... Now instead of in the month column saying job 1, job 2 it would say the phase, drywall, mechanical, ect...
I think I have how I would want it to report.. Just need to have the sheet summary columns populate.
Answers
-
What if you have multiple phases on the same job within the same month (March in your first screenshot would have 3).
-
I see that. Well I wouldn't care if is showed both or the one with the latest finish date.
-
TO show all, you would use a JOIN/COLLECT like so:
=JOIN(COLLECT(phase:phase, phase:phase, @cell <> "", [FInish Date]:[Finish Date], @cell>= DATE(2023, 03, 01), [Start Date]:[Start Date], @cell< DATE(2023, 04, 01)), CHAR(10))
The above is for March of 2023.
-
What If I just wanted to do months and not years. That way I don't have to redo the formulas every year on our template. Our projects are only approx 6-9 months so the year would not really matter.
Or if there was a way to auto redo the year. Because after 2023 then the formula would not work
-
It can be done to just look at months.
=JOIN(COLLECT(phase:phase, phase:phase, @cell <> "", [FInish Date]:[Finish Date], IFERROR(MONTH(@cell), 0)>= 3, [Start Date]:[Start Date], IFERROR(MONTH(@cell), 0)<= 3, CHAR(10))
-
I must be missing something. I get #incorrect, argument set
-
Sorry about that. I messed up the parenthesis when I made the adjustments...
=JOIN(COLLECT(phase:phase, phase:phase, @cell <> "", [FInish Date]:[Finish Date], IFERROR(MONTH(@cell), 0)>= 3, [Start Date]:[Start Date], IFERROR(MONTH(@cell), 0)<= 3), CHAR(10))
-
You Da Man! Thank so much!! Worked perfectly!!! I really appreciate this!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!