Highlight date range on calendar

Hi,

I have the attached spreadsheet with 3 sheets - Issue, Hire and Calendar.

On the calendar I already have formula for the 'Issue' but I would also like to add the information from the Hire sheet to the same Calendar. From Hire sheet, I would to pick the Column K and highlight the start and end date on the calendar.

Thank you.

You can have a different colour for the start date and the end date of the hire period. To start with, you need to set up two named ranges in the Hire sheet. I've used:

Hire_start, referring to =Hire!$A$6:$A$36, and:

Hire_end, referring to =Hire!$F$6:$F$36

Then in the Calendar sheet you need to select all the cells with the dates in them (i.e. =$B$6:$H$6,$B$8:$H$8,$B$10:$H$10,$B$12:$H$12,$B$14:$H$14,$B$4:$H$4) and then you can apply the CF in one operation. With those cells selected (and with B4 as the Active cell), click on Conditional Formatting | New Rule | Use a formula... , then enter this formula in the dialogue box which pops up:

=ISNUMBER(MATCH(B4,Hire_start,0))

Click on the Format button | Fill tab and choose your colour for the start dates (e.g. yellow), then you can OK your way out. With the cells still selected, repeat and use this formula:

=ISNUMBER(MATCH(B4,Hire_end,0))

Format and Fill again, then choose your colour for the end dates (e.g. blue). When you exit the dialogue box Excel will automatically adjust the cell reference to suit all the other cells that had been selected.

Hope this helps.

Pete

Originally Posted by Pete_UK

You can have a different colour for the start date and the end date of the hire period. To start with, you need to set up two named ranges in the Hire sheet. I've used:

Hire_start, referring to =Hire!$A$6:$A$36, and:

Hire_end, referring to =Hire!$F$6:$F$36

Then in the Calendar sheet you need to select all the cells with the dates in them (i.e. =$B$6:$H$6,$B$8:$H$8,$B$10:$H$10,$B$12:$H$12,$B$14:$H$14,$B$4:$H$4) and then you can apply the CF in one operation. With those cells selected (and with B4 as the Active cell), click on Conditional Formatting | New Rule | Use a formula... , then enter this formula in the dialogue box which pops up:

=ISNUMBER(MATCH(B4,Hire_start,0))

Click on the Format button | Fill tab and choose your colour for the start dates (e.g. yellow), then you can OK your way out. With the cells still selected, repeat and use this formula:

=ISNUMBER(MATCH(B4,Hire_end,0))

Format and Fill again, then choose your colour for the end dates (e.g. blue). When you exit the dialogue box Excel will automatically adjust the cell reference to suit all the other cells that had been selected.

Hope this helps.

Pete

Hi Pete

Thank you for your email. I am clearly not doing it right and not sure how to add these formulas to the existing vlookups already on the calendar. If it's not too much of a trouble do you mind putting these formulas to the excel sheets I have uploaded.

Thank you again.

File attached, with everything set up for you. Note that I've changed the hire end date for your first hire, so you can see the effects more clearly. Change the Month and Year in the Calendar sheet to see others.

Hope this helps.

Pete

Hi Pete

I will have a go and try it, thank you.

Originally Posted by pamela16

Hi Pete

I will have a go and try it, thank you.

Hi Pete

One last thing, please can you explain how where the named ranges get pick up in the calendar sheet? As I tried to do the formula using CF but not sure how it will pick the named ranges.

Please can advise once more.

Thank you.

Hi Pete

Sorry I have figured it out.

Thank you.

Glad to hear it, and glad to help - thanks for the rep.

Pete

Good morning Pete

I know I have marked the post 'SOLVED' and if I possible please can I ask another thing. It's currently highlighted the start and finish date but can it be highlighted the dates from start date (in yellow) all the way until it finish on such a date (in blue). Also, if CF can do it put the customer name (hirer).

Thank you.

Hi Pamela,

