Calculate Dates with Conditional Formatting

Hi everybody,

I'm not sure if this is even possible but here it goes:

I have people taking vacation 5 or 6 times a year. I need to present a table showing how long they have worked & how long they were on vacation. This is represented by colored bars (Green/Red) which should be automatically highlighted depending on the dates I input (Column C:N).

The spreadsheet has 52 weeks (columns) for a year.

I'm sure the tricky part will be how to put the total days in each bars and merge/center them for easy navigation.

I've attached a sample worksheet for reference and more explanation.

Thanks even for just checking it out.