Get date value based on month name

Question

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?

Jean-François Corbett · Accepted Answer · 2015-09-29T10:58:20.563

3

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.

edited Sep 29 '15 at 10:58

answered Oct 22 '14 at 10:21

Jean-François Corbett

37,420
30
139
188

I think this approach will depend heavily on regional settings? – ttaaoossuuuu Oct 22 '14 at 11:59
Do you mean, if month names are entered in a different language than the regional settings? Yeah, that's true. Is it relevant? Depends on the OP's application. – Jean-François Corbett Oct 22 '14 at 12:05
Thanks both. For info, my application is only applicable to a single region therefore this solution will work. – danw Oct 22 '14 at 12:57

score 2 · Answer 2 · edited Oct 22 '14 at 12:09

2

Try this:

=DATE(A2,MATCH(A1,{"January","February","March","April","May","June", "July","August","September","October","November","December"},0),A3)

edited Oct 22 '14 at 12:09

Jean-François Corbett

37,420
30
139
188

answered Oct 22 '14 at 10:15

ttaaoossuuuu

7,786
3
28
58

score 1 · Answer 3 · answered Oct 22 '14 at 13:57

1

You can also use this formula

=(A1&A2)+A3-1

format result cell in required date format

answered Oct 22 '14 at 13:57

barry houdini

45,615
8
63
81

Get date value based on month name

3 Answers3