Best Way to Represent Multiple Due Reports?

michelleEYN

We are utilizing Smartsheet for Non-profit grant tracking, including for awards that we have received and have reporting requirements for.

I am wondering what people's best practices are for something that has multiple due dates and report types. Here is an example of a standard situation:

Grant A

Narrative reports are due on the 15th of each month

Financial reports are due on set quarterly dates (2/20, 5/20, 8/20, 11/20)

End of funding report is due 12/31/24.

We generally use Smartsheet in grid form, but I am looking to make this information additionally accessible in calendar view moving into the new year, so that we can have a good snapshot each month.

Right now, I just have one nightmare of a cell + comments where I put all of the information and set up manual reminders (Ie, the cell reads "Narrative: 1/15, 2/15, 3/15, etc. Financial: 2/20, 5/20, 8/20, 11/20. EOY 12/31/24" with more detailed info in row comments). Would love to know how to better structure this. I'm familiar with automations and know how I would trigger notifications once I have a better cell structure. Thank you!

Purnima Gore_PGPS

Hi @michelleEYN

Could you show us the example of what you have so far please?

it is easier to come up with a solution if we can see what you can. that way it is much easier to come up with a solution for you.

Thanks

Purnima

michelleEYN

Hi Purnima, thank you for responding and apologies for my lateness. Here is a snippet of what I am currently working with. I have most of my information in the "additional report due dates" cell and have to manually cycle out old due dates. All of this makes it pretty much unusable in the calendar view, unfortunately.

Genevieve P.

Hi @michelleEYN

With dates written as text in a text/number column, Smartsheet won't be able to read them as date values to set up automations or cycle through data (or display them in a Calendar view, etc).

One option would be to set up a Parent row with the Grant Application then Child rows beneath it with each of the dates listed down a "Report Due Date" column as separate rows. This would enable you to surface top-level information in the Parent (e.g. "Next Due Date" in the top row, with child rows stating both past and future dates).

With each row identifying a date in the Date column, you could use a Report to filter by what's coming up in the next 30 days, or you could set up automated reminders, etc.

To quickly populate child rows with the Parent row Grant Application Name (for your Calendar view) you can use a formula:

=PARENT()

Let me know if this makes sense and will work for you!

Cheers,

Genevieve