Change from date and hour format to numeric format

Question

I am working in R and I need to change from a column in format

9/27/2011  3:33:00 PM 

to a value format. In Excel I can use the function value() but I do not know how to do it in R.

My data looks like this:

9/27/2011 15:33 a   1   5   9
9/27/2011 15:33 v   2   6   2
9/27/2011 15:34 c   3   7   1

Answer 1

This was useful for me:

> test=as.POSIXlt("09/13/2006", format="%m/%d/%Y")
> test
[1] "2006-09-13"
> 1900+test$year
[1] 2006
> test$yday
[1] 255
> test$yday/365
[1] 0.6986301
> 1900+test$year+test$yday/366
[1] 2006.697

You can use similar approaches if you need day numbers like in Excel.

Answer 2

To convert a string into R date format, use as.POSIXct - then you can coerce it to a numeric value using as.numeric:

> x <- as.POSIXct("9/27/2011  3:33:00 PM", format="%m/%d/%Y  %H:%M:%S %p")
> x
[1] "2011-09-27 03:33:00 BST"
> as.numeric(x)
[1] 1317090780

The value you get indicates the number of seconds since an arbitrary date, usually 1/1/1970. Note that this is different from Excel, where a date is stored as the number of days since an arbitrary date (1/1/1900 if my memory serves me well - I try not to use Excel any more.)

For more information, see ?DateTimeClasses