Menu
I have data pulled from a database and stored in Stata .dta files. But when I read it into R using the foreign package, I get a date format unlike any I've seen. All of the other dates are "%m/%d/%Y" and import correctly.
I have searched the database's documentation, but there's no explanation for the odd date format for "DealActiveDate". The "facilitystartdate" date should be close to the "DealActiveDate", but not necessarily the same. Here are a few rows of these two columns.
facilitystartdate DealActiveDate
1 09/12/1987 874022400000
2 09/12/1987 874022400000
3 09/12/1987 874022400000
4 09/01/1987 873072000000
5 09/08/1987 873676800000
6 10/01/1987 875664000000
7 08/01/1987 870393600000
8 08/01/1987 870393600000
9 10/01/1987 875664000000
10 09/01/1987 873072000000
Please let me know if you have any idea how to convert "DealActiveDate" to a more conventional date. Thanks! (I'm not sure SO is the best venue, but I couln't think of any other options!)
545
Follow these steps: Select a blank cell and verify that its number format is General. Click the cell that contains the text-formatted date that you want to convert. Press ENTER, and the DATEVALUE function returns the serial number of the date that is represented by the text date.
Copy the dates that are in the cells that you want to convert. Copy the text and paste it into Notepad. Return to Excel and pick the cells into which you wish to paste the dates. Go to Home –> Number and pick the Text format with the cells chosen (from the drop down).
Looks like milliseconds since 1960-01-01:
as.POSIXct(874022400000/1000, origin="1960-01-01")
# [1] "1987-09-12 01:00:00 CDT"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
