Log In
Register
Home
Active Member
Power Pivot
February 12, 2021
Offline
Hi
I am really stuck and in need of some help. I will caveat this plea with a note of caution - I am still a novice Excel user!
I am doing some data submissions using payroll data which has been sent to me in csv format (27K records). I need to submit data in this format DD/MM/YYYY.
The date format in the files is this format. When you click on the cell is states is in General format. When you try and convert it to short date format you get a string of ###############
| 19061966 |
| 6071962 |
What is the best way to get to the DD/MM/YYYY format??
Trusted Members
October 18, 2018
Offline
Active Member
Power Pivot
February 12, 2021
Offline
Hi thanks for this. I have tried DATEVALUE and the problem remains. I've attached a sample file. I am working in O365. When you do format cell & select short date, that's when you see the ##################. I might have to go back to the data owner!
VIP
Trusted Members
December 7, 2016
Offline
Hello,
No file attached. Try to upload again.
Br,
Anders
Active Member
Power Pivot
February 12, 2021
Offline
Apologies!
VIP
Trusted Members
December 7, 2016
Offline
Hello,
It was just an issue about number format. Excel removes leading zeros in numbers. In attached file you can see how I built the formula.
If this is the way the data looks in your file then I suggest you create helper columns to fix the date values, like I did in the sample file.
Br,
Anders
1 Guest(s)
