Subtraction using a column with a formula

Tzippy

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

Genevieve P.

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

=Chris Palmer

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

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.

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

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.

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

@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.

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