I have created a new version of Visualize date ranges in  a calendar. This excel file let´s you enter names and date ranges (A20:G33). Duplicate names are allowed. Select year and month, days in that month are automatically calculated (row 4). Names whose date ranges are present in the selected month are also automatically shown in cell range A5:A17. Dates are red if they overlap. This workbook contains no vba code.

How I created this excel sheet

Data list validation in cell B2

  1. Select cell B2
  2. Go to tab "Data"
  3. Click "Data Validation" button
  4. Select List
  5. Type: Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec in Source:
  6. Click OK button

Calculate first date in selected month in cell D1

Formula:

=DATE(B1, MATCH(B2, {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}, 0), 1)

Calculate last date in selected month in cell D2

Formula:

=DATE(B1, MATCH(B2, {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}, 0)+1, 1)-1

Hide cell values in cell range D1:D2

  1. Select cell range D1:D2
  2. Press Ctrl + 1
  3. Go to "Number" tab
  4. Click "Custom"
  5. Type ;;;
  6. Press OK

Calculate dates

Formula in cell B4:

=D1

Formula in cell C4:

=IF(MONTH($B$4+COLUMN(A1))<>MONTH($D$2), "", B4+1)

Copy cell C4 and paste to cell range D4:AF4.

Filter names in column A

Array formula in cell A5:

=IFERROR(INDEX($A$20:$A$33, SMALL(IF(($B$20:$B$33<=$D$2)*($G$20:$G$33>=$D$1),MATCH($B$20:$B$33,$B$20:$B$33,0),""),ROW(A1))),"")
  1. Copy array formula
  2. Paste in formula bar
  3. Press and hold Ctrl + Shift
  4. Press Enter
  5. Copy cell A5 and paste to cell range A6:A17

Red conditional formatting

Conditional formatting formula applied to cell range B5:AF17:

=IF((INDEX($B$20:$B$33, SMALL(IF($A5=$A$20:$A$33, MATCH(ROW($B$20:$B$33), ROW($B$20:$B$33)), ""), COUNTIF($A$5:$A5, $A5)))<=B$4)*((INDEX($G$20:$G$33, SMALL(IF($A5=$A$20:$A$33, MATCH(ROW($B$20:$B$33), ROW($B$20:$B$33)), ""), COUNTIF($A$5:$A5, $A5)))>=B$4)), SUMPRODUCT(($B$20:$B$33<=B$4)*($G$20:$G$33>=B$4))>1, FALSE)

Green conditional formatting

Conditional formatting formula applied to cell range B5:AF17:

=(INDEX($B$20:$B$33, SMALL(IF($A5=$A$20:$A$33, MATCH(ROW($B$20:$B$33), ROW($B$20:$B$33)), ""), COUNTIF($A$5:$A5, $A5)))<=B$4)*((INDEX($G$20:$G$33, SMALL(IF($A5=$A$20:$A$33, MATCH(ROW($B$20:$B$33), ROW($B$20:$B$33)), ""), COUNTIF($A$5:$A5, $A5)))>=B$4))

Recommended posts:

Visualize date ranges in a calendar in excel
Advanced Gantt Chart Template

Download excel file

Visualize overlapping date ranges part 2.xlsx

 

Related posts:

Visualize date ranges in a calendar in excel

Sorting date ranges in excel

Dynamic stock chart in excel – Add date ranges

Date ranges: Weeks within a month

Count and visualize specific weekdays in a custom date range