Solved

Dates appears as serial numbers

Posted on 2014-02-09
4
231 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

617 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