Date format issue

Copper Contributor

Hi,

 

i have date in Excel which is 10/7/19 which is actually 10th of July 2019 but when i convert the column to date it is taking as 7th October 2019, kindly help i want to do this without changing the system settings as it is locked for me. 

6 Replies

@rijuthomas 

If your Date is in A2, you may have to convert it to this date in B2: 

=DATE(YEAR(A2),
DAY(A2),
MONTH(A2))

@rijuthomas 

 

If your dates are in one column another variant which could work is to select that column, Data->Text to Columns and on the third step of the wizard select Date and MDY

@Twifoothanks it works but in some places error is coming

@rijuthomas 

Please specify what errors are returned in some of the cells where the formula is applied?

@Twifoosir i have attached the sample sheet for your info

@rijuthomas 

Please give this a try...

 

In B2

=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))

and then copy it down.