Formula Help

Michaela B

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"

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!

Michaela B

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

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.