Hi,
This is my idea how to do it:
library(lubridate)
library(dplyr)
mockdata <-tibble::tribble(
~Customer_ID, ~Payment_date, ~Year, ~Join.date, ~Frequency, ~Cohort,
18445, "09-11-2018", 2018, "12-02-2018", 11, 2,
18445, "11-06-2018", 2018, "12-02-2018", 11, 2,
18445, "12-12-2018", 2018, "12-02-2018", 11, 2,
18445, "12-09-2018", 2018, "12-02-2018", 11, 2,
18445, "11-07-2018", 2018, "12-02-2018", 11, 2,
18445, "10-04-2018", 2018, "12-02-2018", 11, 2,
18445, "13-08-2018", 2018, "12-02-2018", 11, 2,
18445, "10-10-2018", 2018, "12-02-2018", 11, 2,
18445, "09-03-2018", 2018, "12-02-2018", 11, 2,
18445, "12-02-2018", 2018, "12-02-2018", 11, 2
)
#Transform dates to date class
mockdata = mockdata %>% mutate(Payment_date= as.Date(Payment_date, format = "%d-%m-%Y"),
Join.date = as.Date(Join.date, format = "%d-%m-%Y"))
#Calculate the difference in months
mockdata = mockdata %>% mutate(Age_by_Month = (year(Payment_date) - year(Join.date))*12 +
(month(Payment_date) - month(Join.date)))
# A tibble: 10 x 7
Customer_ID Payment_date Year Join.date Frequency Cohort Age_by_Month
<dbl> <date> <dbl> <date> <dbl> <dbl> <dbl>
1 18445 2018-11-09 2018 2018-02-12 11 2 9
2 18445 2018-06-11 2018 2018-02-12 11 2 4
3 18445 2018-12-12 2018 2018-02-12 11 2 10
4 18445 2018-09-12 2018 2018-02-12 11 2 7
5 18445 2018-07-11 2018 2018-02-12 11 2 5
6 18445 2018-04-10 2018 2018-02-12 11 2 2
7 18445 2018-08-13 2018 2018-02-12 11 2 6
8 18445 2018-10-10 2018 2018-02-12 11 2 8
9 18445 2018-03-09 2018 2018-02-12 11 2 1
10 18445 2018-02-12 2018 2018-02-12 11 2 0
I extracted the year and month for both payment date and join date. Then I use the difference in months and years (= 12 months) to calculate the total difference in months.
Hope this helps,
PJ