How to create a date in excel

0
votes

I have excel file with 2 columns, year, and month. The year is given in YYYY format and the month the full name of the month like January, February.

Data sample,

column1, Column2
2014, January
2014, February
2018, March

As of now I have tried below code which is giving the error.

=date(A2, B2, 01)

and it is not returning any date values.

Requesting your assistance.

It will also work for me if it is solved by sas code.

3
excelsas
Convert the month name to number 1-12 before passing it to date. Alternatively, use =datevalue(b2 & " " & a2). – GSerg
Thanks @GSerg, taking the hint, I am using the first approach, converting the month name to number using datevalue() and then use Date(year, month(converted), day) to get the output. Now it is giving me the expected output. – Prafulla

3 Answers

0
votes

Try:

=DATEVALUE(CONCATENATE(B3," 1, ",A3))

This will concatenate the month, follow by a 1 and the year (ex: January 1, 2014). It will then convert this to a datevalue (dates are stored as numbers in Excel). If you format your cell as a date (MMMM YYYY) you will have the desired result.

0
votes

Try following

=DATEVALUE("1-" & LEFT(B1,3) & "-" & A1)
0
votes

If you were importing the data into SAS, here is a solution to create a date from month name and year variables.

data have;
 length month $ 15;
 infile datalines delimiter=','; 
 input year month $;
datalines;
2014, January
2014, February
2018, March
;
Run;

Data want(keep=date);
 Set Have;
 /*Length mon $3 yr $4 dt $15;*/
 /*Mon=substr(month,1,3);*/
 /*Yr=put(year,4.);*/
 /*Dt=cats('01',mon,yr);*/
 /*Date = input(dt,date9.);*/

 /* All in one line */
 Date = input(cats('01',substr(month,1,3),put(year,4.)),date9.);
 Format DATE mmddyy10.;
Run;