Format dates from serial number to date format

[Ben Hando]

I've never had issue with this before but am stuck with this dataset. Just trying to format the date variables included here. When I do so, the years come out as '2079, 2080, etc...' I have double checked in excel and they should correspond to 2019, 2020 and 2021.
Here are the data:

* Example generated by -dataex-. For more info, type help dataex
clear
input long(prep_start_date prep_end_date past_initial_date) float date_of_birth long test_date
43759 43812 43755 35360.5 43755
43759 43812 43755   37034 43755
43759 43812 43755   35043 43755
43759 43812 43755   36916 43755
43759 43812 43755   36530 43755
43759 43812 43755   33432 43755
43759 43812 43755   36433 43755
43759 43812 43755   35779 43755
43759 43812 43755   34161 43755
43759 43812 43755   36004 43755
43759 43812 43755   34721 43755
43759 43812 43755   33182 43755
43759 43812 43755   36501 43755
43759 43812 43755   34333 43755
43759 43812 43755   32120 43755
43759 43812 43755   36334 43755
43759 43812 43755   37161 43755
43759 43812 43755   34864 43755
43759 43812 43755 33571.5 43755
43759 43812 43755   36255 43755
43759 43812 43755   36980 43755
43759 43812 43755   35853 43755
43759 43812 43755   34374 43755
43759 43812 43755   36833 43755
43759 43812 43755   35326 43755
43759 43812 43755   36175 43755
43759 43812 43755   36450 43755
43759 43812 43755   36824 43755
43759 43812 43755   36888 43755
43759 43812 43755   34381 43755
43759 43812 43755   35965 43755
43759 43812 43755   36309 43755
43759 43812 43755   35069 43755
43759 43812 43755   36567 43755
43759 43812 43755   37086 43755
43759 43812 43755   36927 43755
43759 43812 43755   35222 43755
43759 43812 43755   36840 43755
43759 43812 43755   36849 43755
43759 43812 43755   35860 43755
43759 43812 43755   36127 43755
43759 43812 43755 35655.5 43755
43759 43812 43755   33519 43755
43759 43812 43755   37085 43755
43759 43812 43755 35251.5 43755
43759 43812 43755   32750 43755
43759 43812 43755   34411 43755
43759 43812 43755   36870 43755
43759 43812 43755 36724.5 43755
43759 43812 43755   32454 43755
end

Here is the code I am using:
format prep_start_date %tdnn/dd/ccyy

I also tried format prep_start_date %td

Thanks in advance and I apologize for the elementary question!

[Andrew Musau]

I would not attempt to fix this issue in Stata. Even if the values deviate in years by a constant value, values such as "36724.5" are very problematic. Go back to Excel, select all cells with dates and change the date format. Then re-import the data to Stata.

[Ben Hando]

Got it. Thanks, Andrew. Will do now

[Ali Atia]

You should cross-check this with the Excel results per Andrew's advice, but there's guidance in the documentation you might be interested in on converting Excel dates (30dec1899 on Windows/01jan1904 on Mac) to Stata dates (01jan1960) (https://www.stata.com/manuals/ddatet...ersoftware.pdf) (the below example assumes you're using Windows, and the results seem to confirm this):

foreach var of varlist *{
    generate stata`var' = `var' + td(30dec1899)
    format stata`var' %td
}
. list in 1/10,noobs

  +------------------------------------------------------------------------------------------------------------------+
  | prep_s~e   prep_e~e   past_i~e   date_o~h   test_d~e   s~rt_date   st~d_date   statapa~e   statada~h   statate~e |
  |------------------------------------------------------------------------------------------------------------------|
  |    43759      43812      43755    35360.5      43755   21oct2019   13dec2019   17oct2019   22oct1996   17oct2019 |
  |    43759      43812      43755      37034      43755   21oct2019   13dec2019   17oct2019   23may2001   17oct2019 |
  |    43759      43812      43755      35043      43755   21oct2019   13dec2019   17oct2019   10dec1995   17oct2019 |
  |    43759      43812      43755      36916      43755   21oct2019   13dec2019   17oct2019   25jan2001   17oct2019 |
  |    43759      43812      43755      36530      43755   21oct2019   13dec2019   17oct2019   05jan2000   17oct2019 |
  |------------------------------------------------------------------------------------------------------------------|
  |    43759      43812      43755      33432      43755   21oct2019   13dec2019   17oct2019   13jul1991   17oct2019 |
  |    43759      43812      43755      36433      43755   21oct2019   13dec2019   17oct2019   30sep1999   17oct2019 |
  |    43759      43812      43755      35779      43755   21oct2019   13dec2019   17oct2019   15dec1997   17oct2019 |
  |    43759      43812      43755      34161      43755   21oct2019   13dec2019   17oct2019   11jul1993   17oct2019 |
  |    43759      43812      43755      36004      43755   21oct2019   13dec2019   17oct2019   28jul1998   17oct2019 |
  +------------------------------------------------------------------------------------------------------------------+

[Ben Hando]

Thanks, Ali - I will check this out. I did go back and just format the dates in excel to dates and re-imported it and it seems fine now. Thanks again to you both.