0

I'm working on a fairly large table that has multiple data points in it that contain various dates.

The table looks roughly like this:

Case No. Request Date 1 Response Date 1 Request Date 2 Response Date 2
1 1 Jan 2021 7 Jan 2021 10 Jan 2021 21 Jan 2021
2 5 Jan 2021 8 Jan 2021 8 Jan 2021 15 Jan 2021

The 'Request' and 'Response' Dates - in theory could - go on infinitely. All of this data sits in a named excel table called "AllData".

To help easily visualise where a response has been slower than normal (imagine a response should be within 2 working days), I've been trying to do conditional formatting where the Response Date [x] cell changes colour to red if the date within it is greater than 2 working days from the Request Date [x], where [x] is the same number (i.e. Request Date 1 and Response Date 1; and so on).

Every approach I know seems to involve having a new conditional formatting rule for each cell. My current approach is to use the WORKDAY formula combined with conditional formatting:

Example, in cell C2 (Case No. 1, Response Date 1):

'Cell Value' 'greater than' =WORKDAY(B2, 2)

But this approach means that I have to apply a new rule for each cell to replace B2 with D2, F2, B3, etc.

Is there an efficient way of approaching this? I would have thought that by using tables the conditional formatting should 'update' the cell reference, but this does not appear to be the case.

Thanks!

vika09
  • 11
  • 2

1 Answers1

0

Option 1 - Use Conditional formatting in Rule Manager change the 'applies to' the column range. When viewing Conditional Formatting 'Manage Rules...' it appears that the formula is only referencing C2 and B2 however if you remove the $ (static cell or row) excel will cascade the formula down and 'applies to' your column range C2:C7 (in this case). Try it out :)

Conditional formatting Rule: =C2>B2+2 *remove $

Applies to: C2:C7 *the $ can stay, or be removed

enter image description here

Option 2 - Use Helper Column

Formula for days Elapsed: =C2-B2

Conditional formatting: Cell Value >=2

enter image description here

Updated to include answer for second part of question

Option 1 - Use helper column to show Due Date of response required and then compare the Due Date against the Response date column using a simple > than conditional format formula

Due Date column formula: =WORKDAY.INTL(A2,2,1,$E$2:$E$15)

Where:

A2 = start date (Request)

2 = days to add to start date *note this is a full 2 biz days

1 = weekend (Sat & Sun)

E2:E15 = List of holidays to account for *I used a list of Federal American holidays i found online in this example

Response Column conditional format formula: =B2>C2 Applies to B$2$:$B$8

table example

aaron_ca
  • 74
  • 10
  • Oh my god. I'm just an idiot, I can't believe I didn't notice the $ symbols in the formula and the $ symbols in the 'applies to' area. I have a quick follow up; does conditional formatting accept NETWORKDAYS formula? The reason I'm hesitant to use "=C2>B2+2" is because I want to exclude weekends and public holidays. When I try to use "=NETWORKDAYS(B2,C2,PublicHolidays)>2" I get a "There's a problem with this formula" error. – vika09 Jun 08 '21 at 08:25
  • hey its cool, I actually didn't know that either :) Will dig into a conditional formatting rule that excludes weekends and let you know if i find anything that works. – aaron_ca Jun 08 '21 at 13:40
  • @vika09 updated answer with a simple solution. I am sure a complex conditional format formula exists, however i found this solution to simple and easy to implement – aaron_ca Jun 09 '21 at 02:29