Forum Discussion
Highlight date ranges in calendar
How can I highlight the date range in Excel, I have 06 sheets in one workbook. I want to highlight the range of dates in the calendar so that I can see what dates are booked and the rest are available.
5 Replies
@Hans Vogelaar
I have attached the files, From Customer sheet, I want to Highlight the dates in CalandarActivate the Customer sheet.
Select the Arrival Date column of the table and name it (for example) Arrival.
Select the Departure Date column of the table and name it Departure.
Activate the Calender sheet.
Select B10.
Edit the conditional formatting rule.
Change the 'Applies to' range to
=$B$10:$H$15,$J$10:$P$15,$R$10:$X$15,$Z$10:$AF$15,$B$19:$H$24,$J$19:$P$24,$R$19:$X$24,$Z$19:$AF$24,$B$28:$H$33,$J$28:$P$33,$R$28:$X$33,$Z$28:$AF$33
Change the type to 'Use a formula to determine which cells to format', and use
=SUM((Arrival<=B10)*(Departure>=B10))
as formula.
In the attached workbook, the departure date was mostly before the arrival date; I corrected that.
- Thank you!
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
I have attached the files. From Customer sheet M9 and N10 is the range of date and I want to highlight all these dates in Excel Calendar
