Getting a year from a 4-digit number in Excel 2013

Question

I have a list with two columns Year and Month. The Year column is populated by 4 digit numbers (e.g. 1997,2006) while the Month column is populated by Strings (January, February, etc.).

I am trying to make a 3rd column which makes a date out of the Year and Month colum with the day date fixed at 15. So a row with 1997 and January becomes 15/01/1997 (in UK format).

I have used =DATE(YEAR(A2),MONTH(B2&1),DAY(15)) as a formula but this changes the date to 15/01/1905. I know that Excel uses a serial number format for dates but is there a way to turn a 4 digit number into a year-date?

Answer 1

Try the DATEVALUE function as,

=datevalue(B2&" 15, "&A2)

Months (e.g. January, February, etc) in column B and years (e.g. 1997, 2006, etc) in column A.

Format the column with the formula as one of the Date type number formats.