+ Reply to Thread
Results 1 to 8 of 8

date field problem

  1. #1
    zevs
    Guest

    date field problem

    From our invoicing program I exported some data in excel file. All fields seems to be in general format so I had to convert column which contains prices in numbers. OK. But the problem is in the column which contains dates.

    Dates are exported in the following way: D.M.YYYY (example: 1.2.2007). To be sure that in this column dates are in the proper format (and I know they aren't because I tried to manage the data with PivotTable) I changed the format of the columns to display dates in the following way: D. MONTH YYYY (example: 1. February 2007). And after that nothing happened!! "Dates" remained as they were before: 1.2.2007

    BUT!! When I selected (clicked on) one of dates, clicked in the formula bar and pressed Enter, the date got the right format: 1. February 2007 . Boh?

    My questions are: how can I change all dates in the same time in the proper format with ought clicking on each date and in formula bar clicking enter (after the column is changed in date format)?

    Thanks in advance!

  2. #2
    Forum Contributor
    Join Date
    09-04-2006
    Location
    Yorkshire, England
    Posts
    267
    Have you got calculations on?

    Tools > Options > Calculation (tab) and select automatic or press f9 to calculate once.
    JR
    Versions
    Mac OS X 'Leopard'
    Mac MS Office Excel 2004
    Windows XP
    MS Excel 2002
    MS Excel 2003

  3. #3
    zevs
    Guest
    yes, it's on. And also, if I manually type some dates in the same document everything works well.

  4. #4
    Forum Contributor corinereyes's Avatar
    Join Date
    12-02-2003
    Location
    Philippines
    MS-Off Ver
    MS Excel 2010
    Posts
    509
    Hi zevs,

    I experienced it also before, when the data is coming from a database, returning it as .csv file.

    What i did is:

    A1= 1.2.2007, b1 =value(A1) 'it may return the serial number of the date, then format it to say DD MONTH YYYY .

    Hope that will work.
    Corine

  5. #5
    zevs
    Guest
    thanks, with your method it works well.
    mah really strange issue.

  6. #6
    Forum Contributor corinereyes's Avatar
    Join Date
    12-02-2003
    Location
    Philippines
    MS-Off Ver
    MS Excel 2010
    Posts
    509
    Hi zevs,

    strange it is... maybe the cell needed to be refreshed or something before it'l display the correct format.

    glad it worked for you.

  7. #7
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    The "dates" are probaly text formatted, you can probably convert to actual dates using Text to columns.

    Select column of dates then:

    Data > Text to columns > Next > Next > under "column data format" select "date" and DMY > Finish

  8. #8
    zevs
    Guest

    daddylonglegs thanks

    Now it works. thanks!!

+ 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