Thank you Purfleet. It seems a helper column is needed but no problem with that. I changed the $F$1 to DATE(YEAR(TODAY(),1,1) and the "" before the "NO" to "YES". What I'm trying to do now is to transfer the "NO" to the earliest registration not the latest.
I will get back if I could not get the desired result.