Combine IF Function with Conditional Formatting (in Data Bar form)

[Duarte Lousa]

Dear all,

I'm wondering if you can help:
I'm trying to use conditional formatting (in Data bar form) to show the progress of a specific task between 2 dates (Date 1 and Date2 or Date 3):

Date 1: Start date
Date 2: Estimated Finish Date (on time)
Date 3: Actual Finish Date (late assuming Date 3>Date 2)

The progress is calculated based on the following formula:

=IF(Date 3>Date 2,(today()-Date 1)/(Date 3-Date 1),(today()-Date 1)/(Date 2-Date 1))

Depending on the final date used, i would like the data bar to be shown in:

a) Red if Date 3>Date 2 is true.
b) Green if Date 3>Date 2 is false.

Is there any way this can be done without using VBA?

Thank you very much for your help.
Best regards,

[Pepe Le Mokko]

Hi
as you can see in the yellow banner, posting a sheet makes things easier and faster for everybody.
Thanks

[Duarte Lousa]

Please see the example attached.

Thank you very much.
Best regards

[JeteMc]

Would putting the percentage and data bar in the cell adjacent to the finish date used be an option.
If so then the formula for D5 could be: =IF(C5>=C6,(C7-C4)/(C5-C4),"")
The formula for D6 could be: =IF(C6>C5,(C7-C4)/(C6-C4),"")
D5 could be formatted for a green status bar and D6 could be formatted for a red status bar.
Let us know if you have any questions.