I do not know how to dynamically change the RANK formula based on the date.

For each day (or each new date) I have to rewrite the formula in order to match the range of the D-Day.

Date    Name    Points  Ranking Reward
27 Jul  Andy    3          3    6
27 Jul  Mike    14         2    8
27 Jul  Lucy    78         1    10
26 Jul  Andy    45         2    8
26 Jul  Mike    65         1    10
26 Jul  Lucy    12         3    6
25 Jul  Andy    123        3    6
25 Jul  Mike    23423      2    8
25 Jul  Lucy    466566     1    10

See the google sheet here :