IF, CONTAINS, NETDAYS, AND, CONTAINS

Question

Hi all,

Trying to use a formula to work out net days between events in a column (Visit End Date) when a second column (Visit Description) contains "V" but also only if the last visit also contains a V.

=IF(CONTAINS("MV", [Monitor Visit Description]@row), NETDAYS([Monitor Visit End Date]1, [Monitor Visit End Date]2)) worked to figure out net days between but does not distinguish if the last visit description as also V

I have tried:

=IF(CONTAINS("V", [Visit Description]@row), NETDAYS([ Visit End Date]1, [Visit End Date]2)), AND(CONTAINS("V", [ Visit Description]1))

But this returns unparseable.

Any help is appreciated :)

Paul Newcome · Answer

Are you able to provide some screenshots for context?

Yulichka · Answer

The reason your second formula is returning #UNPARSEABLE is because of the placement of the AND() function. In Smartsheet, the AND logic needs to wrap inside the logical test part of the IF statement, rather than being tacked onto the end of the NETDAYS function. Additionally, using specific row numbers like [Visit End Date]1 means the formula won't dynamically look at the previous row relative to where the formula is sitting. To look at the current row and the row directly above it, you will want to use the row number index. Here is the corrected formula format: Excel =IF(AND(CONTAINS("V", [Visit Description]@row), CONTAINS("V", [Visit Description]1)), NETDAYS([Visit End Date]1, [Visit End Date]@row), "") ⚠️ Crucial Step for Row-by-Row Math Because Smartsheet formulas need absolute references when dragging down rows, pasting a formula with a literal 1 can sometimes break. The best practice for a "look at the row above me" formula is to put this in your second row of data (e.g., Row 2): Excel =IF(AND(CONTAINS("V", [Visit Description]@row), CONTAINS("V", [Visit Description]1)), NETDAYS([Visit End Date]1, [Visit End Date]@row), "") When you drag this formula down from Row 2 to Row 3, Smartsheet will automatically adjust it to:=IF(AND(CONTAINS("V", [Visit Description]@row), CONTAINS("V", [Visit Description]2)), NETDAYS([Visit End Date]2, [Visit End Date]@row), "") How this logic breaks down: * AND(...): Checks if both conditions are true before doing any math. * CONTAINS("V", [Visit Description]@row): Checks if the current row has a "V". * CONTAINS("V", [Visit Description]1): Checks if the row above it has a "V". * NETDAYS([Visit End Date]1, [Visit End Date]@row): If both are true, it calculates the net days between the prior date and the current date. * ,"": If either row does not contain a "V", it leaves the cell completely blank instead of throwing an error.

IEPE · Answer

Hello, I am sharing a link to a published sheet with the example, I hope that is helpful.

https://publish.smartsheet.com/694c2d7969f542638ed20a1831ec2a65