Method to check whether a Assessment Date has passed upon multiple cells within a row.

Matthew Drake

Good afternoon,

We are currently using a smart sheet to track assessment bookings for different standards.

We enter the assessment dates within the relevant cells for a particular standard (normally 3 assessments per standard, however are named differently which has resulted in a large number of columns which is unavoidable based upon the desires of skills coaches). Next to each assessment column, a result column is present. (For example, there is a Knowledge Test, MCQ, Professional Discussion, Project, Interview, Practical Obs, Presentation, Etc)

I am trying to create a formula that will check multiple cells within a row and activate a check box or traffic light based on the set day being greater than the actual date without a result. (I.e to identify when an assessment has overlapped but no result record).

Does anyone know how I can complete this or if there is an easier way to achieve my goal?

I have been trying a very long-winded IF / AND statement that checks the recorded date against TODAY() and the Result column to return a value but nothing seems to be working. I thought I might try creating some control columns with checkboxes that activate based on each assessment and then using the IF statement based on each control column.

Thank you very much for your time and assistance.

Matt

Matthew J McAteer

@Matthew Drake

If you wanted to build a nested IF statement, you would add RYG ball Symbol column and use a formula something like this:

=IF(AND(TODAY() > [Knowledge/SJT/MCQ/Skills Test Date & Time]@row, ISBLANK([Knowledge Test Result]@row)), "Red", IF(AND(TODAY() > [Project / Showcase Portfolio]@row, ISBLANK([Project / Showcase Portfolio Results]@row)), "Red", "Green"))

You could keep building this out to evaluate all of your date / result fields, but you could run into issues where a date is not yet entered.

Matthew J McAteer

Hi @Matthew Drake,

Can you share a screen shot or your sheet for better context?

Thanks,

Matthew

Matthew Drake

Good afternoon,

I have removed the data and added a dummy entry. The assessments based upon the standard have dates submitted where-as the others have been left blank.

(I was going to do a conditional format to black out the none relevant assessment based upon the standard name column, however, with the amount of standards, this became impractical at present).

1.PNG

2.PNG

Thank you for your time, and I apologize if it has been setup wrong.

Kind regards,

Matthew J McAteer

Hi @Matthew Drake,

Thanks for the screen shots. So it sounds like you are looking for a type of status indicator that evaluates whether the date of an assessment has passed and there is no entry in the corresponding result column. For example, in your sheet Knowledge/SJT/MCQ/Skills Test Date & Time is 26/10/23, and Knowledge Test Result is "Merit", so a status indicator would be Green because the Test Result is entered, but if the Test Result were blank tomorrow 27/10/23, you would want the indicator to be Red. Is that correct? If that assumption is correct, you want a way to then continue evaluating the subsequent date fields on the sheet.

Matthew Drake

Hello, @Matthew J McAteer

That is correct thank you, however, it would need to check all assessments associated with the standard (3 in the case of the example provided).

Would it be easier to have an identifier for each assessment and then an overall identifier that checks the other identifier?

Thank you by the way.

Matthew J McAteer

@Matthew Drake yes, you could create an identifier for each assessment, then for the overall identifier write a formula that checks if the status of any of the other identifiers is Red, and if there is one, then the overall identifier would be red. This method would be a series of shorter more manageable formulas.

Matthew J McAteer

@Matthew Drake

If you wanted to build a nested IF statement, you would add RYG ball Symbol column and use a formula something like this:

=IF(AND(TODAY() > [Knowledge/SJT/MCQ/Skills Test Date & Time]@row, ISBLANK([Knowledge Test Result]@row)), "Red", IF(AND(TODAY() > [Project / Showcase Portfolio]@row, ISBLANK([Project / Showcase Portfolio Results]@row)), "Red", "Green"))

You could keep building this out to evaluate all of your date / result fields, but you could run into issues where a date is not yet entered.

Matthew Drake

@Matthew J McAteer

Good morning,

Thank you so much for all your assistance, thanks to your formula I managed to get exactly what I needed, just added a little at the start for if there is no date to show nothing.

=IF(ISBLANK([Knowledge/SJT/MCQ/Skills Test Date]@row), "", IF(AND(TODAY() > [Knowledge/SJT/MCQ/Skills Test Date]@row, ISBLANK([Knowledge Test Result]@row)), "Red", IF(AND(TODAY() > [Knowledge/SJT/MCQ/Skills Test Date]@row, NOT(ISBLANK([Knowledge Test Result]@row))), "Green", "Yellow")))

(Just going to make an indicator for each assessment and then an overall indicator)

Thank you again, really appreciate it.

Kind regards,