Results 1 to 5 of 5

EXCEL - Convert Serial Date to Normal Date Format

This is a discussion on EXCEL - Convert Serial Date to Normal Date Format within the Excel Questions forums, part of the Question Forums category; I have a csv file (from fox pro db) which I converted to Excel 2007. This xls file contains the ...

  1. #1
    New Member
    Join Date
    Jan 2009
    Posts
    2

    Question EXCEL - Convert Serial Date to Normal Date Format

    I have a csv file (from fox pro db) which I converted to Excel 2007. This xls file contains the fields: EmployeeID, EmployeeName, Date, Day, Time, etc....(Looks great in PDF) HOWEVER, in xls, the date, time and day come out wrong. How can I convert the 10-digit number to normal date format?
    Egs: of a 10-digit number I have in the first 3 cells are: 3441076560; 3441076261; 3441077042 and so on.
    I'm wondering if the time is also included in this 10-digit serial number?

    Please help.

  2. #2
    Banned
    Join Date
    Sep 2006
    Location
    Bombay, India
    Posts
    3,274

    Default Re: EXCEL - Convert Serial Date to Normal Date Format

    Hi

    Welcome to MrExcel.

    I hope that I have understood your question but incase I am wrong, then please accept my apologies.

    Assuming that you have the 10 digit number in cell A1, and provided that time is included in this 10 digit number, if you need to convert the date into a normal date, you can try:

    =TEXT(LEFT(A1,5),"dd-mmm-yyyy")

    Does that help ?

  3. #3
    Board Regular
    Join Date
    Oct 2006
    Location
    日本
    Posts
    2,457

    Default Re: EXCEL - Convert Serial Date to Normal Date Format

    Sheet1

     ABC
    1CodeDateTime
    2344107626117-Mar-19941:49:49 AM
    3344107656017-Mar-19941:50:15 AM
    4344107704217-Mar-19941:50:56 AM

    Spreadsheet Formulas
    CellFormula
    B2=TEXT(LEFT(A2,5),"dd-mmm-yyyy")
    C2=TEXT("."&MID(A2,5,LEN(A2)-5),"h:mm:ss AM/PM")
    B3=TEXT(LEFT(A3,5),"dd-mmm-yyyy")
    C3=TEXT("."&MID(A3,5,LEN(A3)-5),"h:mm:ss AM/PM")
    B4=TEXT(LEFT(A4,5),"dd-mmm-yyyy")
    C4=TEXT("."&MID(A4,5,LEN(A4)-5),"h:mm:ss AM/PM")


    Excel tables to the web >> Excel Jeanie HTML 4
    Download an HTML Maker to show your data, and please wrap all code in [code][/code] tags so we can read it. My mind-reading add-in is on my other computer.

  4. #4
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995

    Default Re: EXCEL - Convert Serial Date to Normal Date Format

    If 3441076560 is in B2 then try

    Date
    =--Replace(B2,6,,".")
    format the cell like "mm/dd/yyyy"

    Day
    =Replace(B3,6,,".")
    format the cell like "d"

    Time
    =Replace(B4,6,,".")
    format the cell like "hh:mm:ss"

  5. #5
    New Member
    Join Date
    Jan 2009
    Posts
    2

    Default Re: EXCEL - Convert Serial Date to Normal Date Format

    I would like to THANK Stormseed,Sal Paradise and jindon for all your help. I'm on my way to completing my task - with smiles today unlike yesterday.

    Rock On!

    Many Thanks,
    Pacifica09

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com