[R] Read ".xlsx" and convert date-column value into Dataframe

jim holtman jholtman at gmail.com
Wed Jul 22 18:22:47 CEST 2015


forgot the reply to all:

These are serial dates within EXCEL.  Here is a way of converting them:

> as.Date(c(42460, 42426), origin = '1899-12-30')
[1] "2016-03-31" "2016-02-26"



Jim Holtman
Data Munger Guru

What is the problem that you are trying to solve?
Tell me what you want to do, not how you want to do it.

On Wed, Jul 22, 2015 at 11:51 AM, Ivan Calandra <ivan.calandra at univ-reims.fr
> wrote:

> Hi Antony,
>
> I am not sure it could work easily with package xlsx. Try using the
> function read_excel() from package readxl. This function allows for Dates
> to be read.
>
> HTH,
> Ivan
>
> --
> Ivan Calandra, ATER
> University of Reims Champagne-Ardenne
> GEGENAA - EA 3795
> CREA - 2 esplanade Roland Garros
> 51100 Reims, France
> +33(0)3 26 77 36 89
> ivan.calandra at univ-reims.fr
> https://www.researchgate.net/profile/Ivan_Calandra
>
> Le 22/07/15 14:09, R_Antony a écrit :
>
>> Hi,
>>
>> Here i am having a ".xlsx" file and it contains various columns including
>> date-column[mm/dd/yy]-but it is not in the date format. I have to read
>> this
>> excel[.xlsx] file and need to get in dataframe. So i used "xlsx"-liabrary
>> and it was fine to read data. But the problem is, values in the date
>> column
>> is converting to some other value.
>>
>> for eg:-
>>
>> FF DATE
>> -----------
>> 3/31/2016
>> 2/26/2016
>> --
>> 1/2/2016
>>
>> [Values like "--" will come in the column to indicate that there is no
>> date
>> mentioned ]
>>
>> and i getting result like this,
>>
>> FF DATE
>> -----------
>> 42460
>> 42426
>>
>> 42125
>>
>> this is the code i am using for it,
>>
>> theData<-data.frame(read.xlsx2(InputFilePath, sheetIndex,
>> sheetName="Workflow_Report", startRow=3,colIndex=NULL, endRow=NULL,
>> as.data.frame=TRUE, header=TRUE))
>>
>> Aim :- I have to get actual "date-column" values in dataframe from xlsx
>> file.
>>
>> I tried many ways, Could someone please help ?
>>
>> Thanks in advance,
>> Antony.
>>
>>
>>
>>
>> --
>> View this message in context:
>> http://r.789695.n4.nabble.com/Read-xlsx-and-convert-date-column-value-into-Dataframe-tp4710192.html
>> Sent from the R help mailing list archive at Nabble.com.
>>
>> ______________________________________________
>> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide
>> http://www.R-project.org/posting-guide.html
>> and provide commented, minimal, self-contained, reproducible code.
>>
>>
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
> http://www.R-project.org/posting-guide.html
>
> and provide commented, minimal, self-contained, reproducible code.
>

	[[alternative HTML version deleted]]



More information about the R-help mailing list