Getting monthly date to display properly in excel output

[Michael Olu]

I am working on a dataset with daily dates and I have successfully computed monthly dates and formatted thus:

. gen monthly_notification_date = mofd(outbreak_notification_dt)
. format monthly_notification_date %tm

But after doing a tabulation or table1 command which automatically exports the result to excel, I cannot get the monthly date to display properly in the output window (or in excel) in a way that can be interpreted. I get numbers such as 728, 731, 739 etc

How can I get


* Example generated by -dataex-. For more info, type help dataex
clear
input byte hospitalized int outbreak_notification_dt float monthly_notification_date
0 22258 731
0 22519 739
0 22272 731
0 22252 731
0 22385 735
0 22281 732
0 22310 732
0 22276 731
0 22299 732
0 22280 731
0 22404 736
0 22458 737
0 22257 731
0 22257 731
0 22293 732
0 22545 740
0 22279 731
0 22272 731
0 22335 733
0 22310 732
0 22272 731
0 22254 731
0 22321 733
0 22552 740
0 22550 740
0 22272 731
0 22276 731
0 22272 731
0 22281 732
0 22321 733
0 22273 731
0 22305 732
0 22511 739
0 22510 739
0 22321 733
0 22265 731
0 22277 731
0 22299 732
0 22272 731
0 22256 731
0 22321 733
0 22511 739
0 22511 739
0 22265 731
0 22519 739
0 22305 732
0 22490 738
0 22276 731
0 22265 731
0 22340 734
0 22341 734
0 22552 740
0 22552 740
0 22541 740
0 22404 736
0 22287 732
0 22258 731
0 22290 732
0 22490 738
0 22262 731
0 22378 735
0 22287 732
0 22287 732

[William Lisowski]

This is an Excel problem, because Excel only has one type of date - a daily date. If you type jan 2022 into a cell in Excel, you will see that it is actually stored as 1/1/2022.



And then Excel applies a format to it that displays only the month and year. So you are going to have to use your outbreak_notification_dt and apply the suitable Excel format to display it as a month and year.

[Michael Olu]

Thanks for your response, William. This problem also exists in the Stata display window.

[William Lisowski]

Without having been shown the code that you have used to create a table and export it to Excel, it's hard to comment further on why Excel is not recognizing it as a date. Can you possibly create a simple reproducible example using starting with

* Example generated by -dataex-. For more info, type help dataex
clear
input byte hospitalized int outbreak_notification_dt float monthly_notification_date
0 22258 731
0 22519 739
0 22272 731
0 22252 731
0 22385 735
0 22281 732
0 22310 732
0 22276 731
0 22299 732
0 22280 731
0 22404 736
0 22458 737
0 22257 731
0 22257 731
0 22293 732
0 22545 740
0 22279 731
0 22272 731
0 22335 733
0 22310 732
0 22272 731
0 22254 731
0 22321 733
0 22552 740
0 22550 740
0 22272 731
0 22276 731
0 22272 731
0 22281 732
0 22321 733
0 22273 731
0 22305 732
0 22511 739
0 22510 739
0 22321 733
0 22265 731
0 22277 731
0 22299 732
0 22272 731
0 22256 731
0 22321 733
0 22511 739
0 22511 739
0 22265 731
0 22519 739
0 22305 732
0 22490 738
0 22276 731
0 22265 731
0 22340 734
0 22341 734
0 22552 740
0 22552 740
0 22541 740
0 22404 736
0 22287 732
0 22258 731
0 22290 732
0 22490 738
0 22262 731
0 22378 735
0 22287 732
0 22287 732
end
format %td outbreak_notification_dt
format %tm monthly_notification_date

and then adding the code to create the tabulation you want and export it to Excel, progress can be made.