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

Adrienne Van Halem

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!

David Joyeuse

Hi @Adrienne Van Halem

I believe you typed in row 1: "Jan-20" or "Feb-20" and so on...

Those doesn't means much, so you're gonna have to use some long nested IF statements to convert this to a number so you can use that in a date. So I would suggest to replace the "Jan-20" by 01/20 which would be easier to work with.

Also, according to your excel formula, you seem to want to calculate the SUM during a specific month. Then you'd rather go with the MONTH function, instead of an EOMONTH that doesn't exists in Smartsheet.

Hope it helped!

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

David Joyeuse

Hi @Adrienne Van Halem

I believe you typed in row 1: "Jan-20" or "Feb-20" and so on...

Those doesn't means much, so you're gonna have to use some long nested IF statements to convert this to a number so you can use that in a date. So I would suggest to replace the "Jan-20" by 01/20 which would be easier to work with.

Also, according to your excel formula, you seem to want to calculate the SUM during a specific month. Then you'd rather go with the MONTH function, instead of an EOMONTH that doesn't exists in Smartsheet.

Hope it helped!