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!!!

For example:

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

Note:

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.

Any suggestion?

Status Report by Period.png

Find more posts tagged with

Templates

Sheets

Formulas

Government

Reports

Services

IT & Operations

Project Management

Comments

Sarah Thompson

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.

Mike Wilday

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.

Paul Newcome

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

Hi Paul,

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.

Paul Newcome

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…