Go to a cell in Excel and enter July 2008 as 07/08. Unfortunately, Excel will convert this to July 8 of the current year. In Episode 798, a bit of VBA code to convert that entry back into a month and year.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back the the MrExcel netcast.
I’m Bill Jelen. Today, great question sent in by Mary Lou. Mary Lou wants to enter a date but she just wants to enter the month and year. So, for example, for July 2008, she would enter 07/08. However, when she does that, Excel thinks that she's entering the month and the day so it automatically translates to July 8th. She said, you know, this is driving me crazy. How can I fix this?
Well, my initial reaction was we're going to create an event handler. So, I'm going to switch over to VBA, ALT+F11, and double click on the sheet to see the code. Left dropdown and choose WORKSHEET, and then the right dropdown, choose CHANGE, and we can write a little bit of code here that'll grab what she types and reformat it the correct way.
However, I wrote some code for it and it didn't work at all, and it ended up what happens is even though she types, for example, five characters, 07/08, immediately before this event handler runs, Excel is converting it to a full date. So, for example, if I would do MESSAGEBOX TARGET.VALUE & VBCR to go to a new line, & L E N of TARGET.VALUE -- let's just try this -- I'll come back here and I'll enter 07/08, what we'll see is that even though I typed 07/08, Microsoft thinks that I typed 7/8/2008, and the length is really 8.
So, when we start to write this code, we need to think about the fact that basically, as soon as I type 07/08, it's going to put a date in there, and so what I came up with is a little bit different code than where I thought I was going to go. Basically, I assume that we're going to be entering the dates in column D. So, if the TARGET.COLUMN is equal to 4, then we use the ISDATE function.
If what they just entered is a date, we launched into this bit of code where we say the month, that's a variable name, is = to we use the month function, and then the year is = to the day function. So, I typed 07/08. Microsoft thinks that the 08 is the day, and so I ask for the day of that. Now, of course, it's going to convert it just to an 8, so I have to add 2000 to it to get it up to 2008. I turn off the event handler and write a formula back into the cell that uses the DATE function.
Basically, I concatenate the year variable, the month variable, and the number one, and then convert that to values.
So, let's give it a try. We'll come here and, over in column D, I'll enter a date, so 07/08, and you saw it flash there briefly, but now when I go back to the cell, I have 7/1/2008.
Sometimes, Microsoft just has these default actions that it uses that will really drive us crazy but, luckily, with a tiny bit of VBA, maybe 10 lines of code there, we’re able to convert that to handle those situations and bring it around.
This one was a little bit more complicated because I assumed, when I typed in 5 characters, I would see the 5 characters in the variable called TARGET, but instead, even before this macro runs, he had already converted it to the wrong date. Still able to solve the problem.
So, thanks to Mary Lou for sending in the question and thanks to you for stopping by.
We'll see you next time for another netcast for MrExcel.
I’m Bill Jelen. Today, great question sent in by Mary Lou. Mary Lou wants to enter a date but she just wants to enter the month and year. So, for example, for July 2008, she would enter 07/08. However, when she does that, Excel thinks that she's entering the month and the day so it automatically translates to July 8th. She said, you know, this is driving me crazy. How can I fix this?
Well, my initial reaction was we're going to create an event handler. So, I'm going to switch over to VBA, ALT+F11, and double click on the sheet to see the code. Left dropdown and choose WORKSHEET, and then the right dropdown, choose CHANGE, and we can write a little bit of code here that'll grab what she types and reformat it the correct way.
However, I wrote some code for it and it didn't work at all, and it ended up what happens is even though she types, for example, five characters, 07/08, immediately before this event handler runs, Excel is converting it to a full date. So, for example, if I would do MESSAGEBOX TARGET.VALUE & VBCR to go to a new line, & L E N of TARGET.VALUE -- let's just try this -- I'll come back here and I'll enter 07/08, what we'll see is that even though I typed 07/08, Microsoft thinks that I typed 7/8/2008, and the length is really 8.
So, when we start to write this code, we need to think about the fact that basically, as soon as I type 07/08, it's going to put a date in there, and so what I came up with is a little bit different code than where I thought I was going to go. Basically, I assume that we're going to be entering the dates in column D. So, if the TARGET.COLUMN is equal to 4, then we use the ISDATE function.
If what they just entered is a date, we launched into this bit of code where we say the month, that's a variable name, is = to we use the month function, and then the year is = to the day function. So, I typed 07/08. Microsoft thinks that the 08 is the day, and so I ask for the day of that. Now, of course, it's going to convert it just to an 8, so I have to add 2000 to it to get it up to 2008. I turn off the event handler and write a formula back into the cell that uses the DATE function.
Basically, I concatenate the year variable, the month variable, and the number one, and then convert that to values.
So, let's give it a try. We'll come here and, over in column D, I'll enter a date, so 07/08, and you saw it flash there briefly, but now when I go back to the cell, I have 7/1/2008.
Sometimes, Microsoft just has these default actions that it uses that will really drive us crazy but, luckily, with a tiny bit of VBA, maybe 10 lines of code there, we’re able to convert that to handle those situations and bring it around.
This one was a little bit more complicated because I assumed, when I typed in 5 characters, I would see the 5 characters in the variable called TARGET, but instead, even before this macro runs, he had already converted it to the wrong date. Still able to solve the problem.
So, thanks to Mary Lou for sending in the question and thanks to you for stopping by.
We'll see you next time for another netcast for MrExcel.
