Help with formula to add dates (if cell contains certain text) and Text Colour

Hello guys, I have been racking my brain all morning trying to figue out the correct formulas. As I am not yet overly familiar with Excel formulas I thought it would be best to ask to ask for a little help rather than waste any more hours on this.

Basically I send samples away to a lab and depending on what the lab are testing them for the turn-around time will differ. I would like a column to calculate the date that the results are due back.
For Example:
If they are tested for Legionella add 14 days
If they are tested for Bacteria add 5 days
If they are tested for Chemical add 10 WORKING days (This one completely stumped me!)

I will manually input the day the samples are taken in Column A and I list what they are tested for D. The only Column that I need calculated for me is Column H.

Just to be awkward I would also like the text to be black by default but change to red if the date has expired (ie the results are late)

Any help or pointers would be very very much apprecaited. I have attached a dummy version of my Spreadsheet if it helps

Thanks

Anna

Hi Anna.

In H2, write the formula:

=IF(D2="Legionella";A2+14;IF(D2="Bacteria";A2+5;IF(D2="Chemical";WORKDAY(A2;10))))

For conditional formatting, write the code:

=TODAY()>$H3

>>>cOLOUR FORMAT>>rED


Pls take a look to the attachement.

Is it OK for you?

Regards

You're welcome.

Please mark the thread as Solved.