There may be a problem in showing the complete date range, in that you show different machine numbers in your sample data and so it is possible that you could have overlapping date ranges for two different machines. I'm not clear how that should be handled. It sounds like you should have a Gantt-style chart to show the hire data appropriately, with one line devoted to each machine.

As for putting the hirer's name in there, CF cannot do that, so you would need to amend the formula that produces the day number.

Pete

Hello again, Pamela.

Further to this, I have amended the file from Post #4 to give you a continuous date range shown in yellow (with blue for the final date), as requested.

I have added two formulae in columns K and L of the Hire sheet, which basically work out the number of days hire for each record, and give a cumulative sum of those days. Where a Hire End Date is missing, e.g. in cells F8 and F15, it is assumed that the hire is just for one day. Note that there is a zero in cell L5, though this doesn't show. The formulae have been copied down to the bottom of your table, i.e. to row 36, as can be seen by the hyphens in column K, but you must copy these further if you add more data.

I've also used two other columns shown in blue, N and O, which generate individual dates for each hire record - the first finds the appropriate row number and the second gives an incremental date. I have copied these down to row 300, as the hyphens in column N indicate. Again, these will need to be copied down further if you add more data. I've also set up a new named range Hire_dates which will also need to be adjusted if you add more data. You will need many more records in this second table than you have in the first table.

In the Calendar sheet I have amended the CF rule for the yellow colour so that it now reads:

=ISNUMBER(MATCH(B4,Hire_dates,0))

rather than just looking at the dates in Hire_start. If you change the month in the Calendar sheet you will see the effects of these changes.

I do not think it will be very easy to show the name of the Hirer on the same row as the day number. Perhaps you could insert another row under each of the rows containing the dates, and have a formula in those new rows to get the Hirer's name.

Hope this helps.

Pete

Hello, yet again.

In this attachment I have set things up as I suggested at the end of my previous post, i.e. I have added a new row immediately beneath each of the rows that had dates, and have applied the conditional formatting to these new rows rather than on the date rows. This has enabled me to use a formula to pick up the name of the hirer (and also the machine number), and these are shown on the first day of the week for the hire period. There is still a bit of confusion where dates overlap (e.g. check out October and November 2020), but I think this is clearer and more in line with what you were asking for.

For each week, you now have a row for Hire details and another row for your Issues.

Hope this helps.

Pete

It looks fantastic! I will give this a try.

I wish I could give you a star again

Thank you

Glad to help.

You need to "spread the stars around" by giving one to someone else, as you can't give consecutive rep to the same person.

Have fun playing with the calendar, and let me know how you get on.

Pete

Hi Pete

It works like a treat, thank you. Sorry to be a pain but I have faced a new dilemma. I have duplicated all the 3 worksheets as we have different machines. But on the calendar it's picking up and highlighted the hire date for 39 machines as well as the new machine.

How can I resolve this?

On re-reading through your post, it seems that you have amended the file, so it would be a good idea for you to attach your latest version, as it might not be necessary to have a drop-down if you have individual calendars for each machine.

Pete

Hi Pete

Here the one I am trying to work on. I was trying to duplicate the 3 worksheets for each machine however, the calendar still picks the data from the original 309 sheet. So I have tried to use '309 Hire' to include other machine but it's not doing what it supposed to do. E.g. if 2 shredders were on hire on the same date (in red fonts) it's only showing one shredder & not both.

Thank you.

Hi Pamela,

I've only just seen your response, so apologies for the delay.

I won't be able to look at this for another week at least, as I'm going on holiday tomorrow for seven days, and although I'll take a tablet to check on the forum occasionally, it does not have Excel, so my contributions will be limited.

Pete

Hi Pete

Just have a look when you come back and have a chance.

Have a good holiday.

Good morning Pete

I hope you had a great holiday.

Just wondering if you managed to look at the spreadsheets again.

I appreciate all the help, thank you.

It was a great holiday, but I've not had chance to look at your file since I returned as I've been trying to get back on top of a week's worth of garden growth.

