Status checker (Nest/BAU)

[Rhon Alvarez]

hi Team,

needs assistance please on how to correctly tag each employee of their actual status with the company based on either Production Date (Col M) or Re-endorsement Date (Col R).

here's different Scenarios to be fulfilled:
Scenario 1: (newly Hired agents)
if Cols M and R are both blanks, Col AC should be tagged as "Nesting"

Scenario 2 (BAU agents transferred to another LOBs; will be considered new hire and will be tagged as Nesting until a confirmed date was inputted in Col R)
Col M should have the original endorsement date but if Col R is still blank, will be tagged as "nesting" in Col AC
***** this scenario left me with having all the agents tagged as "Nesting" since no date were provided in Col R.
*****would like to consider a helper column but no idea what to provide (a word "transferred" maybe) i.e. "transferred to LOB "
*****if future date was provided in Col R, no changes yet should reflect in Col AC.

Thanks,

Rhon

 

[MikeVol]

In Col AC put next formula:

=IF(AND(M11="", R11=""), "NESTING",
   IF(R11<=TODAY(), "BAU", "NESTING"))

Perhaps I misunderstood you. Good luck.