Excel Date and Time Functions
50+ date and time functions familiar to users coming from an Excel Background. The main benefits are:
Integration of the amazing lubridate package for handling dates and times
Integration of Holidays from timeDate and Business Calendars
New Date Math and Date Sequence Functions that factor in Business Calendars (e.g. EOMONTH(), NET_WORKDAYS())
These functions are designed to help users coming from an Excel background. Most functions replicate the behavior of Excel:
Names in most cases match Excel function names
Functionality replicates Excel
By default, missing values are ignored (same as in Excel)
AS_DATE(x, ...)
AS_DATETIME(x, ...)
DATE(year, month, day)
DATEVALUE(x, ...)
YMD(x, ...)
MDY(x, ...)
DMY(x, ...)
YMD_HMS(x, ...)
MDY_HMS(x, ...)
DMY_HMS(x, ...)
YMD_HM(x, ...)
MDY_HM(x, ...)
DMY_HM(x, ...)
YMD_H(x, ...)
MDY_H(x, ...)
DMY_H(x, ...)
WEEKDAY(x, ..., label = FALSE, abbr = TRUE)
WDAY(x, ..., label = FALSE, abbr = TRUE)
DOW(x, ..., label = FALSE, abbr = TRUE)
MONTHDAY(x, ...)
MDAY(x, ...)
DOM(x, ...)
QUARTERDAY(x, ...)
QDAY(x, ...)
DAY(x, ...)
WEEKNUM(x, ...)
WEEK(x, ...)
WEEKNUM_ISO(x, ...)
MONTH(x, ..., label = FALSE, abbr = TRUE)
QUARTER(x, ..., include_year = FALSE, fiscal_start = 1)
YEAR(x, ...)
YEAR_ISO(x, ...)
DATE_TO_NUMERIC(x, ...)
DATE_TO_DECIMAL(x, ...)
SECOND(x, ...)
MINUTE(x, ...)
HOUR(x, ...)
NOW(...)
TODAY(...)
EOMONTH(start_date, months = 0)
EDATE(start_date, months = 0)
NET_WORKDAYS(start_date, end_date, remove_weekends = TRUE, holidays = NULL)
COUNT_DAYS(start_date, end_date)
YEARFRAC(start_date, end_date)
DATE_SEQUENCE(start_date, end_date, by = "day")
WORKDAY_SEQUENCE(start_date, end_date, remove_weekends = TRUE, holidays = NULL)
HOLIDAY_SEQUENCE(
start_date,
end_date,
calendar = c("NYSE", "LONDON", "NERC", "TSX", "ZURICH")
)
HOLIDAY_TABLE(years, pattern = ".")
FLOOR_DATE(x, ..., by = "day")
FLOOR_DAY(x, ...)
FLOOR_WEEK(x, ...)
FLOOR_MONTH(x, ...)
FLOOR_QUARTER(x, ...)
FLOOR_YEAR(x, ...)
CEILING_DATE(x, ..., by = "day")
CEILING_DAY(x, ...)
CEILING_WEEK(x, ...)
CEILING_MONTH(x, ...)
CEILING_QUARTER(x, ...)
CEILING_YEAR(x, ...)
ROUND_DATE(x, ..., by = "day")
ROUND_DAY(x, ...)
ROUND_WEEK(x, ...)
ROUND_MONTH(x, ...)
ROUND_QUARTER(x, ...)
ROUND_YEAR(x, ...)x |
A vector of date or date-time objects |
... |
Parameters passed to underlying |
year |
Used in |
month |
Used in |
day |
Used in |
label |
A logical used for |
abbr |
A logical used for |
include_year |
A logicial value used in |
fiscal_start |
A numeric value used in |
start_date |
Used in Date Math and Date Sequence operations. The starting date in the calculation. |
months |
Used to offset months in |
end_date |
Used in Date Math and Date Sequence operations. The ending date in the calculation. |
remove_weekends |
A logical value used in Date Sequence and Date Math calculations. Indicates whether or not weekends should be removed from the calculation. |
holidays |
A vector of dates corresponding to holidays that should be removed from the calculation. |
by |
Used to determine the gap in Date Sequence calculations and value to round to in Date Collapsing operations.
Acceptable values are: A character string, containing one of |
calendar |
The calendar to be used in Date Sequence calculations for Holidays from the |
years |
A numeric vector of years to return Holidays for in |
pattern |
Used to filter Holidays (e.g. |
Converters - Make date and date-time from text (character data)
Extractors - Returns information from a time-stamp.
Current Time - Returns the current date/date-time based on your locale.
Date Math - Perform popular Excel date calculations
EOMONTH() - End of Month
NET_WORKDAYS(), COUNT_DAYS() - Return number of days between 2 dates factoring in working days and holidays
YEARFRAC() - Return the fractional period of the year that has been completed between 2 dates.
Date Sequences - Return a vector of dates or a Holiday Table (tibble).
DATE_SEQUENCE(), WORKDAY_SEQUENCE(), HOLIDAY_SEQUENCE - Return a sequence of dates between 2 dates that
factor in workdays and timeDate holiday calendars for popular business calendars including NYSE and London stock exchange.
Date Collapsers - Collapse a date sequence (useful in dplyr::group_by() and pivot_table())
FLOOR_DATE(), FLOOR_DAY(), FLOOR_WEEK(), FLOOR_MONTH(), FLOOR_QUARTER(), FLOOR_YEAR()
Similar functions exist for CEILING and ROUND. These are wrappers for lubridate functions.
Converters - Date or date-time object the length of x
Extractors - Returns information from a time-stamp.
Current Time - Returns the current date/date-time based on your locale.
Date Math - Numeric values or Date Values depending on the calculation.
Date Sequences - Return a vector of dates or a Holiday Table (tibble).
Date Collapsers - Date or date-time object the length of x
# Libraries
library(tidyquant)
library(tidyverse)
library(lubridate)
# --- Basic Usage ----
# Converters ---
AS_DATE("2011 Jan-01") # General
YMD("2011 Jan-01") # Year, Month-Day Format
MDY("01-02-20") # Month-Day, Year Format (January 2nd, 2020)
DMY("01-02-20") # Day-Month, Year Format (February 1st, 2020)
# Extractors ---
WEEKDAY("2020-01-01") # Labelled Day
WEEKDAY("2020-01-01", label = FALSE) # Numeric Day
WEEKDAY("2020-01-01", label = FALSE, week_start = 1) # Start at 1 (Monday) vs 7 (Sunday)
MONTH("2020-01-01")
QUARTER("2020-01-01")
YEAR("2020-01-01")
# Current Date-Time ---
NOW()
TODAY()
# Date Math ---
EOMONTH("2020-01-01")
EOMONTH("2020-01-01", months = 1)
NET_WORKDAYS("2020-01-01", "2020-07-01") # 131 Skipping Weekends
NET_WORKDAYS("2020-01-01", "2020-07-01",
holidays = HOLIDAY_SEQUENCE("2020-01-01", "2020-07-01",
calendar = "NYSE")) # 126 Skipping 5 NYSE Holidays
# Date Sequences ---
DATE_SEQUENCE("2020-01-01", "2020-07-01")
WORKDAY_SEQUENCE("2020-01-01", "2020-07-01")
HOLIDAY_SEQUENCE("2020-01-01", "2020-07-01", calendar = "NYSE")
WORKDAY_SEQUENCE("2020-01-01", "2020-07-01",
holidays = HOLIDAY_SEQUENCE("2020-01-01", "2020-07-01",
calendar = "NYSE"))
# Date Collapsers ---
FLOOR_DATE(AS_DATE("2020-01-15"), by = "month")
CEILING_DATE(AS_DATE("2020-01-15"), by = "month")
CEILING_DATE(AS_DATE("2020-01-15"), by = "month") - ddays(1) # EOMONTH using lubridate
# --- Usage with tidyverse ---
# Calculate returns by symbol/year/quarter
FANG %>%
pivot_table(
.rows = c(symbol, ~ QUARTER(date)),
.columns = ~ YEAR(date),
.values = ~ PCT_CHANGE_FIRSTLAST(adjusted)
)Please choose more modern alternatives, such as Google Chrome or Mozilla Firefox.