Problem while converting into date format

[kuttan98]

Hi
I have a dataset which has the date variable. The time stamp is attached to the date- that's how it is appears in the excel file as shown in the first screenshot.. When I import into R, it changes into another format as shown in the second screenshot. I tried as.Date formula, but it is asking for origin. How can I convert this into proper date format?

data<-data %>%
    mutate(submission_date=as.Date(submission_date))

[parth77]

library(lubridate)

data<-data %>%
mutate(submission_date=ymd_hms(as.Date(submission_date)))

[FJCC]

I think as.Date() will truncate the fractional part of the day, losing the time. Try as.POSIXct(). If the time portions do not match what is in Excel, you may need to adjust the time zone.

#as.Date truncates the fractional part of the day
as.Date(44978.61,origin="1899-12-30")
#> [1] "2023-02-21"

#Convert days to seconds and use as.POSIXct()
as.POSIXct(44978.61*24*3600, origin = "1899-12-30", tz = "UTC")
#> [1] "2023-02-21 14:38:24 UTC"

^{Created on 2023-02-24 with reprex v2.0.2}

#For your data frame
data<-data %>%
  mutate(submission_date= as.POSIXct(submission_date*24*3600,
                                     origin = "1899-12-30", tz = "UTC"))

[kuttan98]

Thanks for the response. I wanted the time stamp to be removed. By giving the given origin it works in the way desired. is it that we always need to give the same origin?

[FJCC]

Excel files, as far as I know, always have an origin of 1899-12-30 if the dates are after 1900-02-28. I am not an expert on Excel versions, so there may be exceptions, but I have never seen one.