Best Of

You're right, Smartsheet doesn't seem to recognize =TEXT like in Excel. A workaround would be something like this:

Change your column from [Start 1] to whatever the name of your date column is.

=IF(MONTH([Start 1]@row) = 1, "January", IF(MONTH([Start 1]@row) = 2, "February", IF(MONTH([Start 1]@row) = 3, "March", IF(MONTH([Start 1]@row) = 4, "April", IF(MONTH([Start 1]@row) = 5, "May", IF(MONTH([Start 1]@row) = 6, "June", IF(MONTH([Start 1]@row) = 7, "July", IF(MONTH([Start 1]@row) = 8, "August", IF(MONTH([Start 1]@row) = 9, "September", IF(MONTH([Start 1]@row) = 10, "October", IF(MONTH([Start 1]@row) = 11, "November", IF(MONTH([Start 1]@row) = 12, "December", ""))))))))))))

Hi @melissalk

Glad to hear that you figured it out.

I created a solution that automatically shows the task counts by department for the last 3 months, and it dynamically updates each month without any manual editing.

https://app.smartsheet.com/b/publish?EQBCT=e59c33d129d7421d99379def6ec33d74

How it works:

🔹 Summary Fields (used to define date ranges for each month):

These are created in the summary section of the sheet to act as reusable date boundaries:

• 3 Month Ago Start
  =DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1)
• 2 Month Ago Start
  =DATE(YEAR(TODAY()), MONTH(TODAY()) - 2, 1)
• 1 Month Ago Start
  =DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1)
• 3 Month Ago End
  =[2 Month Ago Start]# - 1
• 2 Month Ago End
  =[1 Month Ago Start]# - 1
• 1 Month Ago End
  =DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1

These date fields automatically calculate the first and last day of the past 3 months (accounting for different month lengths, including February).

Department-Level Formula (in the sheet)

Each department row uses a formula like this to calculate the number of tasks that overlap with the target month:

[3 Month Ago] =
IF(Department@row = "Year / Month",
YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, DAY(TODAY()))) + " / " +
INDEX({12 months of the year : Month}, MOD(MONTH(TODAY()) + 12 - 4, 12) + 1),
COUNTIFS({Task Name}, ISTEXT(@cell),
{Task Type}, "Task",
{Actual Start Date}, <=[3 Month Ago End]#,
{Actual End Date}, >=[3 Month Ago Start]#,
{Department}, Department@row)
)

• If the Department value is "Year / Month", the cell returns a label (like "2024 / December") to show at the top row for charting.
• Otherwise, it returns the count of tasks that:
  • Are text-based (i.e. actually exist)
  • Are of type "Task"
  • Overlap with the date range for that month
  • Belong to the current department

The same logic is applied to [2 Month Ago] and [1 Month Ago], using the corresponding start and end fields.

{12 months of the year : Month}

To display in a dashboard:

• The top row contains "Year / Month" in the Department column, which outputs the label for each of the 3 months (e.g., "2024 / December", "2024 / January").
• The rest of the rows are department names (e.g., "Editorial", "Design") and show task counts per month.
• This structure can be used directly in a line or column chart.

Let me know if you'd like a copy of my template — happy to share!

Hello @Diana36,

I was able to get it to be ordered based on the Impact and then date.

First for the dates, I created a new column to calculate the age.
=TODAY()-[Date Added]@row

Then I created 3 new rows to rank the Impacts based on the Age of the row. To make them go in order (High, Medium, Low), I just added them to the Max of the previous Impact Rank.
High
=IFERROR(RANKEQ(Days@row, COLLECT(Days:Days, Category:Category, "Gap Item", Impact:Impact, "High"), 0), "")

Medium
=IFERROR(MAX(High:High) + RANKEQ(Days@row, COLLECT(Days:Days, Category:Category, "Gap Item", Impact:Impact, "Medium"), 0), "")

Low
=IFERROR(MAX(Medium:Medium) + RANKEQ(Days@row, COLLECT(Days:Days, Category:Category, "Gap Item", Impact:Impact, "Low"), 0), "")

Then I just joined them all together to get the combined order numbering.
=JOIN(High@row:Low@row)

It will adjust the order if you add item later based on the Date and Impact.

Let me know if this works for you!

Hi,

I hope you're well and safe!

I missed this, but I'm glad to see that you got it working.

Be safe, and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Awesome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

@Jarret.Birdwell Great job laying all this out, but a quick question to simplify my understanding:
Are you also saying that if Item 1 has ANY late items/children, it should show late, if none are late but ANY are at risk, then the parent is as risk etc etc? So worse case to best case, and the threshold for each stage is any? IF so.. I've done this many times….

Here's what you need:
1.) Add two hidden helper columns. (I do this on EVERY sheet). One is named "A", and the other is "C". They should have these formulas: =count(ancestors()) in "A" and =count(children()) in "C". These are column formulas. (This formula doesn't use the C… I just always include it)

2.) Change your status column formula to:
=IF(A@row = 0, IF(COUNTIF(DESCENDANTS(), "Late") > 0, "Late", IF(COUNTIF(DESCENDANTS(), "At Risk") > 0, "At Risk", IF(COUNTIF(DESCENDANTS(), "In Progress") > 0, "In Progress", IF(COUNTIF(DESCENDANTS(), "Complete") > 0, "Complete")))), IF(OR(AND([% Complete]@row = 0, NETWORKDAY(TODAY(), [End Date]@row) > 0, NETWORKDAY(TODAY(), [End Date]@row) < 30), AND([% Complete]@row > 0, [% Complete]@row < 1, NETWORKDAY(TODAY(), [End Date]@row) > 0, NETWORKDAY(TODAY(), [End Date]@row) < 10)), "At Risk", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Late", IF([% Complete]@row = 1, "Complete", IF([% Complete]@row > 0, "In Progress", "Not Started")))))

This also works for multi level parent/child setups.

Also… to keep your today formula up to date:
Add a "TodayHelper" hidden helper date column. Then add an automation to record a date in that column every day at 4am or something early.

Hello!

I have a user tracking the progression of course development and is asking if there is a way to do some formatting on his sheet.

• The columns shown are set up as dropdowns (there are 8 total columns on this sheet).
  
• In the yellow row, in the cells where I've drawn a box, he is going to place due dates.
  
• Is it possible to change cell color based on the dropdown selection AND that due date?

Example: If Not Started is selected in the Initial sweep column AND it's a specific date (which will be in the yellow cell of that column), turn the Not Started cell pink.

My initial solution: Having the column properties be Date columns (instead of dropdowns) and then using conditional formatting to change the cell color based on the text entry does work, but the user entering the text would need to be consistent with their entries. I think it's a possible solution, but I feel I might be missing some other options (formulas, Helper column(s), etc.).

Am I on track or has anyone done anything similar?
Many thanks!
-Melissa