Solved

Dates appears as serial numbers

Posted on 2014-02-09
4
220 Views
Last Modified: 2014-05-21
Hi Experts,

I download a CSV file and when I open the file I see the dates as serial numbers(10 digits).
I tried to change the format cells to date but still doesn't work.

Please advise
0
Comment
Question by:chenzovicc
4 Comments
 
LVL 8

Expert Comment

by:TheNautican
ID: 39846151
I found this to convert Excel serial date to regular date format.

=UPPER(TEXT(A1,"mmm-d"))

Open in new window


You can probably mes around with the "mmm-d" part to get the exact format you want.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39846239
Without any other information it is hard to guess how the dates have been converted to numbers, and hence how to convert them back again,

Today's date (10/2/2014) has the value of 41680 in Excel.

Can you give some examples of the 10 digit numbers, and the dates that they represent?
0
 
LVL 12

Accepted Solution

by:
Harry Lee earned 500 total points
ID: 39855060
When you say 10-Digit time, I believe you are talking about unix time.

try use the following formula to convert the timestamp.

Put the 10-Digit date in A1, then the formula in another cell.

=A1/(60*60*24)+"1/1/1970"

After that, format the formulated cell to Date format in excel.
0
 

Author Closing Comment

by:chenzovicc
ID: 40081983
Thanks
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article will show you how to use shortcut menus in the Access run-time environment.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now