Highlight specific time ranges in a weekly schedule
In a previous post I created a simple weekly schedule with dynamic dates, in this post I am going to highlight hours using date and time ranges, demonstrated in the picture above Here are some random date and time ranges:
Try change date in cell F2 and see how any other week has no highlighted cells (hours).
How to highlight cells in a weekly schedule (conditional formatting)
Conditional formatting does not accept cell references outside current worksheet.
But there is a workaround.
Create a named range for each column and use the named ranges in a conditional formatting formula.
Create named ranges
- Select sheet "Time ranges"
- Select range B3:B5
- Type "Start" in name boxand press Enter
- Select range C3:C5
- Type "End" in name box and press Enter
Create conditional formatting (Excel 2007)
- Select sheet "Weekly schedule"
- Select cell range C6:I30
- Press with left mouse button on "Home" tab on the ribbon
- Press with left mouse button on "Conditional formatting"
- Press with left mouse button on "New Rule.."
- Press with left mouse button on "Use a formula to determine which cells to format"
- Type in "Format values where this formula is true" window:
=SUMPRODUCT((C6>=Start)*(C6<End)) - Press with left mouse button on "Format..." button
- Select "Fill" tab
- Select a color
- Press with left mouse button on Ok
- Press with left mouse button on Ok
- Press with left mouse button on Ok
Calendar category
This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events. What's […]
I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
Schedule category
In this post I am going to add one more function to the weekly schedule I built in a previous […]
Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]
I would like to share this simple weekly schedule I created. How to use weekly schedule Type any date in cell […]
Excel categories
22 Responses to “Highlight specific time ranges in a weekly schedule”
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.
Good Day Oscar,
I would like to get your help on the following:
I would like to create a calendar within excel that would be linked to a series fo recurring events.
Those events are happening every 15 days, and expected to occur for a period of up to 15 months (so 30 times in a row).
We have about 500 events, each named with an ID code (a01,a02,a03...)
We have a database that contains the id code, the date of first manipulation and the recurring dates after that, this file is updated daily, as some schedule might have been moved +- 1 day as it is meant to be flexible.
I have seen your example with the time. We do not require any time frame, just what is due on a specific date.
The difficulty being that a single day may have up to 50 events, hence 50 ID code.
I fear my explanations are vague or misconstructed. Kindly advise if you need me to clarify te statements above.
Hoping to get some informations pertaining to my concerns.
Cheers.
Cyril.
Excel v14.1.4 2011 mac.
Cyril,
Interesting question! What would the calendar look like, dates in a column and up to 50 events in a single row?
Thanks for commenting!
Hi Oscar,
Thanks for your interest in my query.
The database is organized as follows first row for the headings, data starting from row 2 till row 1205.
Column A is for the ID code, Column B for the first date (encoded by coordinator, Column C and forth (AF) for the due dates (formula), now for some instance we have events up to column CC (schedule is flexible and prone to change).
Due dates are linked and will update themselves automatically if any previous date is modified.
The Coordinator needs to see for a specific date what ID codes are due for manipulation.
Now the dates could very well be in A:A and the events (ID code) in B:BO or more if needed.
Maybe the date range (A:A) could be from 7 days prior to today() (row 2) till today()+30 days (row 39). Thus allowing the coordinator to see a whole month schedule, due events of manipulation, and monitor past week schedule (in case of delayed manipulation as I stated a possibility of schedule being moved). If this would work, the coordinator would need not update the summary but merely update the database.
Then we could use a conditional formatting with the formula $A2=TODAY() as condition in order to highlight the present day (faster to spot).
I hope my explanations are precise enough.
Please let me know if you need some more details.
Cyril.
Oscar,
I used the following:
=INDEX(DB!$B$2:$B$459, SMALL(IF(Calendar!$B2=DB!E$2:E$459, ROW(DB!E$2:E$459)-MIN(ROW(DB!E$2:E$459))+1, ""), COLUMN($A$1)))
But I do not know if this is the best solution for what I described, and being an array I need to command enter for each cell, if I highlight the whole range and command enter, it will just copy the formula and give the first ID code in all cells (which is not what we are looking for).
Just an addition to my previous post.
DB being the database with ID in column B and dates in column C to AF (just a sample)
Calendar being the sheet with the ranged calendar as follows:
columnB date (today()-7 to today()+30
columnC row 2 =INDEX(DB!$B$2:$B$459, SMALL(IF(Calendar!$B2=DB!D$2:D$459, ROW(DB!D$2:D$459)-MIN(ROW(DB!D$2:D$459))+1, ""), COLUMN($A$1)))
columnD row 2 =INDEX(DB!$B$2:$B$459, SMALL(IF(Calendar!$B2=DB!E$2:E$459, ROW(DB!E$2:E$459)-MIN(ROW(DB!E$2:E$459))+1, ""), COLUMN($A$1)))
ect
I eventually modified it as =IFERROR(INDEX(DB!$B$2:$B$459, SMALL(IF(Cd!$B2=DB!D$2:D$459, ROW(DB!D$2:D$459)-MIN(ROW(DB!D$2:D$459))+1, ""), ROW($A$1))),"")
But it wouldn't update itself whenever the DB file (database) would be updated.
Most likely i missed something.
Thanks to advise if my proceeds are correct or if a new direction (and formula) shall be used altogether.
Oscar,
Partially solved with a pivot AND merge function.
Still hoping to get some feedback if you have any idea on how to proceed to extract data from both rows and columns.
Posted in Mrexcel.com but so far you were the only one showing interest in my query... :)
Cheers,
Cyril.
Cyril,
Get the Excel *.xlsx file
cyril.xlsx
Oscar,
Yes, it is true,I realized my mistake after sending my reply myself (well that says a lot...) hehehe
the array is fine, command +return (mac version of ctrl+Shift+enter)
the trouble comes when duplicates are found.
The formula returns the first data and ignore all other successive entries sharing the same value (here the date).
Then what you explained in "How to return multiple values using vlookup in excel" seems to be a way to explore.
Thanks for the tip.
Cyril,
I changed my comment. Check out the attached file!
Get the Excel *.xlsx file
cyril.xlsx
Good evening Oscar,
So the range in the database (sheet1) must be the total range (all columns and rows containing info)...
I'll just change the range to accommodate more rows right?
I'll as well add drop down list for identifier (code for the coordinator)
Very sweet formula!
Kudos and many thanks for the time and sharing.
Cheers,
Cyril.
:)
Cyril,
So the range in the database (sheet1) must be the total range (all columns and rows containing info)...
I understand why you ask, the cell references didn´t have the same number of rows.
I'll just change the range to accommodate more rows right?
Yes, change every instance of Sheet1!$B$2:$AY$16.
very nice trick, should have thought of it, was tinkering with INDEX, AND, IF... hehehe
Thanks again Oscar.
Oscar,
I have a similar situation to the template you have provided but instead of dates I need to use different employees and then change the date at the top to the required date I want to look at. I cant seem to get it working.
All the data will be stored in a table on a separate sheet.
Any help would be much appreciated?
Many thanks
Wayne
Hi Oscar,
I very much like your approch to make shedule and i am trying to incorporate your fomula to ease my planning. What I need is to highlight dates in a yearly calendar. This is ok, however my input for the dates to highlight includes time (like your example above). The problem with having time in the input cells seems to be that it is not able to distinguis starting date that starts 00:01 or later (ex 05.06.2015 00:01 start highlighting at 06.06.2015). I have tried to change the format cell to am pm format etc without luck.
Do you know a way arond? If so I would very much apprechiate your help on this frustrating issue.
Many thanks
Jo
Jo
Read this:
https://www.get-digital-help.com/2010/06/14/visualize-date-ranges-in-a-calendar-in-excel/#comment-148154
Hi,
I want to highlight upcoming time in my excel.
For instance : i have 4 dask need to be done at different time (4,5,6,7 CET) so i want upcoming to be hightlight before 2 hours.
Hi. I'm trying really hard to get this to work with 15 minute intervals instead of hours but it's just not happening. Help!
Hi Oscar,
I have exactly the same weekly schedule with the days and the hours, but I want to function it differently. I want the only current hour cell in a current day be highlighted! Can you solve this?
I know that highlighting current day column can be with this formula(by applying it with cells' numbers of the example you posted)
=TEXT(TODAY(),"DDDD")=C$5
and the current hour row by
=HOUR($B6)=HOUR(NOW())
but what about highlighting the only cell that is intersected by current day column and current time row?
Juwaiber,
try this:
=(TEXT(TODAY(),"DDDD")=C$5)*(HOUR($B6)=HOUR(NOW()))
Exactly what I want! I really appreciate it
Thanks Oscar,
Juwaiber
Thank you very much, the templates are very helpful :)
Easy to understand.
All the best ahead,
Vivek
Hi Oscar. Thank you for your useful tutorials. Wanted to see if you have any advice for my problem. Im trying to track ads for a TV campaign. The goal is to record all events (app installs) during the time window of my my ad. The windows for each ad are 10 minutes. I have a column with date and time entries of app installs - a long list, in this format: 12/10/2021 04:10:00. I have another column of date and time entries for when my ad airs like this 17/09/2021 07:30:00. I would love to learn how to match/highlight the installs that occur during my time windows. For example, one time window would be 17/09/2021 07:30:00 (+10 minutes). Thank you