How to group chart dates by month instead of individual dates

This is what I have started on a dashboard. The horizontal axis are individual dates for projected project completion. I want the chart to summarize by month. I've read through a bunch of posts about creating a helper column that pull the month number, but I am not following how that will translate to the chart when we are projecting into the next year. Everything I've read seems to be overly complex compared to what I am used to. I can easily add a helper column to translate each date to say the last day of that month, but again - am I missing a simple formula for that?

Thanks

Answers

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭

    @WasteKnot

    You could use your helper column with the following (replate Date@row with whatever your column name is:

    You can use =MONTH(Date@row) if you want it to return a number, OR if you want to show the month name, you can use the below formula for that.

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

    NOTE: depending where you are located globally you may need to replace the "," in the formula above with ";"

    Additionally I would suggest that you do a metric sheet that does a =SUMIF({Date Helper Column}:{Date Helper Column}, "January", {Projected Cost}:{Projected Cost}, etc on for each month, and then pull from that metric sheet for the chart. This will summarize the information in a way that will create the chart you are seeking, and will update automatically as you enter data.

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

  • Colleen - thank you for both aspects, the formula and the metric sheet.

    Mike

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!