Excel formula

The simplest way to do this would be with an IFS function nested with workday functions. It would look something like this:

=IFS(F1="Standard",WORKDAY(E1,2),F1="Urgent",WORKDAY(E1,5))

The logic of this formula is that you are looking up the priority in column F, and then adding 2 or 5 working days to the date in column E based on which priority it is.

I did this with IFS rather than IF because it will then say N/A if you don't add any priority. If you have any other types of priority then you can also expand the IFS accordingly.

To add the conditional formatting in column I, select the entirety of the column, go to 'conditional formatting > New Rule > Use a formula to determine which cells to format'.

Charge the formatting to the red colour, and then the formula that you want will be this:

=$I1>$H1

This simply looks up whether the date in column I is greater than the date in column H. You need to make sure there is only 1 Dollar sign before the letter in each cell reference to make a partial absolute.

Hello Dominic,

Thank you or your question, we're happy to help.

For the first part of your question, here is a formula which may work:

=IF(F2="standard",WORKDAY(E2,2),IF(F2="urgent",WORKDAY(E2,5),E2))

When you use nested IF, you're able to check for both status types (standard or urgent).

The WORKDAY function returns a weekday date either 2 or 5 days after the Column E date.

E2 at the end of the formula is the Value If False, which returns the original date if no status is specified.

For the second part of your question, you need to build a formula-type rule in Conditional Formatting.

Select cell I2, then click Conditional Formatting. Go to New Rule > 'Use a formula to determine which cells to format'.
In the box below 'Format values where this formula is true', type the following formula:

=$I2>$H2 (this tests if the date in I2 is later than H2)

The reason there is only one $ is to be able to copy the conditional rule down the column.

Remember to set a red colour as format style.

I hope this helps!

Kind regards
Marius Barnard
STL