Plot date ranges in a calendar part 2
I will in this article 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 article 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 article
- 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)
becomes
({41079; 41109; 41162; 41197; 41071; 40995; 41096; 41138; 41213; 40976; 40925; 41113; 41066; 41142}<=41090)*({41150; 41112; 41245; 41221; 41071; 41074; 41168; 41175; 41257; 41029; 40993; 41123; 41132; 41220}>=41061)
becomes
{TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE}*{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE}
and 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), "")
becomes
IF({1;0;0;0;1;1;0;0;0;0;0;0;1;0}, MATCH(ROW(Table1[Start]), ROW(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.
IF({1;0;0;0;1;1;0;0;0;0;0;0;1;0}, MATCH(ROW(Table1[Start]), ROW(Table1[Start]), 0), "")
becomes
IF({1;0;0;0;1;1;0;0;0;0;0;0;1;0}, MATCH({3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16}, {3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16}, 0), "")
becomes
IF({1;0;0;0;1;1;0;0;0;0;0;0;1;0}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14}, "")
and 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;""}, 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
This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events. What's […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
In a previous post I created a simple weekly schedule with dynamic dates, in this post I am going to […]
Templates category
This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events. What's […]
Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
Excel categories
39 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
Paste image link to your comment.
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 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.
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/
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
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?
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