Status Report based on certain conditions, i.e. dates, status etc
Hi Smartsheet community,
I'm trying to come up with a status report to display information on what has been done in our projects between certain dates. I would really appreciate your expertise and help!!!
Project A Status Report for the period 04/Nov/19 - 17/Nov/19
What I want to show is:
1. Completed Deliverables (Phase/Milestone/Task) in the period mentioned above
2. Incompleted Deliverables (Phase/Milestone/Task) that had its End date set for >= 04/Nov/19 and <=17/Nov/19 which did not have it's % Completed = 100%
3. Planned Deliverables for the next Status Report Period (Phase/Milestone/Task) that had its End date set for >= 18/Nov/19 and <=1/Dec/19 which currently its % Completed isn't set as 100% Completed
I have a Cell where I can put "Status Report Start Date and another End Date" also can create another 2 Cell to show Next Period, which means if I want to report for the full month or just a week I can adjust the dates accordingly.
I would just create a report based on that sheet. The "when" would be the dates in question. If you really want to look at each of your 3 items individually, you could create a report for each one and display them on a personal dashboard for your own viewing.
Alternately, you can create a filters on your source sheet and change them according to what you want to see, but I find filters to be less user-friendly than reports.
You can also use cross-sheet formulas and pull your data together in a new sheet. Or use the summary data fields to summarize your data within the sheet.
I personally would create a helper column of the text/number type and call it [Needed for Report].
In this column you could use a nested IF statement to look at your date cells and your criteria and enter either Completed, Incompleted, or Planned.
Then you can pull your report based on this column not being blank, and since it is specified what type it is, you can sort your report on that same column and have everything a little more organized.
For this example I will reference date cells as if they are in rows 1 - 4 of a column simply called "Dates".
Dates1 = Current Start Date
Dates2 = Current End Date
Dates3 = Next Period Start Date
Dates4 = Next Period End Date
The formula in the helper column would look something like this...
=IF(AND([End Date]@row >= Dates1, [End Date]@row <= Dates2), IF([% Complete]@row = 1, "Completed", "Incomplete"), IF(AND([End Date]@row >= Dates3, [End Date]@row <= Dates4, [% Complete]@row < 1), "Planned"))
Thiago Castro ✭✭✭✭
Thanks for always be there to help the community.
I'll try this and come back to you.
In the meantime, any idea how I could also bring its parents to the same report?
i.e. Phase then Milestone and the conditional @row (Completed, Incomplete or Planned)?
Thanks in advance.
Try something like this...
=IF(COUNTIFS(CHILDREN(), ISTEXT(@cell)) > 0, JOIN(COLLECT(CHILDREN(), CHILDREN(), ISTEXT(@cell)), ", "), IF(AND([End Date]@row >= Dates1, [End Date]@row <= Dates2), IF([% Complete]@row = 1, "Completed", "Incomplete"), IF(AND([End Date]@row >= Dates3, [End Date]@row <= Dates4, [% Complete]@row < 1), "Planned"))
The bold portion at the beginning checks if for any child rows. If there are child rows and any of them are text (meaning it was populated with one of the three options in the previous formula), then it will join those results together so that your parent will show the status of each of the child rows that fall under it (as a summary of sorts). The unbold portion of the formula is just the previous formula to otherwise populate the child rows.
Help Article Resources
Check out the Formula Handbook template!