fix_messy_dates: Consistent formatting of a column of dates

In emilelatour/lamisc: Latour's Little Helpers

Consistent formatting of a column of dates

Description

It's come up a number of times, typically when reading in data from Excel, where a column of data that are supposed to be dates are loaded as a character column with a number of fomats. There's no one good solution to this but by combining functions lubridate::parse_date_time() and janitor::excel_numeric_to_date() I am able to fix this date issue when it comes up.

Usage

fix_messy_dates(x, formats = c("m/d/y"), date_system = "modern")

Arguments

x	A character or numeric vector of dates
formats	Date formats passed to lubridate::parse_date_time()
date_system	Date system for janitor::excel_numeric_to_date(), either "modern" or "mac pre-2011"

Details

From janitor::excel_numeric_to_date: Converts numbers like 42370 into date values like 2016-01-01. Defaults to the modern Excel date encoding system. However, Excel for Mac 2008 and earlier Mac versions of Excel used a different date system. To determine what platform to specify: if the date 2016-01-01 is represented by the number 42370 in your spreadsheet, it's the modern system. If it's 40908, it's the old Mac system.

Note that NA's can be coerced in the conversion. This is expected and the warning message has been suppressed purposely. For this reason, it is important to spot check the data after using this function.

Value

A date or vector of dates

Examples

library(dplyr)

bar <- tibble::tribble(
  ~date,                     ~comment,
  NA_character_,                      "",
  NA_character_,                      "",
  "12/21/2011",                       "",
  "2/1/2015",                         "",
  "5/17/12",                          "",
  "10/3/15",                          "",
  "42253",        "should be 2015-09-06",
  "42309",        "should be 2015-11-01",
  "5 /23/2015",                       "",
  "10/3 /2015",                       "",
  "unknown",    "Should turn into an NA",
  "10/5/10",      "should be Oct 5 2010",
  "2/1213",  "Can't handle this one yet",
  "21213",   "Can't handle this one yet"
)

bar %>%
  mutate(date2 = fix_messy_dates(date))

emilelatour/lamisc documentation built on May 20, 2024, 2:42 a.m.