Create array of dates between two dates

I'm planning on using this "3rd cell" (we'll call it J19 for the purposes of this post) as a target for my calendar's conditional formatting. Each calendar cell will have a conditional format rule that works something like:

If the date in this cell is in the array held in J19 then fill green.

Oh and I fixed my keyboard problem, turns out it was just firefox, had to restart it.

Ok it turns out what I wanted to do isn't as simple as I thought it would be. I've attached the file I'm working on. It's a work in progress so there are various artifacts and formulas that don't work correctly, just ignore them.

This is for a dog daycare/boarding business. In the example file there are only two calendars but eventually every dog will have their own instance of the calendar. Anyway, when a user enters dates in the "Boarding In" and "Boarding Out" column, you'll notice it subtracts the dates, multiplies the difference by out boarding rate of $45 and then adds it to the dog's monthly total charges.

Right now I have it set up to turn green if the calendar cell contains the letter "b". A user has to first enter the dates, then go to each cell and enter the letter "b". What I need is for the calendar cells corresponding to those dates, and every date in between, to turn green automatically.

My plan was to make an array that held all the dates between W12 and X12 + W13 and X13 + ... W23 and X23. Store that array in a cell. Refer to that cell in the conditional formatting for every calendar cell.

To put it simply, (ignoring the cells used in my attachment) I'm looking for:

Is A1 equal to or between any of the values paired in B1:C10

and I need it to be simpler than:

Please Login or Register  to view this content.

Originally Posted by martindwilson

dont worry about simple,if it works use it

I don't think it does work though, I actually need more than 10 pairs, and the code eventually becomes too long to put into the conditional formatting rule. Also, I need this conditional format in 42 different cells. When I copy the formatting to each cell with relative references, they all point to the wrong cells and i have to manually edit each one. If I use absolute references, then I can't copy the calendar for another dog.

Martindwilson, thank you for your help. I am sorry to say I am completely lost looking at your suggestions though. It looks like it would be difficult to copy for every dog.

Ok, I think I figured out the best solution:

Please Login or Register  to view this content.

Now it's not perfect because even though "C12" updates to the correct reference when I copy/paste into each calendar cell, the other references in the formula become incorrect. I will have to manually edit them, but it's not as much work as it would have been with my last solution. Yes I could use "$W$12", etc., but then I could not copy the entire calendar down for another dog. Once I get the first calendar working, it'll be smooth sailing from then on.

Ugh, back to the drawing board. It only works when all the cells in W12:W14 and X12:X14 have dates. How could I modify this to not count the empty cells?

We have about 200 different dogs to track. And I'm still not sure how your solution works. I couldn't get your example to work when I entered dates into columns W and X. Would I have to copy that giant grid of 1's for each dog?

Regardless, I think I am close with this formula:

Please Login or Register  to view this content.

As long as I can figure out how to ignore the empty cells

There it is! Ty so much.

I was having a problem similar to yours, except with months. Is it possible to use this line of code to make it highlight the months instead of days?
I know this thread is from 2011, but the problem stil exsist in 2018