I work with a team that serves our customers open enrollment needs. One of the key tasks is to track open enrollment.
When a case comes in from our customer, it provides us with three key pieces of information, in which our team should record in Smartsheet.
Part of the formula should look at these three fields and if one or more is blank, that read as though it is not complete.
The other part of the formula needs to take into account the OE begin date.
Both of these inputs (whether all 3 fields are entered AND reading from the OE begin date) should determine whether the status in R/Y/G. Here's the criteria:
- Green = The D365, PS Benefits Owner and PS Files Review columns are all populated in the tracking sheet OR at > 8 weeks from Anticipated OE Date.
- Yellow = The D365, PS Benefits Owner and PS Files Review columns are not all populated (e.g., none at all or at least one field missing), AND the project is between 6 – 8 weeks from the Anticipated OE Date.
- Red = The D365, PS Benefits Owner and PS Files Review columns are not all populated (e.g., none at all or at least one field missing), AND the project is less than 6 weeks from the Anticipated OE Date.
All of that needs to feed into a sheet summary because I want to report it in our operations dashboard. Something like this:
I would love your input!
