Conditional formatting formula

[Mrlewis]

Hi All,
I have been modifying a project spreadsheet and have a small issue. I'm using the conditional formatting with a formula that uses the percentage in another cell and the date range in other cells to colour milestones up to a certain date. It's seems to get to the milestone date only when I input 130 percent as opposed to 100 percent. Can anyone help with this please?

 

[Joe4]

I have been away from the computer and just saw this now, as I am heading off to bed. I will try to take a look at it tomorrow night, when I am on my home computer again (I cannot download it to my work computer). It is going to take a little time to work through it all.

 

[Mrlewis]

Thank you in advance

 

[Joe4]

OK. I am looking at your sheet. Can you tell me one example on your sheet is not working, and what your expected outcome of that row is?

 

[Mrlewis]

If I put any of the tasks at 100% the colour bar on the right doesn't colour in dark blue over the light blue which is the total amount of that milestone. It will only do it when I set it to 130%

 

[Joe4]

Focus on this part of your formula:

NETWORKDAYS.INTL($F12,$G12)

I think the issue is that your calendar includes weekends, but the NETWORKDAYS.INTL function does not (see: https://exceljet.net/excel-functions/excel-networkdays.intl-function)

So if you look at your example on row 12, the duration is 17 days, but the NETWORKDAYS.INTL part returns 13 days.
If you add 13 days + 1 to January 1, you get January 15 (not January 17). So you will always be short.
There are 7 days in a week, but only 5 are workdays. So if you divide 7/5, you get roughly 128.57% (or close to 130%).

So the issue appears that you seem to be trying to account for weekend days in one part of your worksheet, but not in another (and you are trying to compare the two).