How to group chart dates by month instead of individual dates

WasteKnot

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

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.

WasteKnot

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

Mike