+ Reply to Thread
Results 1 to 7 of 7

Re-formatting data to use DATE-characteristics?

  1. #1
    Registered User
    Join Date
    08-08-2006
    Posts
    6

    Question Re-formatting data to use DATE-characteristics?

    This is my first post, so you can clearly see that I am an Excel novice. I received over 1000 rrithdates in a spreadsheet, coded as dd.mm.yyyy, e.g. "20.10.1956". Unfortunately, this is one format excel does not recognize as a date. So it does not assign a number as it usually does. As a result, my simple formula (=(TODAY()-A2)/365.25) only produces an error.
    I have tried re-formatting the data to dates, but it still only shows the original entry, not the number when I go on general.
    Is there any way to get excel to understand that this is a date, or do I have to key them in again?

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097

    Thumbs up

    Hi dune2,

    Your date appears to be formatted to text, so if your date is in A1, try this in B1

    =ABS(A1), then format to date

    oldchippy

  3. #3
    Niek Otten
    Guest

    Re: Re-formatting data to use DATE-characteristics?

    =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))
    or, if 1.10-1956 occurs also:
    =DATE(RIGHT(A2,4),MID(A2,IF(LEN(A2)=9,3,4),2),LEFT(A2,IF(LEN(A2)=9,1,2)))
    I hope you don't have 10.1.1956 but if you do, post again in this thread

    --
    Kind regards,

    Niek Otten
    Microsoft MVP - Excel

    "dune2" <[email protected]> wrote in message
    news:[email protected]...
    |
    | This is my first post, so you can clearly see that I am an Excel novice.
    | I received over 1000 rrithdates in a spreadsheet, coded as dd.mm.yyyy,
    | e.g. "20.10.1956". Unfortunately, this is one format excel does not
    | recognize as a date. So it does not assign a number as it usually does.
    | As a result, my simple formula (=(TODAY()-A2)/365.25) only produces an
    | error.
    | I have tried re-formatting the data to dates, but it still only shows
    | the original entry, not the number when I go on general.
    | Is there any way to get excel to understand that this is a date, or do
    | I have to key them in again?
    |
    |
    | --
    | dune2
    | ------------------------------------------------------------------------
    | dune2's Profile: http://www.excelforum.com/member.php...o&userid=37228
    | View this thread: http://www.excelforum.com/showthread...hreadid=569392
    |



  4. #4
    Registered User
    Join Date
    08-08-2006
    Posts
    6
    Thanks Chippie, but this only produced further errors. I think the dots are throwing of this function. I agree that the data is probably formatted as text. But why can't I change it? Anything else I can try?

  5. #5
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Hi dune2,

    Sorry about that, Neik's first formula works for me though

    oldchippy

  6. #6
    Guest

    Re: Re-formatting data to use DATE-characteristics?

    Hi

    Select your column, then go to Data/Text to columns. On the third page (I
    think) in the top-right corner is an option to tell Excel that the cells are
    dates. Finish it off and the job's done. Make a backup before you start.

    Hope this helps.
    Andy.

    "dune2" <[email protected]> wrote in
    message news:[email protected]...
    >
    > This is my first post, so you can clearly see that I am an Excel novice.
    > I received over 1000 rrithdates in a spreadsheet, coded as dd.mm.yyyy,
    > e.g. "20.10.1956". Unfortunately, this is one format excel does not
    > recognize as a date. So it does not assign a number as it usually does.
    > As a result, my simple formula (=(TODAY()-A2)/365.25) only produces an
    > error.
    > I have tried re-formatting the data to dates, but it still only shows
    > the original entry, not the number when I go on general.
    > Is there any way to get excel to understand that this is a date, or do
    > I have to key them in again?
    >
    >
    > --
    > dune2
    > ------------------------------------------------------------------------
    > dune2's Profile:
    > http://www.excelforum.com/member.php...o&userid=37228
    > View this thread: http://www.excelforum.com/showthread...hreadid=569392
    >




  7. #7
    Registered User
    Join Date
    08-08-2006
    Posts
    6

    Thumbs up

    @Niek Otten: THANKS!!!

    That formula worked like a charm! Good stuff!

+ 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