extracting newest date from column with multiple dates in each cell

excel find latest date based on multiple criteria
vlookup latest date
find most recent date for id with multiple records in excel sheet
lookup a value and find max date in excel
index match(max date)
find latest date in excel
how to find the last transaction date in excel
index match multiple criteria max date

I have the following dummy data frame:

structure(list(id = 1:10, dates = c("2018-07-02, 2018-06-28", 
"2018-08-22", "2018-08-06, 2018-07-31", "2018-03-08", "2018-02-22, 2018-02-19", 
"2018-07-04, 2018-07-06", "2018-06-26, 2018-06-22", "2018-01-18, 2018-01-24", 
"2018-06-05, 2018-06-14", "2018-01-18")), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -10L))

I want to convert all entries in the "dates" column to date, then select the newest one, and remove all other dates in that cell.

I have tried the following:

library(dplyr)
library(reprex)
library(purrr)
library(lubridate)

test_df %>%
    mutate(dates = dates %>%
            str_extract_all("[0-9]+-[0-9]+-[0-9]+") %>%
            map(ymd) %>%
            map_lgl(~ any(max(.))))

But somehow this converts all entries in each cell to a number instead of proper dates.

What I want to end up with:

id dates
1 2018-07-02
2 2018-08-22            
3 2018-08-06
4 2018-03-08            
5 2018-02-22
6 2018-07-06
7 2018-06-26
8 2018-01-24
9 2018-06-14
10 2018-01-18

scan in the fields, take the largest and convert to Date class.

library(dplyr)

scan_max <- function(x) {
  max(scan(text = x, what = "", sep = ",", quiet = TRUE, strip.white = TRUE))
}
test_df %>%
  mutate(dates = as.Date(sapply(dates, scan_max)))

giving:

# A tibble: 10 x 2
      id dates     
   <int> <date>    
 1     1 2018-07-02
 2     2 2018-08-22
 3     3 2018-08-06
 4     4 2018-03-08
 5     5 2018-02-22
 6     6 2018-07-06
 7     7 2018-06-26
 8     8 2018-01-24
 9     9 2018-06-14
10    10 2018-01-18

It could also be written like this:

scan_max <- . %>% 
  scan(text = ., what = "", sep = ",", quiet = TRUE, strip.white = TRUE) %>%
  max

test_df %>%
  mutate(dates = dates %>% sapply(scan_max) %>% as.Date)

Find latest date based on a condition, The picture below shows you values in column B (B3:B9) and dates in column C (​C3:C9). The formula in cell B3 looks for the latest date in all three worksheets using the Read Lookup and find last date using multiple conditions I believe this article explains how to extract values based on a condition. A1, A2 etc. contain individual strings of data similar to the sample, I would like B1, B2 ect. (or B1:Z1,B2:Z2 however is easiest) to contain the date/time/name information related to the corresponding cell in column A. I would like to keep all information related to cell A1 in row 1, and B1 in row 2 and so on down the list.

You could try:

library(tidyverse)
df %>% mutate(dates = str_split(dates, ", ") %>%
                map(sort, decreasing = TRUE) %>%
                map_chr(1) %>%
                as.Date)

# A tibble: 10 x 2
      id dates     
   <int> <date>    
 1     1 2018-07-02
 2     2 2018-08-22
 3     3 2018-08-06
 4     4 2018-03-08
 5     5 2018-02-22
 6     6 2018-07-06
 7     7 2018-06-26
 8     8 2018-01-24
 9     9 2018-06-14
10    10 2018-01-18

Find latest date in a list, Last value in column The MAX function returns the largest number since Excel treats dates as numbers this will work fine. To extract the earliest date use the MIN function: The LARGE function extracts the k-th largest number in a cell range. k is the Formula for matching a date within a date range. The picture below shows you values in column B (B3:B9) and dates in column C (C3:C9). The formula in cell F4 lets you search for value and return the latest date in an adjacent or corresponding column for that value. Update, 2017-08-15! Added a regular formula. Formula in cell F4:

I use three mutates:

  1. splits the strings by comma
  2. converts strings to dates
  3. keeps only the newest date

Then this is it:

df <- structure(list(id = 1:10, dates = c("2018-07-02, 2018-06-28", 
                                    "2018-08-22", "2018-08-06, 2018-07-31", "2018-03-08", "2018-02-22, 2018-02-19", 
                                    "2018-07-04, 2018-07-06", "2018-06-26, 2018-06-22", "2018-01-18, 2018-01-24", 
                                    "2018-06-05, 2018-06-14", "2018-01-18")), class = c("tbl_df", 
                                                                                        "tbl", "data.frame"), row.names = c(NA, -10L))

library(tidyr)
library(stringi)
library(dplyr)

df_new <- df %>% 
  mutate(dates = stri_split_fixed(dates, ", ")) %>% 
  mutate(dates = rapply(dates, as.Date, how = "list")) %>% 
  mutate(dates = lapply(dates, function(x) {
    sort(x, decreasing = TRUE)[1]
  })) %>% 
  unnest(dates)

> df_new
# A tibble: 10 x 2
      id dates     
   <int> <date>    
 1     1 2018-07-02
 2     2 2018-08-22
 3     3 2018-08-06
 4     4 2018-03-08
 5     5 2018-02-22
 6     6 2018-07-06
 7     7 2018-06-26
 8     8 2018-01-24
 9     9 2018-06-14
10    10 2018-01-18

Another option with map instead of the two applys:

library(tidyr)
library(stringi)
library(dplyr)
library(purrr)

df_new <- df %>% 
  mutate(dates = stri_split_fixed(dates, ", ")) %>% 
  mutate(dates = map(dates, function(x) {
    x <- as.Date(x)
    sort(x, decreasing = TRUE)[1]
  })) %>%
  unnest(dates)

df_new

How to find earliest and latest dates in a range in Excel?, Easily find the max/min value based of each item in another column Exact Copy Multiple Cells Extract Text, Add Text, max date of function. Split date into three columns-day, month and year with formulas. In Excel, you can use below simple formulas to extract day, month or year only from a date cell. 1. Select a cell, for instance, C2, type this formula =DAY(A2), press Enter, the day of the reference cell is extracted.

Calculate the earliest or latest date in Excel based on criteria by , Use this combination of Excel functions to find the latest date and return a functions, to return the latest and earliest dates, respectively, in a range. Keep in mind that the lookup range (column A) contains unique values. Privacy Policy | Cookies | Ad Choice | Terms of Use | Mobile User Agreement. in column B a search is performed from the previous comma (starting with 0) until the last one (ending with LEN()+1). in column C the string between the commas is separated; in column D the location of the '(' character is found; in column E the location of the ')' character is found; in column F the text before the '(' character is selected

How to find the latest (or earliest) date and a corresponding value , The INDEX function returns a value from the tbl_data[Date] column, and the We fill the formula down, and we have the max dates, as shown below. We start by selecting any cell in the data range, and then use the Insert  To extract lines from a multi-line cell, you can use a clever (and intimidating) formula that combines 5 Excel functions: TRIM, MID, SUBSTITUTE, REPT, and LEN. In the example shown, the formula in D5 is: =

Most Recent Transaction Date, 1. Remember to search before asking. Using MAX() in Excel against a list of dates using multiple criteria. See under 'Related'?. RELATED  I am using ALT + Enter along with comma to enter the multiple dates in one cell. I want to do some operation and date analysis on these two cell but i understand that the contents of these two cell is not in date format and as per my understanding it is in text format.

Comments
  • Neat and clean - love one liners! Thanks!