Do you only have 3 machines, or are you likely to want to add even more calendar sheets? Do you just want to show the dates of hire on the individual calendars, or the details from the Issues sheet as well?

Pete

Good morning Pete,

We currently have 4 machines and possibly add more calendars if get additional machine. What I am trying to achieve is to have a calendar per machine to show the issues and hire start and finish date.

Thank you.

So, for each machine that you have, you want to have an Issues sheet, a Hire sheet and a Calendar sheet which combines the data from the Issues and Hire sheets?

In the file that you had attached to Post #18 you show this layout for machines 313 and 309, so in addition to that you have 2 other machines? How are they referred to? I'm just trying to establish all the parameters before I get stuck in again.

I could arrange it so that you have just one Hire sheet and Issues sheet (on which you record the machine number number in a column) and then on a single Calendar sheet you have a drop-down to select the machine that you want to view and only the details that relate to that machine will be displayed. Let me know what you think.

Pete

Hi Pete

We currently have 5 and referred the rest 318, 353 & 452. Each machine will have their own 'Issue', 'Hire' & 'Calendar. And each calendar will have the combined Issue & Hire (Start -Finish).

I hope this makes sense.

Hi Pamela,

I've thought long and hard over the weekend to come up with the best way of achieving what you are trying to do, and have concluded that it will be easiest to use one file for each machine that you have, i.e. if you take the file from Post #12 you would have 5 copies of this, for machine number 309, 313 etc., (maybe incorporating the machine number in the filename) and so for machine 309 you would record the details in the Issues and Hire sheets only for that machine, and the Calendar sheet would thus only apply for that machine.

Hope this helps.

Pete

Hi Pete, I thought it will be a long shot and complicated how I wanted it. But thank you so much for all the help and time spent.

Good morning Pete

I hope you are well.

Apologies if I have to contact you direct instead the forum. I just thought you might remember the background since you helped my initially. The spreadsheet is working great but I've noticed that the hire period (start date) isn't highlighted in yellow mid May 2021 onwards. Previous months were okay. Do you mind changing the formula so it's not limited to certain date.

I have attached the sheet.

Thank you again.

The formulae in columns M and N of the Hire sheet need to be copied down further. The number at the bottom of column K tells you how many rows you need to capture all the data (385 currently), so you could copy M6 and N6 down to row 1000 (for example) to cater for more data being added.

Hope this helps.

Pete

Hi Ali

How do I remove the tag?

Hi Pete, I have tried to copy the formula down it don't seems to do anything

Originally Posted by pamela16

Hi Pete, I have tried to copy the formula down it don't seems to do anything

I had forgotten that the CF rule refers to a named range, so this also needs to be amended. Click on the Formulas tab in the ribbon, and then on Name Manager, and if you scroll down you will find the name Hire_dates which refers to the range:

=Hire!$N$6:$N$300

You need to change the 300 to 1000 (or wherever you have copied the formulae in M and N down to), then it should be good to go.

Let me know how you get on, as there may be other things about this file that I have forgotten about.

Pete

Hi Pete

I have changed the range but still not highlighted.

Good morning Pete

Sorry it's sorted now. Massive thanks again.

You could make that a dynamic named range which automatically adjusts to the amount of data that you have, so that you would not have to bother about it again.

You still have to ensure that the formulae in columns M and N are copied down far enough to cover your data as your file grows.

Pete

Originally Posted by Pete_UK

You could make that a dynamic named range which automatically adjusts to the amount of data that you have, so that you would not have to bother about it again.

You still have to ensure that the formulae in columns M and N are copied down far enough to cover your data as your file grows.

Pete

Hi Pete

And how will I make it dynamic?

Click on Name Manager in the Formulas tab and scroll down to the named range Hire_dates and change the Refers to box to this:

=Hire!$N$6:INDEX(Hire!$N:$N,MAX(Hire!$K:$K)+5)

The +5 is needed as the formula in column N starts on row 6.

