# What is the best practice way to chart number of new Rows by month, rolling 6 months?

Options
✭✭✭✭✭✭
edited 09/28/21

What is the best practice way to chart number of new Rows by month (using Intake Date Creation), rolling 6 months?

Intake Date Creation format is this: "09/22/21 6:20 AM"

• ✭✭✭✭✭✭
Options

Create a Sheet to manage metrics and base the chart off of. Column formulas example below. Copy & Paste the formula for each column (i.e. Month) then increment the bold number from 30, 60, 90..etc. Basically, it calculates the date and subtracts 30, 60, 90 days etc to get to the appropriate month. Then matches the month / year from that date against the date in the search range.

Column 1 (Today)

=COUNTIFS({IT Project Intake | Created On}, IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)), {IT Project Intake | Created On}, IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)))

Column 2 (Last Month)

=COUNTIFS({IT Project Intake | Created On}, IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 30), {IT Project Intake | Created On}, IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 30))

Column 3 (Two Months Ago)

=COUNTIFS({IT Project Intake | Created On}, IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 60), {IT Project Intake | Created On}, IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 60))

• ✭✭✭✭✭✭
Options

Wow that is uber complex. The formulas worked, thank you!

However, using this solution, wouldn't the chart's X axis values (Column Names) need to be updated to the correct month name, each month? Is there a way to automation "Sept 2021", "Oct 2021", "Nov 2021", and so on, as the months roll?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!