convert ASCI date to dd/mm/yyyy

Daithi

New Member
Joined
Feb 28, 2014
Messages
15
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,783
Office Version
365
Platform
Windows
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".
 

Forum statistics

Threads
1,085,693
Messages
5,385,210
Members
401,936
Latest member
stephenpoff

Some videos you may like

This Week's Hot Topics

Top