Get Month and Year and Group/Sort

Options

I've got a sheet in which sales reps input their daily activity. This sheet has a date column that is required. I'm trying to make a dashboard to track month to month levels of activity. Perhaps I'm missing something but there doesn't seem an obvious way to do this. I've tried making a report to group by month and sorts properly but I haven't been successful. The way I've done this is make a helper column in the report of =YEAR(DATE@row) + "/" + MONTH@row however when it gets to sorting 2021/9 ends up below 2021/12, I'm assuming because its sorting them as strings rather than recognizing them as dates. Any tips would be greatly appreciated!

Answers

  • Genevieve P.
    Options

    Hi @tendres

    You can make sure it reads the numbers in the correct order by having 0 in front of the 9, so it reads 09 before 12.

    Try something like this:

    =YEAR(DATE@row) + "/" + IF(MONTH(DATE@row) < 10, "0" + MONTH(DATE@row), MONTH(DATE@row))

    This will add a 0 before any Month earlier than October.

    Let me know if this works for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now