1

Imagine that I have a spreadsheet with timestamps in the following form: dd/mm/yyyy hh:mm I am trying to make a new column in R converting that format into the Excel date format (i.e. the way Excel displays a serial number such as 39448 to represent 1/1/2008. I then want to then multiply this number by *1440 to get the serial date number in minutes.

Can anyone help me figuring out what the R code would look like to convert a dd/mm/yyy hh:mm column to Excel serial number*1440?

Cheers!

I have tried manipulating the data in Excel but I would like to be able to do this in R.

roc
  • 11
  • 1

1 Answers1

1

Similar info to @r2evans, but I find it nicer to work with difftime objects so you can change the units and origin date at will (because different versions of Excel use slightly different origin dates, yay!):

x <- '01/01/2008 00:00'
xtm <- as.numeric(as.POSIXct(x, format="%d/%m/%Y %H:%M", tz="UTC")) +
    difftime("1970-01-01", "1899-12-30", units="secs")
units(xtm) <- "days"
xtm
##Time difference of 39448 days
units(xtm) <- "mins"
xtm
##Time difference of 56805120 mins
thelatemail
  • 91,185
  • 12
  • 128
  • 188
  • Thanks for this explanation! I am wondering why you have "1899-12-30" instead of 1900-01-01? – roc Jun 27 '23 at 21:56
  • @roc - because that is what I needed to match the 39448 that Excel on Windows gave me for 01JAN2008. – thelatemail Jun 27 '23 at 22:00
  • Oh hmm, thank you! I am quite confused now. I followed along with @r2evans ' example but he has deleted it. So I am just trying to understand how this one works, my confusion just stemming from the fact that Excel origin date is number of days elapsed since Jan 1, 1900. :| – roc Jun 27 '23 at 22:04
  • @roc - that's not true - type `1900-01-01` into Excel, watch it automatically convert to a date, then do `Format Cells --> [Number]` and select 'General`'. On Windows that will be 1, not 0. – thelatemail Jun 27 '23 at 22:07
  • argh. Ok this is all super useful, thank you! I am baffled though why 1899-12-30 when "Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900." but it looks like it works!! It's just my own confusion about how the origin date works. – roc Jun 27 '23 at 22:35