Conditional Format Question

Question

Conditional Format Question

Anonymous

Hi, I have an employee training list with 6 skill levels (1A, 2A, 1B, etc.). Each one of those levels has a minimum and maximum due date Calculated Column ("1A Min" and "1A Max") that is conditionally formatted by another "Hire Date" column using (=[Start Date]+120). I'm assuming I'd have to use JSON to add a condition to the "1A Min column" so that it'll add 120 days or 180 days depending on a Choice Column ("Certified" or "Mastered") in the same row.

I hope this makes sense, forgive me if it doesn't. I'm very green in SharePoint. I've attached a pdf for context. Any help is immensely appreciated. Thanks!

Bruno Gomes 111 Reputation points

2024-01-02T02:02:02.7766667+00:00
Hi AirborneSO,

Thanks for reaching out, and welcome to the world of SharePoint! Your scenario involving dynamic due dates based on skill levels and certifications can be elegantly handled with a combination of JSON formatting and lookup columns. Here's the breakdown:

1. Using JSON for Conditional Formatting:

You're right, using JSON adds flexibility to your conditional formatting rule. Here's the formula you can modify for each skill level:

JSON

={([Start Date]+IF([Certified]="Yes",120,180))>=TODAY(),"Red",TODAY()>[1A Max]"Yellow",""}

Use o código com cuidado. Saiba mais

content_copy

Explanation:

This formula checks if the calculated date ([Start Date] + minimum days based on "Certified") is:

Red: Before today, indicating overdue training.

Yellow: Between today and the maximum due date ("1A Max"), highlighting approaching deadlines.

Empty: Neither overdue nor approaching, leaving the cell formatting untouched.

2. Leveraging Lookup Columns for Cleaner Management:

Instead of hardcoding minimum and maximum days in separate columns, consider creating a lookup column called "Training Requirements." This column would:

Have two fields: "Skill Level" and "Days (Certified/Mastered)".

Contain rows for each skill level (1A, 2A, etc.) with corresponding minimum and maximum days for both "Certified" and "Mastered" scenarios.

Benefits:

Centralized management of training durations.

Easy updates: Changing durations requires modifying the lookup list, not individual formulas.

Improved formula readability: Reference the lookup column instead of hardcoded values.

3. Implementing the Lookup Column Approach:

Modify your conditional formatting formula to reference the "Days (Certified/Mastered)" field based on the corresponding skill level (e.g., ([Start Date]+[Training Requirements].[Days (Certified/Mastered)]...).

Ensure correct mapping between skill levels in the lookup list and your employee training list.

4. Testing and Refinement:

Apply the updated formula to a sample of your data and adjust formatting colors/rules as needed.

Share your findings and any remaining challenges with the community for further assistance.

Bonus Tip: You can even create another lookup column called "Due Date" that automatically populates with the calculated date based on the chosen skill level and certification status using a workflow.

By combining JSON formatting with lookup columns, you achieve dynamically adjusted due dates, improved formula clarity, and easier management of training requirements. Happy SharePointing!

Additional Resources:

JSON formatting in SharePoint: https://learn.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting

Lookup columns: https://support.microsoft.com/en-us/office/create-list-relationships-by-using-lookup-columns-80a3e0a6-8016-41fb-ad09-8bf16d490632

SharePoint workflows: https://support.microsoft.com/en-us/office/introduction-to-sharepoint-workflow-07982276-54e8-4e17-8699-5056eff4d9e3

Best regards,

Bruno Gomes.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Anonymous

2024-01-04T16:28:02.8566667+00:00

Wow! This is awesome and just what I was looking for Bruno. Thank you so much! I have one caveat though. If I wanted to add a 3rd option in the "Certified/Mastered" column - like "Transfer" what would the addition of that 3rd choice look like in the formula?