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.
- Can treat a cell from a text/number column as a Date?
- 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
-
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!
Answers
-
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!
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!