csv with dates in decimal format

Question

I've been provided a csv file with the date column as follows:

This is data I'll be using in highcharts, I can't seem to find any functionality to get these formats into YYYYMMDD

It appears like this data was made in R using something like the lubridate function but I have no way of confirming this.

Any ideas on the best way to get this data into YYYMMDD ?

Do you know what the _actual_ dates are? Ex: 1990.12466 = 07/18/1990 — Justin T., Oct 30 '18 at 18:09
Ok, in that case, I have to assume the first four digits represent the year, and the digits after the decimal represent the percentage through the year. Ex. 1990.12466 => year=1990; month=02; day=15. I get that by taking 0.12466 multiplied by 365 (45 days into the year), which lands on 2/15/1990. Do you think that is a safe assumption? — Justin T., Oct 30 '18 at 18:17
If so: [Converting date in Year.decimal form in R](https://stackoverflow.com/questions/26965699/converting-date-in-year-decimal-form-in-r) — Henrik, Oct 30 '18 at 18:21

score 1 · Answer 1 · answered Oct 30 '18 at 18:27

Something like this should work. First we linearly interpolate between the beginning of the year and the end of the year, and then we format the output into YYYYMMDD format as requested:

decimal_to_date = function(dt){
  yr = floor(dt)
  yr_begin = ISOdate(yr, 1, 1, 0, 0, 0)
  yr_end = ISOdate(yr+1, 1, 1, 0, 0, 0)
  interpolated_date = yr_begin + (yr_end - yr_begin) * (dt - yr)
  return(format(interpolated_date, '%Y%m%d'))
}

Then for example decimal_to_date(1990.12466) returns 19900215 for February 15, 1990. If you output the times as well as the dates, the time of day is always very near noon, which suggests something about the process that generated your data, although I'm not exactly sure what.

score 1 · Accepted Answer · answered Oct 30 '18 at 18:30

Assuming that the first four digits represent the year, and the digits after the decimal represent the percentage through the year, you can use the following formula to convert these values into a MS Excel date-time code: (with dates to be converted residing in column "A")

=DATE(MID(A1,1,4),1,1)+((A1-MID(A1,1,4))*(IF(OR(MOD(MID(A1,1,4),400)=0,AND(MOD(MID(A1,1,4),4)=0,MOD(MID(A1,1,4),100)<>0)),365, 366)))

Once you have these MS Excel date-time codes, you can format the date in Excel to whatever format you need (see Format a date the way you want).

score 0 · Answer 3 · answered Oct 30 '18 at 19:50

For what it's worth, here's a very slight variation on Michael Lugo's answer, which indeed does the trick. The ISOdate() function outputs a date-time object. Following code uses as.Date() which outputs date only. Following code also takes a brief shortcut in the calculation of the number of days in a calendar year - which you'll need for the interpolation. This shortcut requires loading a library, however, which the original answer does not.

library(lubridate)
decimals <- c(1990.12466,1990.20137,1990.2863,1990.36849,1990.45342,1990.53562,1990.62055,1990.70548,1990.78767,1990.8726,1990.95479,1991.03973)

decimal_to_date2 = function(dt){
  nDays <- yday(paste0(floor(dt),"-12-31"))
  day1 <- as.Date(paste0(floor(dt),"-01-01"))
  interpolated_date <- day1+(dt-floor(dt))*nDays
  return(format(interpolated_date, '%Y%m%d'))
}
decimal_to_date2(decimals)

Results of first answer and mine are identical.

csv with dates in decimal format

3 Answers3