+ Reply to Thread
Results 1 to 7 of 7

Date converted to Text Number

  1. #1
    Mari
    Guest

    Date converted to Text Number

    If a cell is converted from a date format to text format, the date in that
    cell is converted to a 5-digit number.

    I would like to know how excel calculates that number so that I can reverse
    that formula to figure out what dates I originally had in a bunch of cells
    which now have 5-digit numbers in them, without having to change each cell
    back to date format.

    thanks,

    m-

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Dates in excel start from

    01/01/1900 =1

    so 28/7/06 = 38926

    VBA Noob

  3. #3
    Gord Dibben
    Guest

    Re: Date converted to Text Number

    The 5 digit number is the number of days since Jan-00-1900

    Jan 1st, 1900 is day 1

    For more on this subject see Chip Pearson's site.

    http://www.cpearson.com/excel/datetime.htm#AddingDates

    Why not just re-format to Date?


    Gord Dibben MS Excel MVP

    On Fri, 28 Jul 2006 14:37:02 -0700, Mari <[email protected]> wrote:

    >If a cell is converted from a date format to text format, the date in that
    >cell is converted to a 5-digit number.
    >
    >I would like to know how excel calculates that number so that I can reverse
    >that formula to figure out what dates I originally had in a bunch of cells
    >which now have 5-digit numbers in them, without having to change each cell
    >back to date format.
    >
    >thanks,
    >
    >m-



  4. #4
    Mari
    Guest

    Re: Date converted to Text Number

    Thank you! very interesting.

    "Gord Dibben" wrote:

    > The 5 digit number is the number of days since Jan-00-1900
    >
    > Jan 1st, 1900 is day 1
    >
    > For more on this subject see Chip Pearson's site.
    >
    > http://www.cpearson.com/excel/datetime.htm#AddingDates
    >
    > Why not just re-format to Date?
    >
    >
    > Gord Dibben MS Excel MVP
    >
    > On Fri, 28 Jul 2006 14:37:02 -0700, Mari <[email protected]> wrote:
    >
    > >If a cell is converted from a date format to text format, the date in that
    > >cell is converted to a 5-digit number.
    > >
    > >I would like to know how excel calculates that number so that I can reverse
    > >that formula to figure out what dates I originally had in a bunch of cells
    > >which now have 5-digit numbers in them, without having to change each cell
    > >back to date format.
    > >
    > >thanks,
    > >
    > >m-

    >
    >


  5. #5
    Mari
    Guest

    RE: Date converted to Text Number

    Thanks!

    "Mari" wrote:

    > If a cell is converted from a date format to text format, the date in that
    > cell is converted to a 5-digit number.
    >
    > I would like to know how excel calculates that number so that I can reverse
    > that formula to figure out what dates I originally had in a bunch of cells
    > which now have 5-digit numbers in them, without having to change each cell
    > back to date format.
    >
    > thanks,
    >
    > m-


  6. #6
    Mari
    Guest

    Re: Date converted to Text Number

    To answer your question:

    all the cells in this particular column, i have decided, need to be in text
    format. only about 10% of the cells had a date only in them (the ones with
    date & text converted fine). So, I would still have to convert them to date,
    then convert back to text again, and type the text date into the text
    formatted cell.

    my conclusion:
    too much work. the dates really aren't that important anyway....
    but I'm glad I learned something today!

    Thanks again.

    m-
    , - . _ , - .
    \ /, " ,\ /
    { o }
    Ŭ

    "Gord Dibben" wrote:

    > The 5 digit number is the number of days since Jan-00-1900
    >
    > Jan 1st, 1900 is day 1
    >
    > For more on this subject see Chip Pearson's site.
    >
    > http://www.cpearson.com/excel/datetime.htm#AddingDates
    >
    > Why not just re-format to Date?
    >
    >
    > Gord Dibben MS Excel MVP
    >
    > On Fri, 28 Jul 2006 14:37:02 -0700, Mari <[email protected]> wrote:
    >
    > >If a cell is converted from a date format to text format, the date in that
    > >cell is converted to a 5-digit number.
    > >
    > >I would like to know how excel calculates that number so that I can reverse
    > >that formula to figure out what dates I originally had in a bunch of cells
    > >which now have 5-digit numbers in them, without having to change each cell
    > >back to date format.
    > >
    > >thanks,
    > >
    > >m-

    >
    >


  7. #7
    Gord Dibben
    Guest

    Re: Date converted to Text Number

    Thanks for the feedback.

    Chip has amassed a wealth of information on his site.

    When you get time, browse around.


    Gord

    On Fri, 28 Jul 2006 15:36:01 -0700, Mari <[email protected]> wrote:

    >To answer your question:
    >
    >all the cells in this particular column, i have decided, need to be in text
    >format. only about 10% of the cells had a date only in them (the ones with
    >date & text converted fine). So, I would still have to convert them to date,
    >then convert back to text again, and type the text date into the text
    >formatted cell.
    >
    >my conclusion:
    >too much work. the dates really aren't that important anyway....
    >but I'm glad I learned something today!
    >
    >Thanks again.
    >
    >m-
    > , - . _ , - .
    > \ /, " ,\ /
    > { o }
    > ?
    >
    >"Gord Dibben" wrote:
    >
    >> The 5 digit number is the number of days since Jan-00-1900
    >>
    >> Jan 1st, 1900 is day 1
    >>
    >> For more on this subject see Chip Pearson's site.
    >>
    >> http://www.cpearson.com/excel/datetime.htm#AddingDates
    >>
    >> Why not just re-format to Date?
    >>
    >>
    >> Gord Dibben MS Excel MVP
    >>
    >> On Fri, 28 Jul 2006 14:37:02 -0700, Mari <[email protected]> wrote:
    >>
    >> >If a cell is converted from a date format to text format, the date in that
    >> >cell is converted to a 5-digit number.
    >> >
    >> >I would like to know how excel calculates that number so that I can reverse
    >> >that formula to figure out what dates I originally had in a bunch of cells
    >> >which now have 5-digit numbers in them, without having to change each cell
    >> >back to date format.
    >> >
    >> >thanks,
    >> >
    >> >m-

    >>
    >>


    Gord Dibben MS Excel MVP

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1