Date difference coming out to be wrong - Python

Question

I had date values in two formats (6/13/2018 and 6-13-2018). I had to calculate date difference. Below is my working.

Question: Number of days are coming out to be incorrect for few items.

X['Date of Closing'] = X['Date of Closing'].str.replace('/','-')
X['Date of First Contact'] = X['Date of First Contact'].str.replace('/','-')

X['Date Difference'] = (pd.to_datetime(X['Date of Closing'])- pd.to_datetime(X['Date of First Contact'])).dt.days

Example:

Date of First Contact  | Date of Giving Proposal  | Date of Closing  \
0             13-01-2014              26-02-2014      26-02-2014   
1             28-01-2014                2/2/2014        2-2-2014   
2              11-1-2014              26-01-2014      26-01-2014   
3             18-01-2014              18-01-2014      18-01-2014   
4             14-01-2014              14-01-2014      14-01-2014   
5               5-1-2014              14-01-2014      14-01-2014

Output:

44 - Correct

5 - Correct

-279 - Incorrect

0 - Correct

0 - Correct

-107 - Incorrect

You may need to pass `day_first=True` to `pd.to_datetime`. – ayhan Jun 13 '18 at 13:13 — ayhan, Jun 13 '18 at 13:13

jezrael · Accepted Answer · 2018-06-13T13:19:02.417

I believe need parameter dayfirst=True or format:

X['Date Difference'] = (pd.to_datetime(X['Date of Closing'], dayfirst=True)- 
                        pd.to_datetime(X['Date of First Contact'], dayfirst=True)).dt.days

X['Date Difference'] = (pd.to_datetime(X['Date of Closing'], format='%d-%m-%Y')- 
                        pd.to_datetime(X['Date of First Contact'], format='%d-%m-%Y')).dt.days

print (X)
  Date of First Contact Date of Giving Proposal Date of Closing  \
0            13-01-2014              26-02-2014      26-02-2014   
1            28-01-2014                2/2/2014        2-2-2014   
2             11-1-2014              26-01-2014      26-01-2014   
3            18-01-2014              18-01-2014      18-01-2014   
4            14-01-2014              14-01-2014      14-01-2014   
5              5-1-2014              14-01-2014      14-01-2014   

   Date Difference  
0               44  
1                5  
2               15  
3                0  
4                0  
5                9

Worked like a charm. Thanks a lot. – Harsh Khubchandani Jun 13 '18 at 13:18 — Harsh Khubchandani, Jun 13 '18 at 13:18

Date difference coming out to be wrong - Python

1 Answers1