How to get correct date formats and types when outputting to Excel

[ijmoorejr]

I am attempting to output a date9. formatted date field in SAS to an Excel spreadsheet via ODS tagsets.excelxp and Proc Report. Once in Excel, I would like the data to be treated as a "date" type as opposed to "general" or "text", because I need to apply a date filter. The format of the dates in Excel needs to be m/d/yyyy.

The code is malfunctioning and shows the dates in Excel as 1/1/1960. Does anyone know how this code needs to be edited in order to meet the above requirements? Please help. Thanks.

data test;
input test_date date9.;
format test_date date9.;
datalines;
17FEB2019
18FEB2019
15JAN2019
16JAN2019
;
run;

proc print data=test; run;

ods tagsets.excelxp file="C:\Users\ABC123\Desktop\test.xls"
		style=sasdocPrinter 
		options(autofilter='all');


proc report data=test;
column test_date;
define test_date / 'test_date' center format=e8601dt. style(column) = [tagattr="type:DateTime format:m/d/yyyy"];
run;

ods tagsets.excelxp close;

[Kurt_Bremser · Accepted solution]

E8601DT is a datetime format; because of the date and datetime math in SAS (counts of days vs counts of seconds) you'll get timestamps on 1960-01-01.

To ISO-format dates, use the E8601DA10. or YYMMDDD10. formats.