Plot date ranges in a calendar
Table of Contents
1. Plot date ranges in a calendar
The image above demonstrates cells highlighted using a conditional formatting formula based on a table containing date ranges.
The calendar lets you choose year in cell D1 and the highlighted cells changes accordingly and immediately. The months are in column A and days of the days of the months are in row 2.
Check out this article Heat map yearly calendar if you want to highlight date ranges in yearly view.
This post Yet another excel calendar has a different layout yet still showing all dates in a year. There is also a monthly and daily view, however it won't allow you to add date ranges only single day events.
The following calendar Calendar – monthly view plots the events in a day instead of highlighting days.
I have used Conditional Formatting to
- highlight date ranges (green)
- highlight overlapping dates (red)
- not existing dates (black)
demonstrated in the image to the right.
The conditional formatting formula updates as you type new date ranges or edit an existing one in the table.
This gives you an overview of the date ranges that lets you easily spot any issues or errors.
How I created "invisible" dates in cell range B3:AF14
- Formula in B3: =DATE($D$1;ROWS($A$3:$A3);B$2) + ENTER.
$D$1 is an absolute reference to a cell containing a year. - Copy cell B3 and paste into cell range B3:AF14.
Excel tries to output the entire date but the cell width is too small. That is why it looks like it does, in the image above. - Select B3:AF14
- Press CTRL + 1
- Select "Number" tab
- Select "Custom"
- Type ,,, in "Type:" window
- Press with left mouse button on OK!
The steps above hide the dates in cell range B3:AF14.
How to highlight date ranges green
- Select B3:AF14.
- Press with left mouse button on "Home" tab
- Press with left mouse button on "Conditional formatting" button
- Press with left mouse button on "New Rule.."
- Select "Use a formula to determine which cells to format"
- Type in "Format values where this formula is true" window: =SUMPRODUCT((B3<=$I$19:$I$26)*(B3>=$E$19:$E$26))=1
- Press with left mouse button on "Format.." button
- Press with left mouse button on "Fill" tab
- Select a color (green)
- Press with left mouse button on OK!
- Press with left mouse button on OK!
- Press with left mouse button on OK!
The CF formula in step 6 above checks if the date is inside any of the date ranges, if exactly one date range is then the date cell is highlighted green.
How to highlight overlapping date ranges red
The steps here are identical to the steps above, however, the CF formula is different.
- Select B3:AF14.
- Press with left mouse button on "Home" tab
- Press with left mouse button on "Conditional formatting" button
- Press with left mouse button on "New Rule.."
- Select "Use a formula to determine which cells to format"
- Type in "Format values where this formula is true" window: =SUMPRODUCT((B3<=$I$19:$I$26)*(B3>=$E$19:$E$26))>1
- Press with left mouse button on "Format.." button
- Press with left mouse button on "Fill" tab
- Select a color (red)
- Press with left mouse button on OK!
- Press with left mouse button on OK!
- Press with left mouse button on OK!
The CF formula in step 6 above checks if the date is inside any of the date ranges, if more than one date ranges are then the date cell is highlighted red.
How to highlight not existing dates black
- Select B3:AF14.
- Press with left mouse button on "Home" tab
- Press with left mouse button on "Conditional formatting" button
- Press with left mouse button on "New Rule.."
- Select "Use a formula to determine which cells to format"
- Type in "Format values where this formula is true" window:=MONTH(B3)<>ROWS($B$3:$B3)
- Press with left mouse button on "Format.." button
- Press with left mouse button on "Fill" tab
- Select a color (black)
- Press with left mouse button on OK!
- Press with left mouse button on OK!
- Press with left mouse button on OK!
The formula in step 6 above checks that the month number is equal to the row number returned by the ROWS function. This makes sure that the date is in the correct month and not in the next month.
If it is in the next month then the cell is colored black. The following image shows the days of the months. For example, February 2010 has 28 days, the formula starts with 1 March in the next cell to the right of 28.
To hide that the cell is colored black.
How to reorder conditional formatting rules
- Press with left mouse button on "Home" tab
- Press with left mouse button on "Conditional formatting" button
- Press with left mouse button on "Manage Rules.."
- Reorder rules using arrow buttons
Rule (applied in the order shown)
- Black
- Red
- Green
Conditional Formatting formulas
You will find an explanation in greater detail here:
Highlight overlapping date ranges using conditional formatting
2. Plot date ranges in a calendar part 2
I will in this section demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names and corresponding dates based on the month and year selected by the user.
This is a new version of Visualize date ranges in a calendar. The workbook in this section lets you enter names and date ranges in an Excel defined Table. It allows you to add or delete names and date ranges without changing the cell references in the formulas.
Duplicate names are allowed, select year and month, days in that month are automatically calculated (row 4) and displayed accordingly.
Names whose date ranges are present in the selected month are displayed in cell range A5:A17. Dates are red if they overlap with another date range. This workbook contains no VBA code.
What you will learn in this section
- Create a dynamic monthly calendar.
- Create a calendar that highlights date ranges green and overlapping date ranges red.
- List names accordingly based on the corresponding date ranges.
- Build Conditional formatting formulas that highlight cells based on name and date.
- Build a formula that extracts names based on a year and month.
How this worksheet works
The animated image above shows when I select a month and the calendar instantly displays the appropriate names and dates based on the date ranges below the calendar.
Enter a value in cell B1 to change year, use the drop down list in cell B2 to select the month.
How I created this worksheet
Data list validation in cell B2
- Select cell B2
- Go to tab "Data"
- Press with left mouse button on "Data Validation" button
- Select List
- Type: Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec in Source:
- Press with left mouse button on 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
These steps shows you how to hide values in a cell by applying cell formatting, the value is still there but you can't see it.
- Select cell range D1:D2
- Press Ctrl + 1
- Go to "Number" tab
- Press with left mouse button on "Custom"
- Type ;;;
- Press OK
Calculate dates
Formula in cell B4:
Formula in cell C4:
Copy cell C4 and paste to cell range D4:AF4.
Create an Excel defined Table
- Select any cell in the data set that contains names and date ranges.
- Press CTRL + T to open the "Create Table" dialog box.
- Press with left mouse button on OK button.
Filter names in column A
Array formula in cell A5:
- Copy above array formula
- Paste in formula bar
- Press and hold Ctrl + Shift
- Press Enter
- Copy cell A5 and paste to cell range A6:A17
Explaining array formula in cell A5
Step 1 - Identify records that overlap selected month
The less than and greater than signs are logical oerpators that allows you to compare the date ranges saved in the Excel defined Table to the hidden dates in cell D1 and D2.
Cell D1 contains the first date in the selected month and cell D2 contains the last date of the selected dates.
(Table1[Start]<=$D$2)*(Table1[End]>=$D$1)
returns {1;0;0;0;1;1;0;0;0;0;0;0;1;0}
The position of each value matches the records in the Excel defined Table, for example, 1 indicates that the first record overlaps the select month June and year 2012.
0 (zero) tells us that the record does not overlap the selected year and month.
Step 2 - Convert boolean values to corresponding relative row number
The IF function lets you use a logical expression to determine which argument to return: value_if_true or value_if_false.
IF(logical_test, [value_if_true], [value_if_false])
IF((Table1[Start]<=$D$2)*(Table1[End]>=$D$1), MATCH(Table1[Start], Table1[Start], 0), "")
To create an array of numbers starting from 1 to the number of records in the Excel defined Table I use the MATCH and ROW functions.
The ROW function returns an array of numbers representing the row number for each record, however they don't start with 1 in most cases.
The MATCH function lets you convert the array to a sequence of numbers that start with 1.
returns
{1;"";"";"";5;6;"";"";"";"";"";"";13;""}.
The image above shows the array next to the Excel defined Table. The array contains the relative row number of each record that overlaps the selected year and month.
Step 3 - Extract k-th smallest row number
In order to extract a new value in each cell I use the SMALL function with a relative cell reference that changes automatically when I copy the cell to cells below.
SMALL(array, k)
SMALL(IF((Table1[Start]<=$D$2)*(Table1[End]>=$D$1), MATCH(Table1[Start], Table1[Start], 0), ""), ROW(A1))
becomes
SMALL({1;"";"";"";5;6;"";"";"";"";"";"";13;""}, 1)
and returns 1.
Step 4 - Return name
The INDEX function returns a value from a given cell range based on a row and column number.
INDEX(Table1[Name], SMALL(IF((Table1[Start]<=$D$2)*(Table1[End]>=$D$1), MATCH(Table1[Start], Table1[Start], 0), ""), ROW(A1)))
becomes
INDEX(Table1[Name], 1)
and returns the first value in column Name which is James Smith.
Step 5 - Remove errors
When values run out the formula returns an error, the IFERROR function removes the errors and returns a blank cell instead.
For example, in cell A9 the formula becomes:
IFERROR(INDEX(Table1[Name], SMALL(IF((Table1[Start]<=$D$2)*(Table1[End]>=$D$1), MATCH(Table1[Start], Table1[Start], 0), ""), ROW(A5))), "")
becomes
IFERROR(#NUM!, "")
and returns a blank.
Red conditional formatting formula
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
Calendar category
Table of Contents Excel monthly calendar - VBA Calendar Drop down lists Headers Calculating dates (formula) Conditional formatting Today Dates […]
Table of Contents Monthly calendar template Monthly calendar template 2 Calendar - monthly view - Excel 365 Calendar - monthly […]
This article demonstrates how to highlight given date ranges in a yearly calendar, this calendar allows you to change the […]
Excel categories
58 Responses to “Plot date ranges in a calendar”
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
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form
Hi.
So this is exactly what i am looking for to get my Staff Leave Calendar going except that my table (data sheet) is on another worksheet in the same workbook as the calendar. How can i amend the formula so that it works for me?
Lorne,
Conditional formatting can´t reference cells in other sheets. There is a workaround, use named ranges.
Hi,
thank you for the excellent article. I have only one question. Can I use the same method if the chart looks like this - https://www.freeimagehosting.net/d5b2k I want to add the months (in yellow) on top of the days and get rid of the red months.
I will appreciate any help. Thank you!
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.
[...] on Jul.27, 2012. Email This article to a Friend 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. [...]
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
This is really great thank you! It almost does exactly what I want/need.
The thing I'd love to be able to do is to start part way through one year, and end part way through the next - eg Start Oct 2014 and end the table at Sep 2015.
I've tried playing around with your sheet and I just cannot get it to do that.
For example how is your "invisible date" formula working out the month? It appears it can't be taking the letters "Jan" from A3, because if I simply change those to "Dec" then it still highlights the dates as though they are January.
How could I achieve what I want and have the table denoting the end of one year and the beginning of another?
Thanks again and all the best.
Hi,
I am using your formula, but instead of having the months downwards in colums I have an endless series.
It works, but for some reason it does not highlight the first day of some of the data range (two first ranges are not showing first date while third range works perfectly).
I have checked the invisible dates and they are correct. Do you have any idea as the reason why I am having this issue?
Cheers,
Jorunn
Hi again,
I figured it out. I used time in the date range (03.04.2015 06:00).
Do you know if there is a way wich makes you able to include the time without the effecting the vizualization?
Cheers
Jo,
Try this:
CF formula highlighting green cells:
=SUMPRODUCT((B3<=INT($I$20:$I$26))*(B3>=INT($E$20:$E$26)))=1
CF formula highlighting red cells:
=SUMPRODUCT((B3<=INT($I$20:$I$26))*(B3>=INT($E$20:$E$26)))>1
Many thanks for this, solved a problem I have been working on for a while. Is there any way of combining this conditional formating with another formula so that the cells are only formated if there is a certain name in the first column of the date ranges. I would like to format the dates with different colours for different people.
Many thanks
Hello,
Did you find a solution to this?
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?
Hi,
Thanks for the formula this is almost what I was looking for, but is there a way to show the name of the person that has the range in the calendar?
So in the calendar the days 1 though 5 of February will have the name Theodor in the cells and so on?
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 opened 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.
Hi Oscar,
I am using your visualize overlapping date range part 2 template and when i am trying to change the range from B33 to B41 the template stops working I mean it does not work. Please help.
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/
Is it possible to set conditional formatting to only highlight dates that overlap if there are more than 3 people for example that want vacation at the same time for overlapping dates?
Michelle,
Yes, it is possible.
You need to change two CF formulas.
Red:
=SUMPRODUCT((B3=$E$19:$E$26))>3
Green:
=(SUMPRODUCT((B3=$E$19:$E$26))<=3)*(SUMPRODUCT((B3=$E$19:$E$26))>=1)
Get the workbook:
Plot-date-ranges-in-a-worksheet-3-or-more.xlsx
Oscar,
I have the same issue as Simone from June 24 2013. I do not have unique start dates. I do have unique names. If I have James, Karl, and John with the same start date I receive three rows of James. Any solutions yet? I am using the TT version which is great. Thanks for your knowledge
Mark,
Thank you for telling me, I have made some changes to the TT workbook. Let me know if this is what you are looking for.
Visualize-overlapping-date-ranges-part-2-version-TT1.xlsx
Oscar,
You're a genius. Thank You!
Mark,
you are welcome!
This file is amazing, it is exactly what I am searching for. But can't understand why when I double press with left mouse button on any value (name or "A" in the calendar) data dissapier...Also I tried to add more rows and changed the formula to contain the rows that are being calculated but it doesnt seem to work..
I'm looking for a template that will allow me to create a calendar reflecting a year in columns (by week), and scheduled tasks by individuals. The tasks are shared, so there are multiple people working on a single task, and it should be filterable by task name (or number).
Can this be done?
Hi! For some reason no numbers will show in my row 4, and both "TRUE" and "FALSE" are showing. I am copying and pasting formulas, but I am using Google Sheets not Excel (which might be an issue).
I was also hoping to find out if I could add more columns as I need to show both a campaign, and a car number in certain locations based on the calendar year. It has been complex since there are many variables to show at once.
Hoping you can help!
Thank you.
This is great. I will preface this comment by stating I am attempting to use Google Sheets not Excel. Unfortunately nothing shows in row "4." Along with this both "FALSE" and "TRUE" are appearing in my cells. If possible I would love to include a location dropdown along with car numbers at each given time. It's been a struggle creating and/or finding something to show three variables at any given time throughout the year (Campaign, Car #, and Location). Thank you!
Hello,
This calendar is amazing. I'm trying to make one change. I would like different employees to show up as different colors. Any idea how I can do that? TIA.
Hi .... Excel Developer
This is A good aplication but there is a bugs i found..
If we Summit the Same date and same year in table, the result is not right, ..
Can you email to me, the file if you done to Fixed.
Thank you So much,...
From Indonesia
I need to highlight Saturdays and Sundays with a different color. Can someone assist here?
BUENAS NOCHES Y MUCHAS GRACIAS, ME AYUDO MUCHO ESTA INFORMACION SOBRE PERSONALIZAR LA PLANTILLA DE CALELDARIO.
GRACIAS,..!!
Google translate: GOOD NIGHT AND THANK YOU VERY MUCH, THIS INFORMATION ON CUSTOMIZING THE CALENDAR TEMPLATE HELPED ME A LOT. THANK YOU,..!!
In first section not ; but ,
Hi Oscar, this is so close to what I'm looking for! What I need is something that will plot the date range across the full year (not just 1 month at a time), is there an easy way to change the formula so it shows Jan1-Dec31 across the columns?
Good afternoon, this is amazing!
Is it possible to highlight the a particular date range (as above in green) but based on a name in another column?
Thanks a lot!
I want to add “Leave type” column in the table and apply different colours for each of these, how can i apply that changes in formatting condition formula? For example if sick leave blue, vacation green
Assistance request. I am using this solution to create a travel calendar for myself. this is working great to show the dates that I am traveling and also where I have overlapping travel dates. What I am trying to do is add a check to the conditional formatting that looks at my field Table1 Booked. So my field on my Table1 called Booked is yes, then I want to indicate that somehow. Maybe change the formatting color. I am using this version for conditional formatting. I am sure this is a simple add but I just can't figure it out.
=(INDEX(INDIRECT("Table1[Start]"), SMALL(IF($A5=INDIRECT("Table1[Name]"), MATCH(ROW(INDIRECT("Table1[Start]")), ROW(INDIRECT("Table1[Start]"))), ""), COUNTIF($A$5:$A5, $A5)))=B$4))