## Plot date ranges in a calendar part 2

*Article updated on July 12, 2018*

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

### 27 Responses to “Plot date ranges in a calendar part 2”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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?

Easiest way to resolve this is to use employee ID number or a unique identifier, thats what I've done

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?

Good afternoon,

I really like the calendar but I have few questions.

When I put multiple entry and dates overlap, some entry won't appears on the list.

Is there a way to make it indexed without the red highlight. Just have a list of all events and highlight the dates as well

Thank you

Eric

Can you provide some date examples where the entries won't appear?

Hi Oscar!

This is so close to what I am looking for, I wonder if you can help me with a couple of improvements:

- The conditional formatting shading fields where leave is taken to not just show shading but "A" in cell (meaning: Annual - eventually we would be looking to include additional leave types such as maternity leave, long service leave etc)

- At the end of the table, to count number of days leave taken for the view period

- Leave dates exclude public holiday and weekends?

TT,

The conditional formatting shading fields where leave is taken to not just show shading but "A" in cell (meaning: Annual - eventually we would be looking to include additional leave types such as maternity leave, long service leave etc)- At the end of the table, to count number of days leave taken for the view period

I hope this is what you are looking for? See picture below.

Visualize-overlapping-date-ranges-part-2-version-TT.xlsx

Leave dates exclude public holiday and weekends?The count excludes weekend but not public holidays.

Hi Oscar!

Thats so amazing! however when I tweak your sample (eg. I slot in my employee IDs and dates, the "A" coding disappear?

I've set it up so that all values are in single fields (non-merged fields), seems like when I unmerge fields and double check formulas, the "A" coding part of the formula breaks?

I also put the list of leave to the right of the calendar (as i'd like to format this template so that other departments can use it regardless of number of employees they have (eg. 5 or 100)

Can you tell me if I'm doing something wrong?

Also employee listing in column A is static as managers want to see out of those under their care who is taking leave and who is not

TT

however when I tweak your sample (eg. I slot in my employee IDs and dates, the "A" coding disappear?Make sure the cell references point to the right cell ranges.

Hi Oscar,

This is a solution that I have been searching for! I followed your instructions and downloaded the document associated with the lesson and noticed in both that the Red Conditional formatting formula applied to cell range B5:AF17 yields an error stating that the formula is missing a parenthesis--) or (.

I've parsed out the formula in excel and double checked with no avail.

Am I missing a step here?

Resolved! We were able to parse out the formula this way

in any cell (for this we chose AI21) entered the following formula: =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)), SUMPRODUCT(($B$20:$B$33=B$4))>1, FALSE)

then set a conditional format to:

=IF( ( U26 =B$4)), SUMPRODUCT(($B$20:$B$33=B$4))>1, FALSE )

Everything worked.

April

I am happy you got it working.

I have the above shown Leave Matrix which shows the employees and their Leaves for the month/year. How can i Generate the records for them that you are showing as manual entries ?

Rehan Memo,

great question! See this article:

https://www.get-digital-help.com/2018/07/03/get-date-ranges-from-a-schedule/