Given a start date and an end date, how do you produce a list of all the day numbers? For example, between Feb 28 and March 5, you would want the list 29,1,2,3,4,5.
Mike and Bill show several different ways to solve this question.
Mike and Bill show several different ways to solve this question.
Transcript of the video:
Hey, welcome back it's time for another dueling Excel podcast.
I'm Bill Jelen from MrExcel will be joined by Mike Girvin from Excel Is Fun.
This is our episode 153, Return the Day Number Within 2 dates.
All right this one seems tricky to me, Mike says, he has a couple different ways to do it on money, but sure I have one way to do it as their way for Excel to return the day numbers within 2 dates.
For example, between these two dates return seven, eight, nine, ten.
All right! Why, the one trick I have that takes two dates and start date and end date and coerces an array out of that.
I uses the indirect of the first date join together with a colon and the second day you know, these dates are actually stored as serial numbers you know somewhere from the 40,000 range.
So, this is saying hey, we're going to point to these rows from the first row to the second row and then I have to wrap that in the ROW function. ROW function and when I accept that formula with an Enter and then press F2, F9 you see this is returning 4 different numbers and those are actually the serial numbers for the dates, press Escape.
So, then I can ask for the day of all of that and again we get just the first one, but if i press F2,F9, it is returning the whole array.
So, let me copy that formula to the clipboard Control+C and we'll come over here select several cells type the formula and Control+Shift+Enter.
Nope, nope, no it's not gonna work.
Well, if there was some way, that I could wrap this in CONCATENATE, but CONCATENATE can't deal with something like that and I don't have the more funk add in.
So, I can't do MCONCAT.
All right! Let's, let's try this maybe it's that they're returning the dates and a vertical fashion.
So, we'll type the formula again Control+Shift+Enter there we go seven, eight, nine, ten, but I have to select more cells then I actually need and if this date would change, and I didn't select enough dates that I'm not going to see enough.
So, you know, I don't even know why I'm trying with a formula here.
Mike, I'm sure has a couple of different formulas.
I'm just going to go with VBA here's the start date, the end date, start date, end date, start date, end date.
We're going from row 8 to row 10, Alt+F11, I wrote this code we're going to look from r = 8 To 10, figure out the start date from column 2 the end date from column 3 initializing the counter this is where we're going to write the first answer to For d equals start date to end date.
The cells in this row and wherever the counter is.
So, it's going to start at four is equal to the day it's going to loop through and get each day and then we're moving the counter over.
So, let's just run that and we'll come back to Excel and there we go seven, eight, nine, ten, twenty first, there's 17 and if it actually reps your new month it starts over again at one.
All right! Mike, I'm curious to see what you have I'm sure that you have something, let's take a look.
Bill: Thanks MrExcel.
VBA that's beautiful and I still don't know how to write VBA and this too, I love it a formula element to create an array of days from a start and end.
That's a beautiful array formula there.
I guess, I'm just gonna keep it simple here and do a non array formula.
Hey, I'm going to start out by calculating the number of day.
So, I'll take the end date minus the begin date and I need to include that start date.
So I'm going to add one back in.
So, I got 4, I need a formula here that will calculating the first four cells, but show nothing when it goes down and change dynamically.
So, I'll simply use if and inside of the logical test I'm going to use a formula number incrementer.
So, I'm in F5, I am going to type F dollar sign 5 to lock that.
first five and then F5 notice that second five a row is not locked that's an expandable range.
so as I copy it down Rose will count the rows 1 2 3 4 or 5 i'm going to ask any time that formula number incrementer is greater than the number of days f4 that means i'm past row for like down here but then i need to show for value of true nothing and the syntax for nothing is double-quote double-quote that's a null text string otherwise i'm simply going to run this formula using the day i'm going to start with the start date and I'm going to add to it this formula number incrementer so it'll give me one two three four five now that'll give me 7 plus one to start so that's not what i want so i'll subtract one right off the bat close parentheses control enter and copy it down ah so it looks like it's working let's test it 9 / 21 control enter it's working their control Z let's put a bigger date to start off because that's how you're filling out this little farm ah so the minus shows nothing which is good but wait a second when we put 10 / 2 32 days man I wish I could get an extra day in a month so that for me that's not going to work but no problem I'm simply going to take this little bit here adding 1 2 3 s it copies down control x and then put it next to the serial number now the day since this is a serial number with one two three four five being added the day we'll get it right ctrl enter double click and send it down now let's take a look at this in chapter 8 of the control shift enter book I talk about this formula element and a bunch of other number incrementing formula elements now one thing that I try to stay away from is row of a one although it's much easier to type out then the dollar sign and F's and all that controls see when I structurally change the spreadsheet we can get into trouble so watch this oh yeah that's nice it's much shorter faster to type out and it will work however there are some situations where it will not work that cell a1 boom it's being referred to notice here's our formulas that references outside of this range notice this is not looking outside of any of our inputs and our formula range so the only time this is ever going to get deleted as if I delete any of this which means I don't want the whole setup but if I come over here and delete it will give us a reference error if you delete the row the column you'll also get reference errors and if you insert this is a structural change we do often insert boom will be off by one control-z alright so there's my take on it throwback to mr. excel hey all right Mike that is really cool when you said you were going to do something that was not an array formula I was like oh my gosh but I'm glad they were able to bring in a rate formula thing from the control shift enter book back and I'm guilty of using row of a one because I'm all about it's just shorter but i would certainly run into trouble if i did any of these four things make that my list of things not to do so a great question i want to thank everyone for stopping by Oh see you next week for another dueling excel podcast from mr. excel and excel is fun
I'm Bill Jelen from MrExcel will be joined by Mike Girvin from Excel Is Fun.
This is our episode 153, Return the Day Number Within 2 dates.
All right this one seems tricky to me, Mike says, he has a couple different ways to do it on money, but sure I have one way to do it as their way for Excel to return the day numbers within 2 dates.
For example, between these two dates return seven, eight, nine, ten.
All right! Why, the one trick I have that takes two dates and start date and end date and coerces an array out of that.
I uses the indirect of the first date join together with a colon and the second day you know, these dates are actually stored as serial numbers you know somewhere from the 40,000 range.
So, this is saying hey, we're going to point to these rows from the first row to the second row and then I have to wrap that in the ROW function. ROW function and when I accept that formula with an Enter and then press F2, F9 you see this is returning 4 different numbers and those are actually the serial numbers for the dates, press Escape.
So, then I can ask for the day of all of that and again we get just the first one, but if i press F2,F9, it is returning the whole array.
So, let me copy that formula to the clipboard Control+C and we'll come over here select several cells type the formula and Control+Shift+Enter.
Nope, nope, no it's not gonna work.
Well, if there was some way, that I could wrap this in CONCATENATE, but CONCATENATE can't deal with something like that and I don't have the more funk add in.
So, I can't do MCONCAT.
All right! Let's, let's try this maybe it's that they're returning the dates and a vertical fashion.
So, we'll type the formula again Control+Shift+Enter there we go seven, eight, nine, ten, but I have to select more cells then I actually need and if this date would change, and I didn't select enough dates that I'm not going to see enough.
So, you know, I don't even know why I'm trying with a formula here.
Mike, I'm sure has a couple of different formulas.
I'm just going to go with VBA here's the start date, the end date, start date, end date, start date, end date.
We're going from row 8 to row 10, Alt+F11, I wrote this code we're going to look from r = 8 To 10, figure out the start date from column 2 the end date from column 3 initializing the counter this is where we're going to write the first answer to For d equals start date to end date.
The cells in this row and wherever the counter is.
So, it's going to start at four is equal to the day it's going to loop through and get each day and then we're moving the counter over.
So, let's just run that and we'll come back to Excel and there we go seven, eight, nine, ten, twenty first, there's 17 and if it actually reps your new month it starts over again at one.
All right! Mike, I'm curious to see what you have I'm sure that you have something, let's take a look.
Bill: Thanks MrExcel.
VBA that's beautiful and I still don't know how to write VBA and this too, I love it a formula element to create an array of days from a start and end.
That's a beautiful array formula there.
I guess, I'm just gonna keep it simple here and do a non array formula.
Hey, I'm going to start out by calculating the number of day.
So, I'll take the end date minus the begin date and I need to include that start date.
So I'm going to add one back in.
So, I got 4, I need a formula here that will calculating the first four cells, but show nothing when it goes down and change dynamically.
So, I'll simply use if and inside of the logical test I'm going to use a formula number incrementer.
So, I'm in F5, I am going to type F dollar sign 5 to lock that.
first five and then F5 notice that second five a row is not locked that's an expandable range.
so as I copy it down Rose will count the rows 1 2 3 4 or 5 i'm going to ask any time that formula number incrementer is greater than the number of days f4 that means i'm past row for like down here but then i need to show for value of true nothing and the syntax for nothing is double-quote double-quote that's a null text string otherwise i'm simply going to run this formula using the day i'm going to start with the start date and I'm going to add to it this formula number incrementer so it'll give me one two three four five now that'll give me 7 plus one to start so that's not what i want so i'll subtract one right off the bat close parentheses control enter and copy it down ah so it looks like it's working let's test it 9 / 21 control enter it's working their control Z let's put a bigger date to start off because that's how you're filling out this little farm ah so the minus shows nothing which is good but wait a second when we put 10 / 2 32 days man I wish I could get an extra day in a month so that for me that's not going to work but no problem I'm simply going to take this little bit here adding 1 2 3 s it copies down control x and then put it next to the serial number now the day since this is a serial number with one two three four five being added the day we'll get it right ctrl enter double click and send it down now let's take a look at this in chapter 8 of the control shift enter book I talk about this formula element and a bunch of other number incrementing formula elements now one thing that I try to stay away from is row of a one although it's much easier to type out then the dollar sign and F's and all that controls see when I structurally change the spreadsheet we can get into trouble so watch this oh yeah that's nice it's much shorter faster to type out and it will work however there are some situations where it will not work that cell a1 boom it's being referred to notice here's our formulas that references outside of this range notice this is not looking outside of any of our inputs and our formula range so the only time this is ever going to get deleted as if I delete any of this which means I don't want the whole setup but if I come over here and delete it will give us a reference error if you delete the row the column you'll also get reference errors and if you insert this is a structural change we do often insert boom will be off by one control-z alright so there's my take on it throwback to mr. excel hey all right Mike that is really cool when you said you were going to do something that was not an array formula I was like oh my gosh but I'm glad they were able to bring in a rate formula thing from the control shift enter book back and I'm guilty of using row of a one because I'm all about it's just shorter but i would certainly run into trouble if i did any of these four things make that my list of things not to do so a great question i want to thank everyone for stopping by Oh see you next week for another dueling excel podcast from mr. excel and excel is fun
