Conditional formatting dates overlap

The Excel SUMPRODUCT function multiplies ranges or arrays together and then returns the sum of products. It is an incredibly useful function with much more suppleness as other functions and can be easily used inside the SUMPRODUCT function to extend its functionality even further. With the help of the SUMPRODUCT function, you can check conditional formatting dates overlap in Excel. The Excel SUMPRODUCT function is just perfect for this kind of test because it handles array comparisons sophisticatedly.   

Formula

=SUMPRODUCT((start_date <= end_dates)*(end_date >= start_dates))> 1

Explanation

The formula incorporating Excel SUMPRODUCT function to check conditional formatting dates overlap uses the following arguments:

  • start_date (required): This is the start date of the project that we are checking for overlap.
  • end_date (required): This is the end date of the project that we are checking for overlap.
  • start_dates (required): This is the range of start dates of other projects.
  • end_dates (required): This is the range of end dates of other projects.

This works as follows:

  • For a project to overlay other projects’ dates, the following two conditions must be compulsorily true:
    • The start date must be less than or equal to (<=) at least one other end date in the list or range.
    • The end date must be greater than or equal to (>=) at least one other start date in the list or range.
  • We use the above formula to check the start and end date of the project against all start and end dates respectively.
  • The arrays that result from the TRUE & FALSE values are multiplied by one another inside the Excel SUMPRODUCT function. This leads the TRUE and FALSE values into 1’s and 0’s automatically.
  • If the return value of SUMPRODUCT function is greater than 1, then TRUE or else FALSE.

Example

In this example, we have several projects and their start and end dates. To check conditional formatting dates overlap, the following formula is used using Excel SUMPRODUCT function;

=SUMPRODUCT(($C5<=$D$5:$D$9)*($D5>=$C$5:$C$9))>1

This works as follows:

  • To check the project start date against all the other end dates, we use the following expression, ($C5<=$D$5:$D$9). This further comes out to be {1,1,1,1,1}.
  • To check the project end date against all the other start dates, we use the following expression, ($D5>=$C$5:$C$9). This further comes out to be {1,1,0,0,0}.
  • Finally, the formula can be solved as follows,
    • =SUMPRODUCT({1,1,1,1,1}*{1,1,0,0,0})>1
    • =SUMPRODUCT({1,1,0,0,0})>1
    • =TRUE

Figure 1. Example of SUMPRODUCT function to check conditional formatting dates overlap in Excel

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar