Date Conversion from Engineering Notation to Standard xx/xx/xx

I've exported data from Active Directory and imported the resultant .csv file into MS Excel. All the dates and times are displaying in MS Excel in Engineering Notation format and are of no use in this format.

How can I get the dates from Active Directory such that they display correctly?  Or how can I use MS Excel to view the correct date in the appropriate format.

I tried changing the column format.  Didn't work.  I also tried to change the field type to "Date" when importing the data.csv into excel.

Specifically looking for date fields like "LastLogon" or "modified".  Getting 1.32314E+16 where it should read 3/14/2014 or something similar.

Thanks
egaloisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
You need only select the column and change its formatting to date/time in the format you want dd/mm/yyyy h:m:s..
arnoldCommented:
The other option is open excel first, then open the csv and there you will have the option to designate the formatting of columns as you need them.
egaloisAuthor Commented:
Thanks Arnold, but if you read in my original post, I tried both these methods.  Thanks though.
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Mohammed RahmanCommented:
Did you try custom sort column as dd/mm/yyyy
egaloisAuthor Commented:
Hi Mohammed, Can you be more specific?  I'm not sure what you mean. Apologies.
Mohammed RahmanCommented:
Select the column you want to sort as dd/mm/yyyy... At top in format dropdown, select custom...or i think its called more number format, its the last option in dropdown. Select custom, at right pane of new pop up, select the format of your choice. Like: dd-mm-yy (there would be lots of options to choose from)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
arnoldCommented:
What is the format of the date in the csv file when viewed using notepad/wordpad.?

Try the following, open excel first. Then use open/insert file and select the csv and see whether it triggers the wizard to match the source of data to place them in columns. once you set comma as separator, .you have th option to choose each column and designate the format

....

The idea is to guide the csv data handling so as to not have excel choose an option for a cell/column that does not work for you.
egaloisAuthor Commented:
Thank you Mohammed and Arnold. I appreciate the help thus far.  The format is ldap's native formatting for dates exported.  

Here is an article that speaks to more of the specifics.  I just found this.  Not sure it's my solution yet.  But it's getting me closer.  Using excel to reformat directly doesn't work. No matter how the data is treated.  Because excel doesn't seem to recognize the original format.
http://stackoverflow.com/questions/9806329/converting-a-ldap-date
Mohammed RahmanCommented:
Can you help us with a CSV (if possible). We can probably try import it to excel and hopefully come up with a solution or an alternative.
arnoldCommented:
Define the column first  as a date time in the format yyyymmddHHMMss when importing the csv, you can then change the format as you see fit.
What are you using the export? What tools do you have to preprocess the .csv?
David Johnson, CD, MVPOwnerCommented:
what you are seeing are ticks to excel if ticks are in A1
=IF(A1>0,A1/(8.64*10^11) - 109205,"") format destination cell as datesample picture
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Active Directory

From novice to tech pro — start learning today.