Date conversion gives NA

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

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.

1 Like

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

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:

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

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()

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

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.