excel formula

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel formula

Excel formula

resolvedResolved · Urgent Priority · Version 365

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

Awesome, worked a treat, thank you so much for your help on this.

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


 

Excel tip:

Moving between Worksheets without using the mouse

Use the 'Ctrl+PgDn' and 'Ctrl+PgUp' keys.

'Ctrl+PgDn' will move to the right and 'Ctrl+PgUp' will move to the left one worksheet at a time.

View all Excel hints and tips


Server loaded in 0.06 secs.

✓ w3speedster