Dates Appearing Backwards

Question

Hi All,

I have some dates saved in an Access App in Sharepoint. I've linked the database into an excel spreadsheet but the dates or returning backwards. They are saved in the database as DD/MM/YYYY but are displaying YYYY/MM/DD. I don't understand why this is happening and I've checked the data format in excel and it is saying it should appear as DD/MM/YYYY. It's also doing this in a mail merge in word.

Any help is much appreciated!

Regards,

Matt

Answer 1

Hi,

Try this formula in cell B2 and copy down

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

Hope this helps.

Answer 2

You may also use this alternate formula.

=VALUE(RIGHT(A2,2)&"/"&MID(A2,6,2)&"/"&LEFT(A2,4))

Also in Ashish's formula, do a very small correction, change 5 to 6 in MID formula as your month starts from 6th character.

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

Answer 3

Hi Ashish,

That did fix it until I refreshed the data. Is there something that will permanently format the data in the required format?  And also a way to implement this into Word aswell?

Regards,

Matt

Answer 4

Hi,

Select the range which has the dates and go to Data > Text to columns > Delimited > Next > Next > Date > YMD > Finish

Hope this helps.