R help - Apr 2022 - readxl, read_excel: how colon (:) is read ?

Probably (but not entirely sure), Excel is storing your text as a number of
days, so 13:38 is a little more than half a day. Open your spreadsheet in
excel and save those columns as text instead of times, that (should) fix
your issue.

On Fri, Apr 1, 2022, 02:12 Patrick Giraudoux <
patrick.giraudoux at univ-fcomte.fr> wrote:
> I have a unexpected behaviour reading times with colon from an Excel
> file, using the package readxl.
>
> In an Excel sheet, I have a column with times in hours:minutes, e.g:
>
> Arrival_time
> 13:39
> 13:51
>
> When read from R with readxl::read_excel, this gives a tibble column
> with full date by defaut being the last day of 1899. OK. Why not, I know
> that POSIX variables are starting in 1900 after R doc (however I wonder
> why here the defaut is one day before January 1, 1900
>
> > tmp$Arrival_time  [1] "1899-12-31 13:39:00 UTC"
"1899-12-31 13:51:00 UTC"
>
> Well, this is not exactly what I want to. I do not care about the year
> and the day... Therefore I decided to import this column as
"text"
> explicitely (in order to manage it within R then). And this is what I
> get now:
>
> >
>
read_excel("saisie_data_durban_rapaces_LPO.xlsx",sheet=2,col_types="text")
> > tmp$Arrival_time [1] "0.56875000000000009"
"0.57708333333333328"
>
> Can someone tell me what happens ?
>
> I would really appreciate to understand the trick...
>
>
>         [[alternative HTML version deleted]]
>
> ______________________________________________
> 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]]

Absolutely correct ! I checked in Excel and when I change the format to 
"text", then I get in Excel the same fractional numbers as those 
obtained importing text from R... Hence the issue comes from Excel 
itself. Will find a way to change this format to text in Excel without 
avoiding such conversion...
Thanks Andrew !

Le 01/04/2022 ? 08:26, Andrew Simmons a ?crit?:
> Probably (but not entirely sure), Excel is storing your text as a 
> number of days, so 13:38 is a little more than half a day. Open your 
> spreadsheet in excel and save those columns as text instead of times, 
> that (should) fix your issue.
>
> On Fri, Apr 1, 2022, 02:12 Patrick Giraudoux 
> <patrick.giraudoux at univ-fcomte.fr> wrote:
>
>     I have a unexpected behaviour reading times with colon from an Excel
>     file, using the package readxl.
>
>     In an Excel sheet, I have a column with times in hours:minutes, e.g:
>
>     Arrival_time
>     13:39
>     13:51
>
>     When read from R with readxl::read_excel, this gives a tibble column
>     with full date by defaut being the last day of 1899. OK. Why not,
>     I know
>     that POSIX variables are starting in 1900 after R doc (however I
>     wonder
>     why here the defaut is one day before January 1, 1900
>
>     > tmp$Arrival_time? [1] "1899-12-31 13:39:00 UTC"
"1899-12-31
>     13:51:00 UTC"
>
>     Well, this is not exactly what I want to. I do not care about the
>     year
>     and the day... Therefore I decided to import this column as
"text"
>     explicitely (in order to manage it within R then). And this is what I
>     get now:
>
>     >
>    
read_excel("saisie_data_durban_rapaces_LPO.xlsx",sheet=2,col_types="text")
>     > tmp$Arrival_time [1] "0.56875000000000009"
"0.57708333333333328"
>
>     Can someone tell me what happens ?
>
>     I would really appreciate to understand the trick...
>
>
>     ? ? ? ? [[alternative HTML version deleted]]
>
>     ______________________________________________
>     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
>     <http://www.R-project.org/posting-guide.html>
>     and provide commented, minimal, self-contained, reproducible code.
>
	[[alternative HTML version deleted]]