Cell with 2 formulas voids conditional formatting

[eeframe27]

Hello! I am using Excel 2016 and having an issue with conditional formatting.

Cell E2 has 2 formulas at play:
=IF(F2="",0,DAYS360(D2,F2))&IF(ISBLANK(F2),TODAY()-D2,"")

This way I can calculate the number of days an invoice has been with my office using the date entered in D2 and the current date. When the date we sent the invoice to accounting is later entered into F2, E2 will then display the number of days the invoice was in our office using cells D2 and F2.

The formulas work to display the correct number of days, but my conditional formatting will not work. I have formatted E2 to highlight in red if the cell value is over 15, but no matter what the value in E2, it is always red. How can I get conditional formatting to apply in E2?

*Note* Conditional formatting works correctly in my cells that use only one of the below formulas at a time:
=TODAY()-D3
=IF(F4=””,0,DAYS360(D4,F4))

[AliGW]

Because when you combine the two, the answer is no longer a number, but text, so use this:

=VALUE(IF(F2="",0,DAYS360(D2,F2))&IF(ISBLANK(F2),TODAY()-D2,""))

You can always set the formatting to show leading zeroes if you really need them.

[Mvaldesi]

It's because the result of your concatenated formulas is a Text value, i.e.: the "07" in E2 is actua; "0" and "7" run together, and Excel doesn't see it as the number "7." What is it exactly you're trying to accomplish with the "&" and two formulas? Seems to me there's a simpler way (i.e.: a nested =IF() statement?) to achieve what I think you're going for.

[BadlySpelledBuoy]

Another solution:

Please Login or Register  to view this content.

BSB

[Mvaldesi]

Does this work for you?

=IF(ISBLANK(F2),DAYS360(D2,TODAY()),DAYS360(D2,F2))

[eeframe27]

Oh perfect! This works. Would you be able to help format it further so that if D2 is empty, E2 will display "0." Currently if D2 is empty, E2 displays "043291"

[Mvaldesi]

Maybe this?

=IF(ISBLANK(D2),0,IF(ISBLANK(F2),DAYS360(D2,TODAY()),DAYS360(D2,F2)))

EDIT: Take the zero in first logical test out of quotes.

[AliGW]

Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[jason.b75]

Assuming column F will be either date or empty, something as simple as

=IF(F2,F2,TODAY())-D2

Formatted as number.

All of these extra functions you are using are just making the task more complicated than it needs to be.