How can I give a cell a numeric value based on the colour and text within them?

We work a 12 hour shift pattern so a 'D' shift is equal to 1.5 days of Annual Leave
Every 2 months we do 2 weeks of 'D8' shifts which are equal to 1 day of Annual Leave
The same values should apply when an employee takes sick leave.
I would like the table on the right to auto-fill based on the inputs in the table on the left. If I fill a cell red and it has a 'D' as the text I would like that to be translated to a numeric value of 1.5 in the table on the right
but if the cell is red and has a 'D8' as the text I would like that to be translated to a numeric value of 1.0 in the table on the right.

Any and all help would be greatly appreciated.