Formula Help

Hello,


I am needing help with a formula, I have a spreadsheet with "expected ship dates" that I need to create a way to group them by month so I can get a more accurate look at our monthly projected revenue. For example I have 70 line items and each line has a different "expected ship date (ex. 11/8/23, 3/6/24.....) I need a way for this data to translate on my dashboard as monthly revenue based on that "expected ship date"

Answers

  • khickman
    khickman ✭✭✭✭✭

    Hi @Michaela B

    My first thought would be to create helper column using the formula Month. Add a column for Month and the formula show in the image below (after the slash). This would give you set column to group by month. This only works if your "expected ship date" column type is set to 'Date." I hope this helps!



  • How do I add a helper column? Is this the same as adding any column to the sheet? I'm still pretty new to smartsheet so I am learning as I go.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Yes. You would just inert a new column. A "Helper Column" is one that is used to perform "back-end" type calculations. They can typically be hidden after setting them up because they don't typically house data that needs to be displayed.


    If you are looking to group them in a report and want to keep them in chronological order though, I would suggest avoiding just using the month number as the report when grouped would be sorted

    1

    10

    11

    12

    2

    3

    4

    5

    6

    7

    8

    9



    To avoid this, you would need to add (at a minimum) a leading zero to the single digit numbers so that all numbers are two digits long.

    =RIGHT("0" + MONTH([Expected Ship Date]@row), 2)


    The above will output

    01

    02

    03

    04

    05

    so on and so forth.


    There are other options with nested IF statements that can output text such as "Jan", "Feb", "Mar", etc. if needed, but you would still need to start them off with the 01, 02, 03, etc. to ensure sorting is proper in a grouped report.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!