This can be made using the TEXT (TEXTE in the French version) function
of Excel, hence:
TEXT(M2;"HH:MM")
Changes the time into text, and it can be imported from R as wanted.
Le 01/04/2022 à 08:34, Patrick Giraudoux a écrit :
> 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.giraud...@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@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]]
______________________________________________
R-help@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.
