Search code examples
exceldateformatexcel-2011

Get date value based on month name

The scenario I have is as follows:

  • Cell A1 - contains the name of the current month, e.g. "October"
  • Cell A2 - contains the value of the current year, e.g. "2014"
  • Cell A3 - contains the value of a given day, e.g "22"

I'd like to populate A3 with a formula that will give it the value 22 October 2014 and have this formatted as a date so I can perform comparisons and calculations in other cells - so along the lines of 22 + A1 + A2. I've tried using the CONCATENATE function but this doesn't let me format the cell as a date.

Is something like this even possible using the standard Excel functions?

Solution

  • You're looking for the DATEVALUE function. It can convert month names into a numerical date expression, which you can then format as a date (dd/mm/yyyy in the example below). 

    =DATEVALUE(A3 & " " & A1 & " " & A2)

    enter image description here

    As a bonus, this will also work if A1 contains short-form month names i.e. Jan, Feb, Mar, etc.

    I just did a bit of testing, which showed that you can also drop the " " space delimiters entirely:

    =DATEVALUE(A3&A1&A2)

    In fact, just using -- to force Excel to treat the concatenated string as a numerical value works as well!

    =--(A3&A1&A2)

    So far, my testing has not found any instance where -- doesn't work as well as DATEVALUE. Leaves me wondering what the point of DATEVALUE is.