Cannot Format My Dates

Hello..I'm having a hard time running this code:
data = read.delim(file = 'purchases.txt', header = FALSE, sep = '\t', dec = '.')

colnames(data) = c('customer_id', 'purchase_amount', 'date_of_purchase')

data <- with(data, data[!(customer_id == "" | is.na(customer_id)), ])

data$date_of_purchase = as.Date(data$date_of_purchase, "%Y/%m/%d")
data$year_of_purchase = as.numeric(format(data$date_of_purchase, "%Y"))
data$days_since = as.numeric(difftime(time1 = "2019/03/25",
time2 = data$date_of_purchase,
units = "days"))
After running: data$date_of_purchase = as.Date(data$date_of_purchase, "%y/%m/%d"); the first row as an example had an initial date of: 16/12/2017 then the value became 0016-12-20.

I want the date to be displayed as 2017/12/16 and not the obtained value of 0016-12-20.

Thank you for your assistance!

Welcome to the community!

I think you're looking for something like this:

data_example <- "16/12/2017"
data_example
#> [1] "16/12/2017"

data_as_date <- as.Date(x = data_example,
                        format = "%d/%m/%Y")
data_as_date
#> [1] "2017-12-16"

data_displayed_in_desired_format <- format(x = data_as_date,
                                           format = "%Y/%m/%d")
data_displayed_in_desired_format
#> [1] "2017/12/16"

Created on 2019-06-15 by the reprex package (v0.3.0)

Your data (16/12/2017) is in the format DD/MM/YYYY, and hence the format "%Y/%m/%d" that you have used here doesn't work correctly. You should use "%d/%m/%Y". You can learn about these formats in the documentation of strptime.

Hope this helps.

For your future posts, please provide a REPRoducible EXample of your problem. It provides more specifics of your problem, and it helps others to understand what problem you are facing.

If you don't know how to do it, take a look at this thread:

3 Likes

Hi. Thanks for your response. I still cannot convert my data in the desired format. I have changed the format to "%d/%m/%Y but still get awkward results:
Attached
data = read.delim(file = 'purchases.txt', header = FALSE, sep = '\t', dec = '.')

colnames(data) = c('customer_id', 'purchase_amount', 'date_of_purchase')

data <- with(data, data[!(customer_id == "" | is.na(customer_id)), ])

data$date_of_purchase = as.Date(data$date_of_purchase, "%d/%m/%y")
data$year_of_purchase = as.numeric(format(data$date_of_purchase, "%Y"))
data$days_since = as.numeric(difftime(time1 = "2019/03/25",
time2 = data$date_of_purchase,
units = "days"))

See attached. Thanks!

X$newdate <- strptime(as.character(X$date), "%d/%m/%Y")

format(X$newdate, "%Y-%m-%d")

1 Like

Problem solved. Thank you so much..Iā€™m new to R :slight_smile:)

Careful here, strptime will give you a POSIXlt datetime object, which is not much used anymore (POSIXct has some advantages). In this case, you're not working with datetimes anyway, so as.Date will suffice.

Also be aware that format returns character strings, not date or datetime objects, so you normally don't want to call it until you're presenting your results.

1 Like

0000@000.com	$22	08/01/2019
0000christie@gmail.com	$174	02/11/2017
0000christie@gmail.com	$61	03/01/2018
000@hotmail.com	$33	16/12/2017
000@hotmail.com	$9	28/12/2017
001nzpi@gmail.com	$136	24/10/2017
001nzpi@gmail.com	$191	10/12/2017
001nzpi@gmail.com	$24	25/01/2018
001nzpi@gmail.com	$381	20/10/2018
001nzpi@gmail.com	$165	20/12/2018
001nzpi@gmail.com	$144	22/01/2019
005moloney@gmail.com	$35	18/12/2017
007cadbury@gmail.com	$81	26/12/2017
007cadbury@gmail.com	$43	06/02/2019
007cadbury@gmail.com	-$43	21/02/2019
007daws@gmail.com	$13	01/08/2018
007girl@hotmail.co.nz	$39	17/12/2017
007lisaday@gmail.com	$110	07/01/2018
007lisaday@gmail.com	$8	29/01/2018
007lisaday@gmail.com	$24	30/01/2018
007lisaday@gmail.com	$51	03/02/2018
007lisaday@gmail.com	$50	04/02/2018
007lisaday@gmail.com	$14	15/02/2018
007lisaday@gmail.com	$280	14/08/2018
007marsden@clear.net.nz	$31	29/04/2018
007pennington@gmail.com	$130	09/03/2019

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.