Results 1 to 8 of 8
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    EXTRACT ALWAYS (2000 sr 1)

    ... the newest date from a column
    In this file in column H is present a list of date i could want to extract the most newest date from this column.... in C2
    In this case s present only a date for every cells

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: EXTRACT ALWAYS (2000 sr 1)

    You could use =MAX(H:H) for the whole column or you can specify a part of the column like this =MAX(H7:H1000)

    There is a problem with your spreadsheet, the values shown in column H are not stored as dates, instead they are stored as text. To convert the text values to dates you can highlight the cells, in this case H7:H17 and then select "Data>Text to columns..." (Dati>Testo in colonne...). For the first 2 steps just press "Next". In step 3 the data preview box should only show 1 column like the attached image, if it is trying to split the data into more columns go back and change the options in the previous steps. On step 3 specify date format and click "Finish".

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXTRACT ALWAYS (2000 sr 1)

    ok! but this date is taken ( var_DATA )from a macro to import a txt file with this line:

    If InStr(Mid(riga, 1, 70), " TIPOLOGIA: GLOBALE SITUAZIONE AL") > 0 Then
    var_DATA = Mid(riga, 71, 10)
    'var_TIPO = Trim(Mid(riga, 36, 8 & "."))
    FASE = 1
    End If

    end put this in

    Foglio3.Range("H" & Trim(Str(n))).Value = var_DATA

    is possible to insert this formated option during the import?

  4. #4
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: EXTRACT ALWAYS (2000 sr 1)

    I am not an expert with VBA but try this on a test file. One of our VBA experts may have a better solution.
    var_DATA=DateSerial(Mid(riga,77,4),Mid(riga(74,2), Mid(riga(71,2))

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: EXTRACT ALWAYS (2000 sr 1)

    Without modifying the data you could use:
    =MAX(DATEVALUE(H7:OFFSET(H1,MATCH(REPT("z",255),H: H)-1,)))
    entered as an array formula (Ctrl-Shift-Enter)

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXTRACT ALWAYS (2000 sr 1)

    HUM...
    i have an error #valore
    If you like insert your formulas in the my sheet (C2) and reattache this, Tks for your help.
    Salvatore

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: EXTRACT ALWAYS (2000 sr 1)

    Hi Salvatore

    Your error #valore is because you entered the formula as an ordinary formula (Enter) instead of as an array formula (Ctrl-Shift-Enter).

    See attached update to your workbook.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXTRACT ALWAYS (2000 sr 1)

    Tks. for all.
    Sal

Posting Permissions

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