Right, Left, and Middle functions for date handling

As I mentioned before, the right, left, and middle functions can be very useful for handling any sort of formatted data, and one of the simplest forms of formatted data is dates.

Everyone knows what a date looks like. If I were to give someone the date 07/12/11 they’d know just what I meant. Unfortunately, someone in the United States wouldn’t read it the same way as someone in Europe. In the US, it’s the month, then the day, then the year. Therefore 07/12/11 would be July 12, 2011. However, elsewhere in the world it’s assumed to be written day, then month, then year. By this interpretation 07/12/11 would be 7 December, 2011.

Because of this, a computer can’t be relied on to interpret dates correctly. While there are some functions designed to help the user manipulate dates, which I’ll discuss later, they can’t handle dates that are in the wrong format, or even dates that are too old. Human intuition can, however, and the right, left, and middle functions can be used to pull the information out and put it in a more useful format.

Assume that you had a list of dates in the DD/MM/YYYY format, and that they were in column you’d named “Dates”. In a second column, labeled “Day”, you’d want the formula =left(Dates,2). That would give you the left two characters, which of course happen to be the day. Then in a third column, labeled “Month”, you’d want the formula =mid(Dates,4,2). That would give you two characters, starting with character number 4, which of course would be the month. Lastly you’d want a fourth column, labeled “Year”, with the formula =right(Dates,4), which would give you the 4 rightmost characters, which are the year.

To recap: For a list of dates (named Dates) in DD/MM/YYYY format you’d want three formulas.

  1. =left(Dates,2) to get the DD, or day.
  2. =mid(Dates,4,2) to get the MM, or month.
  3. =right(Dates,4) to get the YYYY, or year.

An example is available at as Project 7.