Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.
Sign in to join the conversation:
Hi,
I have a feeling that I should be using "@cell" or "@row" in a formula to make less calculations. Can anyone shed light on a better formula than the one I have started? Basically trying to determine if a date falls between two other dates through a range (i.e. find out which year/quarter that a specific dates falls within). Screenshot:
Hi Derek,
Can you maybe share the formula/sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
Hope that helps!
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
Does this screenshot help which shows the column headings?
I figured out a work around that made it a bit easier for determining the year & month and for which quarter it falls into; and then added a "Q" to make the cell read the year and quarter (like 2016 Q2).
=YEAR([Lease Date]24) + " " + "Q" + (IF(AND(MONTH([Lease Date]24) > 0, MONTH([Lease Date]24) < 4), 1, IF(AND(MONTH([Lease Date]24) > 3, MONTH([Lease Date]24) < 7), 2, IF(AND(MONTH([Lease Date]24) > 6, MONTH([Lease Date]24) < 10), 3, IF(AND(MONTH([Lease Date]24) > 9, MONTH([Lease Date]24) < 13), 4)))))
Nice work!
If the row with the formula would be on the same row as most cells that you reference, I'd also recommend using the @row function because it makes it easier to work with and takes fewer resources.
Something like this:
=YEAR([Lease Date]@row) + " " + "Q" + (IF(AND(MONTH([Lease Date]@row) > 0, MONTH([Lease Date]@row) < 4), 1, IF(AND(MONTH([Lease Date]@row) > 3, MONTH([Lease Date]@row) < 7), 2, IF(AND(MONTH([Lease Date]@row) > 6, MONTH([Lease Date]@row) < 10), 3, IF(AND(MONTH([Lease Date]@row) > 9, MONTH([Lease Date]@row) < 13), 4)))=YEAR([Lease Date]@row) + " " + "Q" + (IF(AND(MONTH([Lease Date]@row) > 0, MONTH([Lease Date]@row) < 4), 1, IF(AND(MONTH([Lease Date]@row) > 3, MONTH([Lease Date]@row) < 7), 2, IF(AND(MONTH([Lease Date]@row) > 6, MONTH([Lease Date]@row) < 10), 3, IF(AND(MONTH([Lease Date]@row) > 9, MONTH([Lease Date]@row) < 13), 4)))
You can also increase efficiency by leveraging the fact that a nested IF will stop at it's first true value. If it makes it further into the formula, then all previous statements must be false. You can also combine the space and the Q.
So the first IF would be less than or equal to 3. The second IF could just be less than or equal to 6 because by default it must be greater than 3 to make it this far. So on and so forth.
.
=YEAR([Lease Date]@row) + " Q" + IF(MONTH([Lease Date]@row) <= 3, 1, IF(MONTH([Lease Date]@row) <= 6, 2, IF(MONTH([Lease Date]@row) <= 9, 3, 4)))
Hi all, reaching out to see if of you have ideas; I'm hoping this makes sense: I'm having some trouble getting my formulas to pull data in correctly. I currently have a sheet (primary data) where I have data in rows where each row is a single project, with columns identifying persons, $ values, unique project identifier,…
Hello, I have a workflow where, when a team member checks a checkbox, Smartsheet automatically sends an email to themselves containing merge fields that are used as a customer email draft. The challenge is that this activity typically happens on a deadline day, and a team member may need to generate 30 or more of these…
I have a sheet with 5000 rows and 30 columns. with 4 columns of them are formular columns. How can i reach this limit for 25000000 formular cells reference? How can i enlarge this limit? I need those formular to link some data from another sheet as there is also Max cells limit for one sheet. How can I join those data…
