Create array of dates between two dates

[Nutshell]

If I have two cells, one with date 1-2-2011, and the other with 1-8-2011, how do I create a 3rd cell that holds an arraz of all the dates including and between those dates

sidenote, for some reason, various kezs are remapped when tzping in this forums text boxes. Zoull notice that I cant tzpe the letter that comes after X

[Ron Coderre]

If you describe what you want to do, perhaps we can provide a more customized solution to your issue.
• How many dates do you anticipate the range will contain?
• What do you plan on using the date range for?
• Can you post a simple example?

[Nutshell]

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.

[martindwilson]

why not just say the cell you want coloured is A1 coz it holds the date 2-2-2011

and b1=1-2-2011, and the other c1=1-8-2011
use
=and(a1>=b1,a1<=c1) as your conditional format

[DonkeyOte]

FWIW I agree with @mdw.

A cell can't hold an Array of values (only single), however, you "could" use a Defined Name to hold the Array but I can't see any good reason for doing so.

[Nutshell]

why not just say the cell you want coloured is A1 coz it holds the date 2-2-2011

and b1=1-2-2011, and the other c1=1-8-2011
use
=and(a1>=b1,a1<=c1) as your conditional format

I actually did this at first, but I thought it might start to get out of hand because if you look at the file I attached in my last post, you'll see that there are multiple spots for date ranges, so i would need a new rule for each one. I was looking for a more elegant solution with less clutter. Another reason was because I would have to edit each formula instead of just drag pasting the formatting. Absolute cell references would work, but not when I needed to go to the next calendar.

[Nutshell]

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.

[Nutshell]

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:

=OR(AND(B1<A1,A1<C1),AND(B2<A1,A1<C2),AND(B3<A1,A1<C3),AND(B4<A1,A1<C4),AND(B5<A1,A1<C5),AND(B6<A1,A1<C6),AND(B7<A1,A1<C7),AND(B8<A1,A1<C8),AND(B9<A1,A1<C9),AND(B10<A1,A1<C10))

[martindwilson]

well you could do it like this with named ranges refering to sheet 2 but maybe someone will code it for you in vba

[martindwilson]

dont worry about simple,if it works use it

[Nutshell]

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]

see my example but it perhaps would be better to put on same sheet away to the right then you wouldnt need named ranges and could simply copy the whole sheet
like this, you can copy sheet to any other and change/delete dates

[Nutshell]

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.

[Nutshell]

Ok, I think I figured out the best solution:

=SUMPRODUCT((W12:W14<=C12)*(C12<=X12:X14))>0

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.

[Nutshell]

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?

[martindwilson]

how many dogs is the maximum?
i.d slill go with what i proposed and extend it down to say 20 dogs you can always just delet any extras each month that you dont need

[Nutshell]

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:

=SUMPRODUCT((W12:W23<=C12)*(C12<=X12:X23))>0

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

[martindwilson]

assuming you will never put a end without an start
=sumproduct((c12>=$w$12:$w$23)*(c12<=$x$12:$x$23)*($w$12:$w$23<>""))>0
must admit i completely forgot to look at sum product!

[Nutshell]

There it is! Ty so much.

[MeZiMoX]

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

[FlameRetired]

Hi MeZiMoX. Welcome to the forum.

Please take the time to get acquainted with our forum rules ... particualarly

Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.


I look forward to seeing your new thread.