• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date range

S

Simo22

New Member
Hi everyone
I have no idea what formula I can use to look up a range of start and end dates which I can then use in conditional format to highlight the corresponding dates on a chart.
The dates are on a separate sheet. I have attached a file showing what I am hoping to achieve.

Any help would be greatly appreciated.
 

Attachments

  • Screenshot 2023-04-08 at 19.05.35.png
    Screenshot 2023-04-08 at 19.05.35.png
    263.4 KB · Views: 7
A workbook would be better than a picture of one.
There is no reference to vehicles in your data, which is needed to know which row to highlight.
 
Thank you for your feedback, this is my first time on a forum so I'm a bit of a novice. I've attached the file as suggested. Hope you can help.
 

Attachments

  • Les Holland Quotation calc Main copy.xlsm
    95.6 KB · Views: 3
Simo22
There are some challenges with Your ... dates ... which were texts.
Could You check something this sample?
Instructions:
Select Private Hire -sheet
and
Select Private Hire Chart -sheet
 

Attachments

  • Les Holland Quotation calc Main copy.xlsb
    33.9 KB · Views: 3
It's pretty much impossible to do this with your dates in columns B & C of the Private Hire sheet being text rather than real dates (numbers).

In the attached, I have Power Query converting those dates to real dates and if there's more than one day between Date of Journey and Rtn Date it splits that row into multiple rows, one row per day. Then a pivot table of that data is at cell B21 of the Private Hire Chart sheet.
Notes:
  • You have many unallocated coaches; what do you want to show for them? Currently they're showing alongside (blank).
  • You have 2022 in cell C245 instead of 2023
  • Currently it's not showing some dates at the top because there's no data to show - if you want to see these dates, a further tweak is necessary.
  • Instead of hiding columns using row 1 of your sheet and a bit of macro code, you can filter the dates in the pivot using the drop down in cell C21:

83801
 

Attachments

  • Chandoo52432Les Holland Quotation calc Main copy.xlsx
    112.6 KB · Views: 6
Last edited:
Hi p45cal.
I never thought about using a pivot table to solve this problem, what you have done is great, many thanks.
With regard blanks, I can I get them not to show until an allocation has been made?
 
You must log in or register to reply here.
Back
Top