How Can I Convert a Number to a Date in Excel?

ScriptingGuy1

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?

— JM

Hey, JM. You probably didn’t know this, but one of the Scripting Guys has a brother who’s an actor. As every actor knows, nepotism is pretty prevalent in the acting world. Well, it’s a good thing the Scripting Guys aren’t actors, because we’d never stoop to such favoritism. (There are other reasons it’s a good thing that the Scripting Guys aren’t actors, but that’s 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.

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:

Our first thought was to simply use the FormatDateTime method. This method accepts a string and outputs that string in date format:

strNew = FormatDateTime(strOld)

The problem we ran into is that the first date in our table, 40806, became 9/20/2011. Why? Well, the … interesting … 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.

In the end, we decided to treat the number like a string: use the VBScript functions Left, Right, and Mid 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’s what we came up with:

Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\scripts\test.xls”)

intRow = 1
intCol = 1
i = 0
Do Until objExcel.Cells(intRow,intCol).Value = “”
    strDate = objExcel.Cells(intRow, intCol).Value
    i = Len(strDate)
    If i > 6 Then
        dtYear = Right(strDate, 4)
        i = i – 5
    Else
        dtYear = Right(strDate, 2)
        i = i – 3
    End If
    dtDay = Mid(strDate, i, 2)
    i = i – 1
    dtMonth = Left(strDate, i)
    newDate = dtMonth & “/” & dtDay & “/” & dtYear
    objExcel.Cells(intRow, intCol).Value = newDate
    intRow = intRow + 1
Loop
objExcel.Visible = True

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’t have a date in every column, you’ll have to modify this script.

Next, we read in the date from the first cell, which happens to be row 1, column 1:

strDate = objExcel.Cells(intRow, intCol).Value

We then use the Len function to retrieve the number of characters in our date string. Because our string won’t always be the same length we’ll use this number as we read through the string to keep track of how many characters we have left.

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:

If i > 6 Then
    dtYear = Right(strDate, 4)
    i = i – 5
Else
    dtYear = Right(strDate, 2)
    i = i – 3
End If

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):

dtDay = Mid(strDate, i, 2)
i = i – 1

Here, we’re 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’re 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:

dtMonth = Left(strDate, i)

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 (/):

newDate = dtMonth & “/” & dtDay & “/” & dtYear

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.

So that’s it. Say hi to Mom for us. Um, say hi to your Mom for us ….

Top of page

ScriptingGuy1

Follow