Conditional Formatting - Colouring % of mulitple cells based on date range

[yeahyeah93]

can someone please provide some guidence I am trying to get a row of cells to highlight a percentage based on a date range

Below is an example of what my spreadsheet will look like, very simple for managers to read and understand but I am stuck on how i can get this to display the right way.

In the example i would need the Jan column to colour for a certain percent for 21 days and continue to feb for 26 days.
Im not sure if this makes sense but this is what they are asking for.
Colour bars to simple show the percent of days off each month.


Name Start Date End Date Jan Feb Mar

Dale 11/01/14 26/02/14 21 days 26 days

I have attached the spreadsheet for an example.

It is probably the simpliest thing on earth to do...

[Richard Buttrey]

Hi,

It's not clear what you're asking for. You mention percentage but I can see no values that can be used to derive a %. Neither do I understand what you mean by colour for 21 days.

You also show start and end dates although it's not clear whether these are relevant and should be used in any way.

Upload your workbook and manually add results you expect to see for specific examples. Note which are the result cells and explain how you arrived at them.

[yeahyeah93]

Book1.xlsxNo worries attached is the example

The dates are from when the injury occured to when the worker comes back to work, they are neccesary to calculate the days that employee spent away from work.
They want a visual

What i want to show for example for dale the period he was away from work for the two months on a colour bar/data bar
Im not sure how to do this or how to display the data...

[Richard Buttrey]

Hi,

The attached is the nearest I've come so far.

After googling around I don't think there's sufficient control over the data bars. Ideally it would be nice to be able to determine the last cell in a row that has a value - which can easily be done, and then knowing that change the data bar to be a left to right bar rather than the default used here which is a right to left.