Date conversion gives NA

[Pavithra]

My date value is in this format
02:27:16 05-Mar-2019, Tue stored in Assigned date column

Am converting
srdetails1$Assigned On GMT<-as.POSIXct(srdetails1$Assigned On GMT, tz="", format = "%H:%M:%S %m/%d/%Y")
srdetails$Assigned On GMT

the value get converted as
43497.067407407405

Instead of showing a date and any function i use on this column for e.g day(ymd_hms() etc gives me "NA

How do i resolve this

[Yarnabrina]

Welcome to the community!

You're using the wrong format. Try the following:

as.POSIXct(x = "02:27:16 05-Mar-2019", format = "%H:%M:%S %d-%b-%Y")

Check the documentation of strptime for more details of the formats.

Hope this helps.

[Pavithra]

%d-%b-%Y"

Thanks for the response.. when i used the above format, it gives me NA as the result

[Yarnabrina]

That's surprising. It works for me, as illustrated below:

# what you did
as.POSIXct(x = "02:27:16 05-Mar-2019",
           tz = "",
           format = "%H:%M:%S %m/%d/%Y")
#> [1] NA
# the above expects date in this form: "02:27:16 03/05/2019"

# what I did
as.POSIXct(x = "02:27:16 05-Mar-2019",
           format = "%H:%M:%S %d-%b-%Y")
#> [1] "2019-03-05 02:27:16 UTC"
# this one expects date in this form: "02:27:16 05-Mar-2019"

Can you please provide a REPRoducible EXample of your problem of getting NA using my code?

In case you don't know how to make a reprex, here's a great link:

FAQ: How to do a minimal reproducible example ( reprex ) for beginners

A minimal reproducible example consists of the following items: A minimal dataset, necessary to reproduce the error The minimal runnable code necessary to reproduce the error, which can be run on the given dataset, and including the necessary information on the used packages. Let's quickly go over each one of this with an example: Minimal Dataset (Sample Data) You need to provide a dataframe that is small enough to be (reasonably) pasted on a post, but big enough to reproduce your issue. Let's say, as example, that you are working with the iris dataframe head(iris) #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> 1 5.1 3.5 1.4 0.2 se…

[Pavithra]

The above example works perfectly

Here is what am doing
srdetails=read_xlsx(file.choose())

Am choosing the data from excel, the date stored in excel in GMT is like 2/8/19 10:54 PM

but when i do this , it shows as numbers as below

head(srdetails$Assigned On GMT)
[1] "43504.954282407409" "43504.956446759257" "43504.943159722221" "43504.880810185183"
[5] "43504.846585648149" "43504.837731481479 ===> So the problem is here itslef?

typeof(srdetails$Assigned On GMT)
[1] "character"

srdetails$Assigned On GMT<-as.POSIXct(srdetails$Assigned On GMT, tz="", format = "%H:%M:%S %d-%b-%Y")

srdetails$`Assigned On GMT

output as all N/As

class(srdetails$Assigned On GMT) shows
[1] "POSIXct" "POSIXt

[andresrcs]

Your problem is with the way Excel represents date-time values, you could convert them this way.

df <- data.frame(stringsAsFactors = FALSE,
                 dates = c("43504.954282407409", "43504.956446759257",
                           "43504.943159722221", "43504.880810185183"))
as.POSIXct(as.numeric(df$dates)*86400, origin="1899-12-30",tz="GMT")
#> [1] "2019-02-08 22:54:10 GMT" "2019-02-08 22:57:17 GMT"
#> [3] "2019-02-08 22:38:09 GMT" "2019-02-08 21:08:22 GMT"

Also, you can specify the column class when reading the file with the col_types argument of read_xlsx()

[Pavithra]

That really helped, i used the col_types and it displays the dates properly.

When I used the POSIXct with the specific value it worked, but when the same value is in the excel and reading from excel was giving NA for me.

Thank you all