+ Reply to Thread
Results 1 to 8 of 8

convert date column to general value

  1. #1
    Registered User
    Join Date
    10-02-2003
    Posts
    80

    Unhappy convert date column to general value

    I am trying to upload excel data to a databse which only accepts general values. Some one tried to help me with formulas as per attached word document but it is not working. I get #value as result. (cell colored red)

    need to Import this Excel sheet into an AMS Database system - All Cells NEED "general" value even if its numbers.

    Tried doing manually but main sheet has 3500 entries...:-(

    I am sure there has to be easier way to do this.

    Your help is higly appreciated

    Thanks much

    vikkam confused:
    Attached Files Attached Files
    vikkam

  2. #2
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: convert date column to general value

    Try

    Please Login or Register  to view this content.
    and copy down


    Or better way, copy an emty cell, then select entire column with dates then right click > paste special > add > ok
    Then apply dd mm yy format to these values
    Last edited by contaminated; 01-08-2010 at 02:13 AM.
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, ?Born in USSR?
    Vusal M Dadashev

    Baku, Azerbaijan

  3. #3
    Registered User
    Join Date
    10-02-2003
    Posts
    80

    Re: convert date column to general value

    Tried =datevalue(D2)
    but it gives me value as 1

    Is it possible to do it in one cell and show as not clear.

    Thanks for prompt response.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: convert date column to general value

    What is the default date format of your locale - dd/mm or mm/dd ?

    There are inconsistencies in the dates - some are listed as mm/dd/yyyy and others as dd/mm/yyyy (this precludes use of Text to Columns conversion unfortunately)

    Assuming default date format of dd/mm/yyyy then perhaps:

    Please Login or Register  to view this content.
    However... there are still some invalid entries to account for (00/00/0000, 00/00/001 etc...) - should these be 0 or something else ?

    On an aside 1 is the correct DateSerial for Jan 1 1900 on a 1900 Date System.

  5. #5
    Registered User
    Join Date
    10-02-2003
    Posts
    80

    Re: convert date column to general value

    copied the formule but no results are displayed.
    Is it possible to do on one cell in spread sheet ad show ?

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: convert date column to general value

    please post your version with suggested formulae in place.

  7. #7
    Registered User
    Join Date
    10-02-2003
    Posts
    80

    Re: convert date column to general value

    want it to read once exported - 12/10/2009 - not the 27431..and so on

  8. #8
    Registered User
    Join Date
    10-02-2003
    Posts
    80

    Re: convert date column to general value

    Basically I am trying to write dat in same format mm/dd/yyyy but as text.

+ 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