![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
\n \n
Hey, Scripting Guy! How can I convert a number in Excel to a date; for example, how can I convert 40806 to 4\/8\/2006? Hey, JM. You probably didn\u2019t know this, but one of the Scripting Guys has a brother who\u2019s an actor. As every actor knows, nepotism is pretty prevalent in the acting world. Well, it\u2019s a good thing the Scripting Guys aren\u2019t actors, because we\u2019d never stoop to such favoritism. (There are other reasons it\u2019s a good thing that the Scripting Guys aren\u2019t actors, but that\u2019s another story.) We randomly select our questions to answer from the hundreds we receive every day. So, little brother, uh, JM, what was it you wanted to know? Oh yes, numbers-to-dates in Excel.<\/P>\n From your email, it sounds like you have a spreadsheet with a column of numbers where the first one or two digits (depending on the month) represent the month, the next two (always two) digits represent the day, and the last two or four (either 06 or 2006) represent the year. Something like this:<\/P> The problem we ran into is that the first date in our table, 40806, became 9\/20\/2011. Why? Well, the \u2026 interesting \u2026 thing about Excel is that when you try to turn a number into a date the program assumes that the number is a serial number representing the number of days that have occurred since Jan. 1, 1900. Turns out that 40806 days after Jan. 1, 1900, is Sept. 20, 2011. Back to the drawing board.<\/P>\n In the end, we decided to treat the number like a string: use the VBScript functions Left<\/B>, Right<\/B>, and Mid<\/B> to take the string apart, and then put everything back together with the appropriate symbols that will convince Excel we want a real date, not a serial number. Here\u2019s what we came up with:<\/P> intRow = 1\nintCol = 1\ni = 0<\/p>\n Do Until objExcel.Cells(intRow,intCol).Value = “”\n strDate = objExcel.Cells(intRow, intCol).Value\n i = Len(strDate)\n If i > 6 Then\n dtYear = Right(strDate, 4)\n i = i – 5\n Else\n dtYear = Right(strDate, 2)\n i = i – 3\n End If\n dtDay = Mid(strDate, i, 2)\n i = i – 1\n dtMonth = Left(strDate, i)\n newDate = dtMonth & “\/” & dtDay & “\/” & dtYear<\/p>\n objExcel.Cells(intRow, intCol).Value = newDate\n intRow = intRow + 1\nLoop<\/p>\n objExcel.Visible = True\n<\/PRE>\n The first thing we do is create an Excel object and open our spreadsheet. We then set up a Do loop to loop through the entire column of dates. The loop will continue until we reach an empty cell in the column; if you don\u2019t have a date in every column, you\u2019ll have to modify this script.<\/P>\n Next, we read in the date from the first cell, which happens to be row 1, column 1:<\/P> We then use the Len function to retrieve the number of characters in our date string. Because our string won\u2019t always be the same length we\u2019ll use this number as we read through the string to keep track of how many characters we have left.<\/P>\n In reading our string, we start from the end of the string. Given the format of the dates, the longest a string with a two-digit year can be is six characters, while the shortest a string can be with a four-digit year is seven characters. So we check to see whether the string is greater than six characters long; if it is, that must mean this particular value uses four digits for the year. Consequently, we use the Right function to read the last four characters and save them as the year. If the string is six characters or less, that can only mean one thing: it uses two digits to indicate the year. Therefore, we read in only the last two characters and save those two characters as the year:<\/P> As you can see, we also subtract the length of the date plus one from our remaining string length. We use that number as we continue to work backwards through the string to read the day (and yes, these are U.S. settings):<\/P> Here, we\u2019re using the Mid function to say that we want to retrieve two characters starting at position i, which, as we said, is the length of the string minus the length of the year, minus one more because we\u2019re retrieving two characters. In other words, our first string is five characters, so the value of i is 5. After reading the year, the value of i is 5 – 2 – 1, or 2. Starting at position 2, we take two characters, two characters which just happen to represent the day. We then subtract 1, and use that position to retrieve the month:<\/P> Here we use the Left function to retrieve the remaining number of characters (in this case, one) and save them as the month. Then we simply put the string together, separating the parts of the date with slashes (\/):<\/P> After that we put the new string into the cell we read the date from, increment the row number, and loop back to start on the next row in the column. The last thing we do is make Excel visible so you can see the results.<\/P>\n So that\u2019s it. Say hi to Mom for us. Um, say hi to your<\/I> Mom for us \u2026.<\/P>
— JM<\/P>![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/A> \n![\"Hey,](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/qanda\/exceldates.jpg\")
\n
Our first thought was to simply use the FormatDateTime<\/B> method. This method accepts a string and outputs that string in date format:<\/P>strNew = FormatDateTime(strOld)\n<\/PRE>\n
Set objExcel = CreateObject(“Excel.Application”)\nSet objWorkbook = objExcel.Workbooks.Open(“C:\\scripts\\test.xls”)<\/p>\n
strDate = objExcel.Cells(intRow, intCol).Value\n<\/PRE>\n
If i > 6 Then\n dtYear = Right(strDate, 4)\n i = i – 5\nElse\n dtYear = Right(strDate, 2)\n i = i – 3\nEnd If\n<\/PRE>\n
dtDay = Mid(strDate, i, 2)\ni = i – 1\n<\/PRE>\n
dtMonth = Left(strDate, i)\n<\/PRE>\n
newDate = dtMonth & “\/” & dtDay & “\/” & dtYear\n<\/PRE>\n
\n\n
![\"Top](\"http:\/\/www.microsoft.com\/technet\/mnplibrary\/templates\/MNP2.Common\/images\/arrow_px_up.gif\"){kind=icon}
<\/A>