# Sheet formula efficiencies

Options
edited 12/09/19

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:

Tags:

• ✭✭✭✭✭✭
Options

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

Does this screenshot help which shows the column headings?

• Options

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)))))

• ✭✭✭✭✭✭
Options

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)))

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

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)))

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!