When you click Close you will be asked if you want to accept those changes.

Don't forget to mark the thread as Solved again (using Thread Tools at the top of the screen) if you think it is.

Hope this helps.

Pete

Hi Pete

Can't thank you enough and hopefully this is the last how do I remove 'm/c 11592' on the calendar. I can't find where this info pulling out from?

That is the customer code (from column C of the Hire sheet), along with what was the Machine Number (in the early days you had several different machines in the one file), but is now Machine Hours (column B of the Hire sheet). It is only shown from the first day of the week where the hire is in place and on the final day (blue), and it helps clarify where one hire period ends and another one (maybe for another customer) begins, if there are more than one hire periods in the same month.

If you don't want to see them, you can just select the cells from B5 to H5 and press the Delete key. Do the same for rows 8, 11, 14, 17 and 20, and then you will just get coloured bands to show the hire periods.

Hope this helps.

Pete

P.S. Please note that you don't need to have the table in columns A to J of the Hire sheet extended down as far as the formulae in columns M and N. The table is effectively a summary which contains the start date and end date of each hire, but the formulae in M and N expand this for each day of the hire period, so one record in the main table may lead to 20 records (rows) in the second if the hire lasts 20 days.

Note further that there are two other named ranges Hire_end and Hire_start which are currently defined down to row 36 - you might want to extend these further if you intend to add more data (as shown in earlier posts).

Pete

Originally Posted by Pete_UK

That is the customer code (from column C of the Hire sheet), along with what was the Machine Number (in the early days you had several different machines in the one file), but is now Machine Hours (column B of the Hire sheet). It is only shown from the first day of the week where the hire is in place and on the final day (blue), and it helps clarify where one hire period ends and another one (maybe for another customer) begins, if there are more than one hire periods in the same month.

If you don't want to see them, you can just select the cells from B5 to H5 and press the Delete key. Do the same for rows 8, 11, 14, 17 and 20, and then you will just get coloured bands to show the hire periods.

Hope this helps.

Pete

P.S. Please note that you don't need to have the table in columns A to J of the Hire sheet extended down as far as the formulae in columns M and N. The table is effectively a summary which contains the start date and end date of each hire, but the formulae in M and N expand this for each day of the hire period, so one record in the main table may lead to 20 records (rows) in the second if the hire lasts 20 days.

Note further that there are two other named ranges Hire_end and Hire_start which are currently defined down to row 36 - you might want to extend these further if you intend to add more data (as shown in earlier posts).

Pete

Hi Pete

I am not quite entirely sure what I am looking. It's mind boggling

On calendar I just don't want to see the ref 'M/c xxxx'. I also tried to make the dates dynamic but when I did it just messed up the formula and the sheet.

Sorry I am pulling my hair out here

Hi Pamela,

I've been away in Buxton for a couple of days, so apologies for the delay in getting back to you. If you want to make those two named ranges dynamic, then you should use these formulae in the "Refers To" box for each one in Name Manager:

Hire_start: =Hire!$A$6:INDEX(Hire!$A:$A,COUNT(Hire!$A:$A)+5)

Hire_end: =Hire!$E$6:INDEX(Hire!$E:$E,COUNT(Hire!$A:$A)+5)

Note that both are using the count of the number of entries in column A (Start Date), as every record will have a start date but not necessarily an end date (for one day hires).

The formula currently in the yellow boxes on the Calendar sheet is this (in B5):

=IF(COUNTIF(Hire_dates,B4),INDEX(Hire!$C:$C,MATCH(B4,Hire!$A:$A))&" : M/c "&INDEX(Hire!$B:$B,MATCH(B4,Hire!$A:$A)),"")

If you don't want to see the reference to the (old) Machine number, then you can delete the part shown in red, and press Enter. Then you can copy that across to H5. You need to repeat this in turn for B8, B11, B14, B17 and B20, and copy across each time.

Hope this helps.

Pete

That's so great! Thank you ever so much!