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.

I didn't mention anything for the R-D and R-N cells as they are just to show the Overtime for the person covering the shift. This will not affect the A/L or S/L balances. I am trying to achieve this result with minimal changes to the existing document but if needed then I believe I can get them authorised. If I were to use codes to represent the events then how could I convert those to values?

Thanks for the above example! I have discussed this with management and they like the idea but have said that from a HR perspective that the data needs to only be visible to a select few.

I have tried to hide and protect only the table but it then protects the rest of the cells within the sheet from being edited by others who don't have the password. We still need for unprivileged users to be able to edit the rota but not be able to see the output data. Is there any way to either only hide and protect the desired cells or to have a formula that outputs data so that it only appears on a separate sheet which I can then hide and protect?

Thank you kindly! Didn't realise it was that easy......