Dominic has attended:
Excel Advanced - Formulas & Functions course
Excel Intermediate course
Excel Introduction course
Excel formula
Just wondering if you could help.
I am trying to create a lookup formula to populate a date in column H based on the date in column E with a priority from column F where if standard is entered (+2 working days) or Urgent (+5 working days).
I would also like to then colour code column I (date actually completed) red if the date entered is after the date in column H.
Any help would be really appreciated as I've been at it for hours and i think my brain is going to leak out of my ears shortly.
Regards
Dom
RE: 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.
RE: Excel formula
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
