R convert number into time

Question

Someone gave me really bad data in Excel, where the date (such as July 1, 2015) is 20150701 and the time (such as 11:41:23) is 114123. There are over 50,000 rows of data and I need to convert these all into proper date and time objects. These aren't the number of seconds from any epoch, it is just the date or time without the dashes or the colons.

I imported them into a data frame and converted the dates using the ymd() function, but I can't find a function to do that for time, hms() gives me an error:

package(lubridate)
df <- readWorksheetFromFile(file="cktime2012.xls", sheet=1)
df$date <- ymd(df$date)
df$time <- hms(df$time)
# Warning message:
#  In .parse_hms(..., order = "HM", quiet = quiet) :
#   Some strings failed to parse

and I get a data frame that looks like this before running the last line. Once I run the last line, the TIMEIN column turns into all NA's:

DATEIN      TIMEIN  etc...
2012-02-01  200000  etc...
etc...

I need it to look like this for all 50,000 rows. I included POSIXct as a tag, because I don't know if there could be a way to use that to help convert:

DATEIN      TIMEIN      etc...
2012-02-01  20:00:00    etc...
etc...

Answer 1

If TIMEIN is always six characters (i.e., there's a leading zero for times before 10 AM), then you can do this:

df$TIMEIN = paste0(substr(df$TIMEIN,1,2),":",substr(df$TIMEIN,3,4),":", substr(df$TIMEIN,5,6))
df$TIMEIN = hms(df$TIMEIN)

Answer 2

You can try this too to get the specified time, but then you'd have to get rid of the date too.

> as.POSIXct("200000", format="%H%M%S")
[1] "2015-07-01 20:00:00 IST"

Edit- Okay, as.POSIXct() works on date and time. So, to merge the whole into one you can do something like this.

> as.POSIXct("20120201 200000", format="%Y%m%d %H%M%S")
[1] "2012-02-01 20:00:00 IST"