Conditional Format Question

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://video2.skills-academy.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.