How to correct "misaligned" dates in R?

Question

I'm using a csv file from excel to import data and then I use it in rCharts. The dates are imported as 39873, 39904, etc and to convert them I use

tickFormat = "#!function(d){return d3.time.format('%b %Y')(new Date(d*60*60*24*1000))}!#"

The problem is the command shows jan-2076, feb-2076, etc instead of jan-2006, feb-2006, etc

Any ideas on how to substract 70 years to the dates?

Best regards MV

EDIT: full code:

p2 <- nPlot(tasa ~ periodo2, group = 'indicador', data = data, type = 'lineChart', id = 'chart')
p2$xAxis(axisLabel = 'fecha', rotateLabels = -30,
         tickFormat = "#!function(d){return d3.time.format('%b %Y')(new Date(d*60*60*24*1000))}!#")
p2$yAxis(axisLabel = '%')
p2$chart(tooltipContent = "#! function(key, x, y){
        return key + '<p>' + y + '%' + ' en ' + x + '</p>'} !#", 
        margin = list(left = 80, bottom = 80),
        color = c('#ff3232','#4332ff'))
#p2$set(width = 800, height = 500)
p2

Can you show us your sample data ? – Jd Baba Nov 03 '14 at 21:12 — Jd Baba, Nov 03 '14 at 21:12
you're right... how fool from myself – pachadotdev Nov 04 '14 at 04:09 — pachadotdev, Nov 04 '14 at 04:09

score 0 · Answer 1 · answered Nov 07 '14 at 15:38

I've found a solution after reading a lot

windows and mac calendars are different, so windows starts from 01-01-1900 and mac from 01-01-1904 in R this will verify the difference

#conversion excel windows
as.Date(37000, origin = "1904-01-01") # 1998-07-05
#conversion excel mac
as.Date(34519, origin = "1904-01-01") # 1998-07-05

then, if I have an excel windows file in mac I have to substract 2481 to every date

and the chart axis format goes fine with this:

c1 <- nPlot(tasa ~ fecha3, 
            group = "indicador", 
            data = data2, 
            type = "lineWithFocusChart")
c1$yAxis(axisLabel = "%", width = 40)
c1$xAxis(axisLabel = "Fecha", rotateLabels=-30,
         tickFormat = "#!function(d){return d3.time.format('%b %Y')(new Date(d*60*60*24*1000))}!#")
c1$x2Axis(rotateLabels=-0, tickFormat = "#!function(d){return d3.time.format('%Y')(new Date(d*60*60*24*1000))}!#")
c1$chart(tooltipContent = "#! function(key, x, y){
        return '<p>' + key + '</p>' + '<p>'+ y + '%' + ' en ' + x + '</p>'
        } !#",
         color = c('blue','red'), margin = list(left = 100, bottom = 100))
c1

There is no "mac calendar" or "windows calendar". Excel on the Mac used a particular date as its base and Excel on Windows (written years later, but designed to emulate the choice of the then dominant spreadsheet Lotus 1-2-3) used a different date. You are noticing the effects of exporting dates from Excel as integer values. This is NOT an R problem which uses POSIX date conventions and has a default origin of "1970-01-01" (which is why your dates are shifted 70 years.) — IRTFM, Apr 01 '16 at 00:15

How to correct "misaligned" dates in R?

1 Answers1