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! I'm working on a column formula that will check whether the Column2ref cell in the current row is blank. If it is blank, the formula finds the maximum value in the ID column where the Trainer Name matches the Trainer Name cell value in the current row and the ID cell value is less than the ID cell value in the…
How do I see who submitted a form for a specific sheet?
Hi, I have a Gantt covering 500+ rows of events past and planned. I use it to predict and track values around these events. Using helper columns to identify the current week, I get an email report on a Monday that tells me the upcoming events in the current week (7 days) and their values. Currently if an event spans…
