## Visualize date ranges in a calendar part 2

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**

- Select cell B2
- Go to tab "Data"
- Click "Data Validation" button

- Select List
- Type: Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec in Source:
- Click OK button

**Calculate first date in selected month in cell D1**

Formula:

**Calculate last date in selected month in cell D2**

Formula:

**Hide cell values in cell range D1:D2**

- Select cell range D1:D2
- Press Ctrl + 1
- Go to "Number" tab
- Click "Custom"
- Type ;;;
- Press OK

**Calculate dates**

Formula in cell B4:

Formula in cell C4:

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

**Filter names in column A**

Array formula in cell A5:

- Copy array formula
- Paste in formula bar
- Press and hold Ctrl + Shift
- Press Enter
- Copy cell A5 and paste to cell range A6:A17

**Red conditional formatting**

Conditional formatting formula applied to cell range B5:AF17:

**Green conditional formatting**

Conditional formatting formula applied to cell range B5:AF17:

### Recommended posts:

Visualize date ranges in a calendar in excel

Advanced Gantt Chart Template

### Download excel file

Visualize overlapping date ranges part 2.xlsx

### 14 Responses to “Visualize date ranges in a calendar part 2”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

If you are interested, here are shorter alternate formulas for the first and last day of the month given the setup you posted above...

First Day: =1*(1&B2&B1)

Last Day: =(1&B2&B1)+31-DAY((1&B2&B1)+31)

Note: The parentheses are important and must remain as shown.

Rick Rothstein (MVP - Excel),

Thanks! Of course I am interested.

Please group Name in row date ?

Jose,

Can you explain in greater detail?

I've been looking for something like this, but need to make a change. Currently if a name has two or more start and end dates in a the same month, each entry appears on a different row. I would like each name to appear only once, but all their dates from that month to appear on the calendar. Do you think this is possible? Thanks

Hi Oscar, thanks for this solution. Unfortunately my dates are not unique values, so the formula always pulls the smallest campaign name instead of the next campaign name, i.e. if the first 3 entries (James, James and Robert) all start on the same day, the calendar would show me 3 x James instead of James, James, Robert. Any idea on how to fix this? I assume I need to get rid of the SMALL formula but I can't quite get it to work. Any hints are welcome!

Am having a probem when it come with different names having the same dates,if 3 entries (James,Michael and Robert) all start on the same day, the calendar would show me 3 x James instead of James, Michael, Robert. Any idea on how to fix this?

Hi Oscar,

How can we add more than 13 names to be marked in the calendar?

I have an excel were I register the rented cars of a company and I want to mark the days the cars are rented in the calendar, I tried to add more rows and changed the formula to contain the rows that are being calculated but it doesnt seem to work.

Thank you

[…] run into a glitch with conditional formatting on a date range visualization spreadsheet I got from Visualize date ranges in a calendar part 2 | Get Digital Help - Microsoft Excel resource . I took the existing conditional format referencing green date ranges and modified it to add […]

Hi,

Trying to get this to work, can't see where I am going wrong, changing the month increases the days to number of days in the year, if I put 2015 in the year I get 42005 in D1. Is something formatted wrong, can't see any formula errors.

Cheers

Simeon

Sorry sorted formats working now.

Ta!

Your calendar is exactly what I was looking for. My names and dates, however, are in a different tab and I cannot make it work. For example, this is one of the formulas reading from a tab called Pipeline. Any ideas on how to fix this?

=IFERROR(INDEX(Pipeline!$I$20:$I$1000, SMALL(IF((Pipeline!$D$20:$D$1000=$D$1),MATCH(Pipeline!$D$20:$D$1000,Pipeline!$D$20:$D$1000,0),""),ROW(A1))),"")

Hi, this is the closest thing I have found to what I am looking for, however, it's not quite there.

I am essentially trying to create something very similar for tracking time off, but these are the following modifications I need:

1) The names starting in cell A5 would need to be fixed. So, essentially I want each name to appear only once and I would like to see all of their dates for that month appear in the calendar

2) The formula would need to take into consideration time splitting. So for example, if employee1 takes time off between July 1-5th and then again between July 25-27th, then I would like to see these cells in the calendar highlighted.

I would be so grateful if someone could provide a solution. I have seriously been searching for a solution on and off for the past 2 months and have come up with nothing yet.

Thanks!

Caroline,

1, How is your data arranged? Name and then a single date in each cell or multiple date ranges?