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)))
Looking to calculate the % of empty cells in a sheet for all rows that have data, with the intention of excluding any rows beyond my last row. I'd like to use this to motivate relevant stakeholders to completely populate the columns. Thank you.
So here what I'm trying, I have multiple solution and status for all. Trying to get the total net worth value of order based on solution + individual status. Let's say I want to capture the total revenue for Centralised Inventory with all different status as below: I tried with Sumifs but it's not calculating all values.…
I have an Action Log and in the log we have a column that identifies the different projects our team leads. There is also a Workstream column, which identifies what workstream is responsible for getting the answer. There can be more than one answer, so we have a dropdown and if multiple workstreams are responsible for the…
