Automatically-Updating Date Field

Suppose you’re working with data that includes dates (e.g., birth dates, start or stop dates for a project or customer account, graduation dates, etc.) and you want to flag those observations whose dates meet some criterion related to today's date. For example, you’re working with customer account data, and you want to identify those customer accounts that were closed in the past year. To flag recently-closed accounts, you need to test the account close date against a date representing one year ago today, but given that time keeps passing, the date that represents one year ago today keeps changing, too. If you’re going to be re-running your code periodically, you’ll want the program to automatically update the test date based upon the current date. (The alternative is manually updating the test date each time you run the program, which is inefficient and also susceptible to error.) 

This post presents some clean, simple code that will update a date-related field using today's date as the reference point. You'll need to install the lubridate package, if you don't already have it.

In this example, imagine that you’re working with customer account data, and you want to identify those customer accounts that were closed in the past year. The close.date field in the example below indicates the date the account was closed.

# Generate some sample data
data <- data.frame("account.number" = sample(100000:999999, 100, replace=F), 
 "close.date" = seq.Date(as.Date("2008-01-04"), by = 30, length.out = 100)) 

# Today's date is automatically provided by the system
today <- Sys.Date()

# Subtract a specified amount of time from today's date in years, months, or days
library(lubridate)
one.year.ago.today <- today - years(1) 
# one.year.ago.today <- today - months(12)
# one.year.ago.today <- today - days(365)

# Create a field that tests the relevant date field in the data against the criterion you've provided
# In this example, we're testing close.date in the data against one.year.ago.today,
# the cut-off for those accounts that were closed in the past year.
data$recently.closed <- ifelse(data$close.date > one.year.ago.today, 1, 0)