convert ASCI date to dd/mm/yyyy

[Daithi]

I've a spreadsheet where the date column is 04-APR-17. By replacing the - with a / and formatting as a date I can only get it to show 4/4/2017 in the formula bar. The field however shows 42829. I need it to be 4/4/2017 (dd/mm/yyyy)

All help appreciated

 

[arthurbr]

42829 is the same as 4/4/2017, you just need to format it accordingly

Perhaps read thsi https://support.microsoft.com/en-us/help/214094/how-to-use-dates-and-times-in-excel

 

[Joe4]

The field however shows 42829

That is how Excel stores dates, the number of days since 1/0/1900.
Enter any date into an Excel cell, and change the format to "General" or "Number", and you will see values like this.
If you want it to look like 4/4/2017, you just need to change the cell format to "d/m/yyyy".