Using Different Colour Font Depending On Which Cell Used

[mightymax76]

Hi,

Hopefully someone will be able to provide some guidance.
I have been tasked with setting up some spreadsheets at work to on applications coming into my department and the number of days it takes for us to deal with them. I am also looking at number of written enquiries as well. Using the following headings Application Received (B4), Enquiry Received (C4), Completed Date (E4).

Then using the following formula =NETWORKDAYS(IF(B4<>"",B4,C4),E4,$A$119:$A$158)
So that it will look at B4 determine if a date is in the cell if it isn’t populated it looks at C4 instead whichever cell is completed it then works out the networkdays by looking at E4. The text within the $ signs refer to bank holidays listed on the spreadsheet

Would it be possible to have anything in place that does that formula but depending on which cell was used (B4 or C4) it would provide the answer in a different coloured font? For example it works out which date it has to use for doing the networkdays and if it’s used the date in B4 it remains black and if it uses the date in C4 it turns red.

If so would someone be so kind in advising the formula I would need to use.


Thanking you in advance

[Palmetto]

Would it be possible to have anything in place that does that formula but depending on which cell was used (B4 or C4) it would provide the answer in a different coloured font? For example it works out which date it has to use for doing the networkdays and if it’s used the date in B4 it remains black and if it uses the date in C4 it turns red.

Use Conditional Formatting for the formula cell to look at both B4 and C4. Use two conditions and set the format to colors of choice.

[mightymax76]

Use Conditional Formatting for the formula cell to look at both B4 and C4. Use two conditions and set the format to colors of choice.

Many thanks for the advice, but sorry for being thick how would that look within the formula I am already using =NETWORKDAYS(IF(B4<>"",B4,C4),E4,$A$119:$A$158)

Thanking you in advance

[Palmetto]

how would that look within the formula I am already using =NETWORKDAYS(IF(B4<>"",B4,C4),E4,$A$119:$A$158)

You are already testing B4 for a value (null or a date) and if it is blank you use C4 per your formula.

So, you would use the Formula option of Conditional Formatting using something like this:

=AND(B4="",C4>0)

This formula will test if B4 is blank (no date) and the C4 has a number (date serial in Excel). Set the fill color to red. When the conditions are met the cell will turn red and you'll know it used the date in C4. If the conditions are not met the cell will remain black

[mightymax76]

Ah right, now I’m lost! I have never done conditional formatting.
So if I am using =NETWORKDAYS(IF(B4<>"",B4,C4),E4,$A$119:$A$158) in my cell to work out the number of days it has taken to complete either task. If I add =AND(B4=””,C4>0) to that formula will that then populate the answer in either red or black font?
I have attached a screen-print showing what I am trying to achieve, I would like the formula to determine which cell to use for the date (B or C) and then workout the NETWORKDAYS in cell H using the complete date in cell E with whichever cell has got the received date in it. Once it has done the number of days, either keep the text normal (black) if it’s the date is cell B was used and date was used in cell C changed the colour to red.

I have also looked at the conditional formatting option on excel and to be honest I’m at a complete loss how that all works if that’s what you are referring to. I attached another screen shot showing you what I get on that as well.

Again bowing to your superior knowledge and thanking you in advance

[Palmetto]

See this link for an introduction on How to use Conditional Formatting

In the attached example:
Delete the value from B4 and enter a date into C4. Cell F4 will change to red.
Notice there is no formula in cell F4. The formula for Conditional Formatting is NOT added to cell formulas; it is only used in the Conditional Formatting dialog.

Select cell F4, go Menu > Format > Conditional Formatting and look at the dialog.

See this link for a demonstration of how to use Conditional Formatting at YouTube

[mightymax76]

Ok cooooooool now I fully understand! Thats fantastic!!

Many thanks for your help its been brilliant!!

I now have another question but will start a new thread although its related

Again thanking you for your help its been great