Subtraction using a column with a formula

Options

For some reason I cannot use a subtraction formula when pulling the info from a column that has an existing formula. Is that a thing or am I doing something wrong?

I'm using the simple formula below

=[PCA In service required]@row - [In service hours completed]@row


Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Tzippy

    It's the quotes around your numbers that turns them into text. For example:

    IF(CONTAINS("PCA", Discipline@row), "6"

    Should be

    IF(CONTAINS("PCA", Discipline@row), 6


    Try adjusting that formula to be this:

    =IF(AND([Most recent hire date]@row < DATE(YEAR(TODAY()), 4, 1)), IF(CONTAINS("PCA", Discipline@row), 6), IF(AND([Most recent hire date]@row >= DATE(YEAR(TODAY()), 4, 1), [Most recent hire date]@row < DATE(YEAR(TODAY()), 10, 1)), IF(CONTAINS("PCA", Discipline@row), 3), IF(AND([Most recent hire date]@row >= DATE(YEAR(TODAY()), 10, 1), [Most recent hire date]@row < DATE(YEAR(TODAY()), 12, 31)), IF(CONTAINS("PCA", Discipline@row), 0))))


    Cheers,

    Genevieve

Answers

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭
    edited 09/08/22
    Options

    Hello @Tzippy

    I think this is likely because in the "PCA In Service Required" column, there is a function (the small fx symbol) below the column name.


    This is a shot in the dark, but perhaps we can try something like this:

    =VALUE([PCA In service required]@row) - [In service hours completed]@row

    Hope this helps!

  • Tzippy
    Options

    Hi @Mr. Chris

    The fx just means that there is a column formula I believe...

    I tried your formula but got the invalid operation error message.

    Any other ideas?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Tzippy

    I agree that it looks like your column formula is turning your number into a text value. You can see this because the number appears on the left side of the cell instead of the right.

    The VALUE() function should have converted it back to being a number. Since that didn't work, can you post what formula you're using in the "PCA in service required" column? We may be able to adjust that formula to ensure the output is numerical (e.g. removing any quotes around it).

    Cheers,

    Genevieve

  • Tzippy
    Options

    Hi @Genevieve P.

    The formula in the pca in service column is:

    =IF(AND([Most recent hire date]@row < DATE(YEAR(TODAY()), 4, 1)), IF(CONTAINS("PCA", Discipline@row), "6"), IF(AND([Most recent hire date]@row >= DATE(YEAR(TODAY()), 4, 1), [Most recent hire date]@row < DATE(YEAR(TODAY()), 10, 1)), IF(CONTAINS("PCA", Discipline@row), "3"), IF(AND([Most recent hire date]@row >= DATE(YEAR(TODAY()), 10, 1), [Most recent hire date]@row < DATE(YEAR(TODAY()), 12, 31)), IF(CONTAINS("PCA", Discipline@row), "0"))))

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Tzippy

    It's the quotes around your numbers that turns them into text. For example:

    IF(CONTAINS("PCA", Discipline@row), "6"

    Should be

    IF(CONTAINS("PCA", Discipline@row), 6


    Try adjusting that formula to be this:

    =IF(AND([Most recent hire date]@row < DATE(YEAR(TODAY()), 4, 1)), IF(CONTAINS("PCA", Discipline@row), 6), IF(AND([Most recent hire date]@row >= DATE(YEAR(TODAY()), 4, 1), [Most recent hire date]@row < DATE(YEAR(TODAY()), 10, 1)), IF(CONTAINS("PCA", Discipline@row), 3), IF(AND([Most recent hire date]@row >= DATE(YEAR(TODAY()), 10, 1), [Most recent hire date]@row < DATE(YEAR(TODAY()), 12, 31)), IF(CONTAINS("PCA", Discipline@row), 0))))


    Cheers,

    Genevieve

  • Tzippy
    Options

    @Genevieve P.

    Yes!! That worked!! I had no idea about this quotations and on which side of the cell the number is! Thanks for teaching me that and your patience for helping me with this!!

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    No problem! I'm glad to hear it worked 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!