CODEWITHSUNDEEP
exceldateformatexcel-2011
danw
danw

Reputation: 1648

Get date value based on month name

The scenario I have is as follows:

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?

Upvotes: 1

Views: 6968

Answers (3)

Jean-François Corbett
Jean-François Corbett

Reputation: 38500

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.

Upvotes: 3

barry houdini
barry houdini

Reputation: 46331

You can also use this formula

=(A1&A2)+A3-1

format result cell in required date format

Upvotes: 1

ttaaoossuu
ttaaoossuu

Reputation: 7884

Try this:

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

Upvotes: 2

Related Questions