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.

=SUMIFS(Invoices!$I$7:$I$11914,Invoices!$B$7:$B$11914,Invoices!$C34,Invoices!$K$7:$K$11914,">="&AT$32,Invoices!$K$7:$K$11914,"<="&EOMONTH(AT$32,0))

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

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭

    You will need to either use a date column or use the DATE function to allow Smartsheet to interpret the text as a date, see https://help.smartsheet.com/function/date

    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!