Todays Dueling Excel Podcast #1303 with Mike Girvin and Bill Jelen looks at 'Unwinding Dates'. Suppose your dates are input like this: "1997-1999" How can you have each date year list out so that they now appear as sequential: "1997 1998 1999"?
Transcript of the video:
Bill: Hey, welcome back. It's another Dueling Excel podcast.
I’m Bill Jelen from MrExcel and we are joined by Mike Girvin from Excel is Fun.
This is episode 57: Unwind Dates.
All right, hey today's question, they have dates like this, 1997 and 1999 over here in column A and we want to convert that to a sequential list of dates.
I'm sure that Mike is going to use an amazing formula here but I am not.
Formulas make my head hurt and I'm going to just jump right out to VBA.
A few lines of code will do this and we say for each cell in selection, that allows me to select the range that contains those values.
StartYear is the left of that cell for 4, EndYear is the right of that cell for 4 and for some reason they left a blank column there that's why I start with my counter of 2 and then I have a simple little “For Next” loop.
If you've ever had any class in programming, you get this for i equals StartYear to EndYear.
Now each time through this loop “I” tells us which year we're looking at, all right.
So I know that I want to write the number for example 1997 in a cell.
What cell what I want to use well I'm going start from the current cell dot offset.
Offset says hey we're going to go some number of rows down some number of columns over.
Well no rows down in this case and then counter, Ctr that variable rows over and it's going to be equal to the year.
Sub SpillDates() For Each cell In Selection StartYear = Left(cell,4) EndYear = Right cTR = 2 For I = StartYear To EndYear Cell.Offset(0,Ctr) = i Ctr = Ctr + 1 Next i Next cell End Sub Now I want to bump counter up by 1 and so let's see in that first case what do we have?
We have from 1997 to 1999 it's going to go through this loop 3 times 1, 2, 3, 97, 98, 99 and then when it goes back to the next cell we'll get a new StartYear, new EndYear and reset counter to B2.
So let's just try this here.
I select those cells Alt+F8, run our spill dates macro and there we are; just fill it in as easy as that.
All right easier than Mike's formula.
Mike you're going to impress us all with his amazing formula let's see what you’ve got.
Mike: Thanks MrExcel; impressed.
That VBA impresses.
Simple and beautiful but I don't know VBA so I'm going to have to stick with a formula here.
All right, let's look at this text right and a year dash year.
Well just like MrExcel’s VBA I can do the Left function four and get the BeginYear and the Right function of the right four characters from the right and get the EndYear and then do something with that.
So let's start with the left.
I'm going to say hey I'm going to take the left and Ineed to lock this so when I go this direction it's locked on that so I'm going to hit F4 three times.
Ah but when I copy it down I don't want the row reference locked, I want that to move down to there so that'll work; comma 4.
=LEFT($A1,4) All right, let's just enter this and see something right off the bat; you see it's a line to the left that means it's text and that will be important later in our formula.
Now we want to, as we copy this in this direction as we go from 1997 to 1998 to 1999, so I'm going to add and this is a formula element that increments a single number as you copy it to the side.
I'm going to use the Columns function and I'm sitting in C1 so I'm going to type dollar sign C1 colon C1.
=LEFT($A1,4)+COLUMNS($C1:C1) Now column says hey how many columns are there from C to C?
There's one.
This is locked, this is not.
So that copies to the side.
That C will turn into a D and how many columns are there from C to D?
Two.
Copy this over here.
Ah there's two problems; one is it started one year too late and it didn't turn off.
It’s showing 2000 and 2001 and I only want it to go up to 1999 so I'm going to simply subtract one I just subtracted one there and then copy it over =LEFT($A1,4)+COLUMNS($C1:C1)-1 All right, that solves the problem of starting on the right year but now we need to turn it off.
We need a blank anytime the formula goes further that direction.
Well we can notice that we have our years; this is changing years as we go this direction, so we can use the IF function and compare this to RIGHT 4.
So I'm going to say if the right of this, the same F4 three times; 4, any time that there is less than.
So this left construction here, is given a successively bigger year(s).
As soon as we see the end year is less than this then what do we want?
That's the logical test, true or false.
I'm going to say comma double quotes because I want to show a double blank otherwise if that's false then we want to Ctrl+V. I just pasted that.
Now that ought to work.
Ctrl+Enter, double click and drag it over.
The Formula: =IF(RIGHT($A1,4)<LEFT($A1,4)+COLUMNS($C1:C1)-1,””, LEFT($A1,4)+COLUMNS($C1:C1)-1 Ooh but there's a problem and it has to do with the fact that text and numbers are considered different things.
So if I click here and run formula evaluator Alt+T+U+F, formula evaluator on the formula tab.
If I run this; enter, enter, enter, enter, enter, enter.
Right now you can see there's text and in double quotes which means that Excel thinks it's text and a regular number.
They can't be compared.
They are different things and so Excel doesn't know what to do.
No problem; we're just going to convert this and by the way you can hit F9 to prove to yourself.
Double quotes means that's text not a number.
I'm going to Ctrl+Z; we could simply add a 0.
Anytime you do an operation on text that's a number it'll convert it to a number.
That's why I by the way over here, the left originally gave us a text but as soon as we added these numbers here it converted it to a number.
=IF(RIGHT($A1,4)+0<LEFT($A1,4)+COLUMNS($C1:C1)-1,””, LEFT($A1,4)+COLUMNS($C1:C1)-1 Now we can highlight this and hit the F9 key and we can see we get a number.
So we'll get a number here and here and we can compare them and the formula will work.
Ctrl+Enter and drag it over and down and it looks like, sure enough we got one, two, three and it turned off after that.
1992 to 2000, sure enough all the way over there and obviously you'd copy this however far over you need.
One other interesting note; we converted this one right here to a number, so number compared to a number but we could have done this and I think I like the plus zero better but we could have done this ampersand double quote.
That means now this has been converted to text.
F9, Ctrl+Z and then this one right here is text F9 and it works.
=IF(RIGHT($A1,4)+0<LEFT($A1,4)+COLUMNS($C1:C1)-1&””,””, LEFT($A1,4)+COLUMNS($C1:C1)-1 All right, throw it back over to MrExcel.
Bill: Mike that was incredible.
I loved it.
I thought you were going have to use an array formula; even better than I thought you were going to do, so excellent.
Well I want to thank everyone for stopping by.
We’ll see you next time for Dueling Excel podcast from MrExcel and Excel is Fun.
I’m Bill Jelen from MrExcel and we are joined by Mike Girvin from Excel is Fun.
This is episode 57: Unwind Dates.
All right, hey today's question, they have dates like this, 1997 and 1999 over here in column A and we want to convert that to a sequential list of dates.
I'm sure that Mike is going to use an amazing formula here but I am not.
Formulas make my head hurt and I'm going to just jump right out to VBA.
A few lines of code will do this and we say for each cell in selection, that allows me to select the range that contains those values.
StartYear is the left of that cell for 4, EndYear is the right of that cell for 4 and for some reason they left a blank column there that's why I start with my counter of 2 and then I have a simple little “For Next” loop.
If you've ever had any class in programming, you get this for i equals StartYear to EndYear.
Now each time through this loop “I” tells us which year we're looking at, all right.
So I know that I want to write the number for example 1997 in a cell.
What cell what I want to use well I'm going start from the current cell dot offset.
Offset says hey we're going to go some number of rows down some number of columns over.
Well no rows down in this case and then counter, Ctr that variable rows over and it's going to be equal to the year.
Sub SpillDates() For Each cell In Selection StartYear = Left(cell,4) EndYear = Right cTR = 2 For I = StartYear To EndYear Cell.Offset(0,Ctr) = i Ctr = Ctr + 1 Next i Next cell End Sub Now I want to bump counter up by 1 and so let's see in that first case what do we have?
We have from 1997 to 1999 it's going to go through this loop 3 times 1, 2, 3, 97, 98, 99 and then when it goes back to the next cell we'll get a new StartYear, new EndYear and reset counter to B2.
So let's just try this here.
I select those cells Alt+F8, run our spill dates macro and there we are; just fill it in as easy as that.
All right easier than Mike's formula.
Mike you're going to impress us all with his amazing formula let's see what you’ve got.
Mike: Thanks MrExcel; impressed.
That VBA impresses.
Simple and beautiful but I don't know VBA so I'm going to have to stick with a formula here.
All right, let's look at this text right and a year dash year.
Well just like MrExcel’s VBA I can do the Left function four and get the BeginYear and the Right function of the right four characters from the right and get the EndYear and then do something with that.
So let's start with the left.
I'm going to say hey I'm going to take the left and Ineed to lock this so when I go this direction it's locked on that so I'm going to hit F4 three times.
Ah but when I copy it down I don't want the row reference locked, I want that to move down to there so that'll work; comma 4.
=LEFT($A1,4) All right, let's just enter this and see something right off the bat; you see it's a line to the left that means it's text and that will be important later in our formula.
Now we want to, as we copy this in this direction as we go from 1997 to 1998 to 1999, so I'm going to add and this is a formula element that increments a single number as you copy it to the side.
I'm going to use the Columns function and I'm sitting in C1 so I'm going to type dollar sign C1 colon C1.
=LEFT($A1,4)+COLUMNS($C1:C1) Now column says hey how many columns are there from C to C?
There's one.
This is locked, this is not.
So that copies to the side.
That C will turn into a D and how many columns are there from C to D?
Two.
Copy this over here.
Ah there's two problems; one is it started one year too late and it didn't turn off.
It’s showing 2000 and 2001 and I only want it to go up to 1999 so I'm going to simply subtract one I just subtracted one there and then copy it over =LEFT($A1,4)+COLUMNS($C1:C1)-1 All right, that solves the problem of starting on the right year but now we need to turn it off.
We need a blank anytime the formula goes further that direction.
Well we can notice that we have our years; this is changing years as we go this direction, so we can use the IF function and compare this to RIGHT 4.
So I'm going to say if the right of this, the same F4 three times; 4, any time that there is less than.
So this left construction here, is given a successively bigger year(s).
As soon as we see the end year is less than this then what do we want?
That's the logical test, true or false.
I'm going to say comma double quotes because I want to show a double blank otherwise if that's false then we want to Ctrl+V. I just pasted that.
Now that ought to work.
Ctrl+Enter, double click and drag it over.
The Formula: =IF(RIGHT($A1,4)<LEFT($A1,4)+COLUMNS($C1:C1)-1,””, LEFT($A1,4)+COLUMNS($C1:C1)-1 Ooh but there's a problem and it has to do with the fact that text and numbers are considered different things.
So if I click here and run formula evaluator Alt+T+U+F, formula evaluator on the formula tab.
If I run this; enter, enter, enter, enter, enter, enter.
Right now you can see there's text and in double quotes which means that Excel thinks it's text and a regular number.
They can't be compared.
They are different things and so Excel doesn't know what to do.
No problem; we're just going to convert this and by the way you can hit F9 to prove to yourself.
Double quotes means that's text not a number.
I'm going to Ctrl+Z; we could simply add a 0.
Anytime you do an operation on text that's a number it'll convert it to a number.
That's why I by the way over here, the left originally gave us a text but as soon as we added these numbers here it converted it to a number.
=IF(RIGHT($A1,4)+0<LEFT($A1,4)+COLUMNS($C1:C1)-1,””, LEFT($A1,4)+COLUMNS($C1:C1)-1 Now we can highlight this and hit the F9 key and we can see we get a number.
So we'll get a number here and here and we can compare them and the formula will work.
Ctrl+Enter and drag it over and down and it looks like, sure enough we got one, two, three and it turned off after that.
1992 to 2000, sure enough all the way over there and obviously you'd copy this however far over you need.
One other interesting note; we converted this one right here to a number, so number compared to a number but we could have done this and I think I like the plus zero better but we could have done this ampersand double quote.
That means now this has been converted to text.
F9, Ctrl+Z and then this one right here is text F9 and it works.
=IF(RIGHT($A1,4)+0<LEFT($A1,4)+COLUMNS($C1:C1)-1&””,””, LEFT($A1,4)+COLUMNS($C1:C1)-1 All right, throw it back over to MrExcel.
Bill: Mike that was incredible.
I loved it.
I thought you were going have to use an array formula; even better than I thought you were going to do, so excellent.
Well I want to thank everyone for stopping by.
We’ll see you next time for Dueling Excel podcast from MrExcel and Excel is Fun.
