Function to convert time values read in from excel into fractions of a day. Tries to deal with special cases. See https://tonyladson.wordpress.com/2016/08/08/data-cleaning-times/

Time_covert_excel.R

# Function to convert a time value to a fraction of a day
# 
# Intended to be used for time data from Excel
#
# x is the time
#
# if x is between 0 and 1 it is assumed to be a fraction of a day and returned
# if x is > 24 the decimal part is assumed to represent a fraction of a day and is returned
# # if x contains a colon it is assumed to be in the form HH:MM:SS and converted to 
# a fraction of a day.
#
# if to_afternoon is specified (as hours AM) times less than this are moved to the afternoon
# if zero_to_missing is set to true then zero times are set to missing
#
# if both to_afternoon and zero_to_missing are specified, the conversion is ambiguous but the function will
# set values that are zero  (within machine precision) to missing
# vales less than to_afternoon are moved to the afternoon.  So 00:00 will be set to missing 00:01 will be moved to 12:01

# Value
# Integer between 0 and 1 (fraction of a day)


Time_convert_excel <- function(x, to_afternoon = NULL, zero_to_missing = FALSE){
    
    # make output vector
    out <- suppressWarnings(as.numeric(x))
    
    # Helper functions to convert HH:MM:SS
    f <- function(x,y){
      as.numeric(x)/60 + as.numeric(y)
    }
    
    
    
    ind.char <- str_detect(x, ":") # index to elements that are in the form HH:MM 
    ind.char[is.na(ind.char)] <- FALSE
    
    
    # index to elements that are greater than 1
    ind.gt1  <- out > 1
    ind.gt1[is.na(ind.gt1)] <- FALSE
    
    
    # infill correct values
    out[ind.char] <- sapply(str_split(x[ind.char], ':'), function(x){ as.numeric( Reduce(f, rev(x)) )/24})
    out[ind.gt1] <- out[ind.gt1] %% 1 # retain fractional part
    
    # Set zero times to missing
    
    if(zero_to_missing) {
      ind.zero  <- abs(out - 0) < .Machine$double.eps^0.5 # Exactly zero and close to zero
      ind.zero[is.na(ind.zero)] <- FALSE
      out[ind.zero] = NA
    }
    
    # Move morning to afternoon
    
    if(!is.null(to_afternoon)) {
      if(to_afternoon < 0 | to_afternoon >= 12) stop('check arguement to_afternoon, ', to_afternoon)
      ind.arvo  <- out < to_afternoon/24
      ind.arvo[is.na(ind.arvo)] <- FALSE
      out[ind.arvo] = out[ind.arvo] + 12/24
      
      if(zero_to_missing) warning("Zero times have been set to missing.  Small time values have been moved to the afternoon")
    }

    
    
 
    # Check all values are between 0 and 1 or missing
    if(any(out > 1 | out < 0, na.rm = TRUE)) stop('Error in time conversion')
    
    # output
    out  
}