How to register a cell in a Text/Number column as a date in a formula?


I have a formula from Excel that I'm trying to duplicate in SmartSheet. The formula references row1 which is a month-year (eg JAN-20). This is a duplicate for the column name so it can be interacted with in a formula.

So there are 2 elements here that I need help with.

  1. Can treat a cell from a text/number column as a Date?
  2. If so, how in Smartsheets can I treat the Month-Year as end of the month like Excel's EOMONTH function?

Here is my original formula from Excel.


Where AT32 is the Month-Year I'm trying to reference.

Here is my first attempt in SmartSheet that isn't working for many reasons.

=SUMIFS({Invoices Range 2}, {Invoices Range 1}, [Tracking ID]@row, [{Invoices Range 3},"<=&EOMONTH([ACT-1]1,0)

And a snippit of my sheet so you can see how i'm trying to make it reflect Row1.

Thank you for the help!

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!