convert_dates: Convert Excel dates to consistent date formatting

In MikkoVihtakari/MarineDatabase: Export data to Norwegian Polar Institute's marine database

Description

Converts messy nonstandarized Excel dates to consistent date formatting.

Usage

convert_dates(
  dt,
  excel_file = NULL,
  file_ext = NULL,
  add_time = 0,
  date_origin = "1899-12-30",
  output_format = "iso8601"
)

Arguments

dt	Data.frame for which the date conversions should be made. The date column has to be named as date.
excel_file	path to the Excel file where the dates originate from. Can be left empty, if the date conversion should be done for other type of files (for example .csv or .txt).
file_ext	Extension of the data file. Can be left empty, if dt originates from another type file than Excel sheet.
add_time	Hours to be added to the ISO 8601 dates. See Details.
date_origin	The origin for recorded dates in the Excel sheet in "YYYY-MM-DD" format. See Details.
output_format	Character string specifying in which format the date information should be returned. Options: "iso8601" (default) returns the date column as a character string in ISO 8601 standard, "POSIXct" returns the column in UTC time format, and "as.Date" returns the date column in Date format ignoring hours, minutes and seconds.
date_col	Not implemented yet. Name of the column, which contains dates to be converted.

Details

Large (biological) datasets are often recorded on Excel sheets with the file going around several computers using different operating systems and locales. This often leads to dates being recorded in multiple formats from text strings, to various Excel date formats and numeric date codes for which the origin date may vary. This function attempts to fix such inconsistensies in date formats and returns the dates as a character column representing ISO 8601 dates. The function is still experimental and due to the many ways of recording dates in Excel, the outcome might differ from the desired outcome. Please check each date returned by the function and report any inconsistencies so that the function can be improved.

The add_time argument can be used to add or subtract hours from the output, if the times do not match with those in the Excel sheet. This can be helpful if your locale or operating system causes an offset between recorded dates.

The function also works for other types of messy dates than those recorded in Excel sheets.

Value

Returns a data.frame equal to dt with date_col as character representing ISO 8601 dates.

Author(s)

Mikko Vihtakari

MikkoVihtakari/MarineDatabase documentation built on July 7, 2020, 2:16 a.m.