+ Reply to Thread
Results 1 to 10 of 10

Date Column Modification

  1. #1
    Registered User
    Join Date
    05-28-2016
    Location
    london, england
    MS-Off Ver
    2007
    Posts
    5

    Date Column Modification

    Hi ..

    We have a lot of Excel Files - input by different users

    In order to get the Date Column in a uniform state ..

    I tried a formula - but it would not work on all variations of input - Column H in image.

    I posted on another Forum and was given a 'short' version of my formula [ which fixed one type of input - but un-did another ] Column J in image

    date image.JPG



    Looking for a [ non VBA ] solution - hopefully a single column formula - which can be used to replace existing input of dates.

    Wonder if this can be done ?
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    04-26-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 Apps for Business (v 2306 / 16.0) 64-bit
    Posts
    27

    Re: Date Column Modification

    Right click column A, click Format Cells, go to date and chose the format that suits you.


    Please let me know if this will not work

  3. #3
    Registered User
    Join Date
    04-26-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 Apps for Business (v 2306 / 16.0) 64-bit
    Posts
    27

    Re: Date Column Modification

    Oh... well I just actually looked at the data. I see you have ranges of dates there. I am not sure if that would be possible or not, at first I took it as a single date.

  4. #4
    Registered User
    Join Date
    05-28-2016
    Location
    london, england
    MS-Off Ver
    2007
    Posts
    5

    Re: Date Column Modification

    Hi KB

    The formats input & the required output are of mixed kind .. so no - I doubt this would do anything.

    Also - would not want to mess with the source [ input ] column .. just have a formula to output correctly.


    Cheers
    Harry

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Date Column Modification

    This seems to do most of what you are looking for. I combined the formula in J2 with a part that making use of the CELL function.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    1
    Date Input
    Len
    Text
    Numb
    DESIRED RESULT
    MY ORIGIONAL FORMULA
    YKY FORMULA
    FULL SOLUTION ?
    2
    Jun-79
    5
    FALSE
    TRUE
    JUNE 1979
    JUNE 1979
    01/06/1979
    June 1979
    3
    DEC 1980 - JAN 1982
    19
    TRUE
    FALSE
    DECEMBER 1980 - JANUARY 1982
    DECEMBER 1980 - JANUARY 1982
    DECEMBER 1980 - JANUARY 1982
    DECEMBER 1980 - JANUARY 1982
    4
    FEB 1975 TO APR 1978
    20
    TRUE
    FALSE
    FEBRUARY 1975 TO APRIL 1978
    FEBRUARY 1975 TO APRIL 1978
    FEBRUARY 1975 TO APRIL 1978
    FEBRUARY 1975 TO APRIL 1978
    5
    1980
    4
    FALSE
    TRUE
    1980
    1980
    1980
    1980
    6
    10/03/1988
    5
    FALSE
    TRUE
    10/03/1988
    MARCH 1988
    10/03/1988
    10/03/1988
    7
    10/3/88
    5
    FALSE
    TRUE
    10/03/1988
    MARCH 1988
    10/03/1988
    10/03/1988
    8
    N/A
    3
    TRUE
    FALSE
    N/A
    N/A
    N/A
    N/A
    9
    NO DATE
    7
    TRUE
    FALSE
    NO DATE
    NO DATE
    NO DATE
    NO DATE
    10
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  6. #6
    Registered User
    Join Date
    05-28-2016
    Location
    london, england
    MS-Off Ver
    2007
    Posts
    5

    Re: Date Column Modification

    Thanks newdoverman

    This is almost perfect ..

    I don't understand what the CELL function is doing .. this part ="D3" ? ?

    The only thing this formula does not do - is put the first cell in UPPER CASE JUNE 1979 not June 1979
    [ I tried adding UPPER - but getting error ]

    Could that part be corrected - by any chance ?

  7. #7
    Registered User
    Join Date
    05-28-2016
    Location
    london, england
    MS-Off Ver
    2007
    Posts
    5

    Re: Date Column Modification

    Sorry ...

    Fixed ... I was trying to wrap an IF statement in UPPER .. all I had to do was wrap the whole formula.

    Thanks Again

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Date Column Modification

    This will take care of the upper case requirement.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Here are the codes that CELL returns when using the FORMAT argument. The codes are within " ".
    General "G"
    0 "F0"
    #,##0 ",0"
    0.00 "F2"
    #,##0.00 ",2"
    $#,##0_);($#,##0) "C0"
    $#,##0_);[Red]($#,##0) "C0-"
    $#,##0.00_);($#,##0.00) "C2"
    $#,##0.00_);[Red]($#,##0.00) "C2-"
    0% "P0"
    0.00% "P2"
    0.00E+00 "S2"
    # ?/? or # ??/?? "G"
    m/d/yy or m/d/yy h:mm or mm/dd/yy "D4"
    d-mmm-yy or dd-mmm-yy "D1"
    d-mmm or dd-mmm "D2"
    mmm-yy "D3"
    mm/dd "D5"
    h:mm AM/PM "D7"
    h:mm:ss AM/PM "D6"
    h:mm "D9"
    h:mm:ss "D8"

  9. #9
    Registered User
    Join Date
    05-28-2016
    Location
    london, england
    MS-Off Ver
    2007
    Posts
    5

    Re: Date Column Modification

    PERFECT

    And thanks for the explanation.

    H

  10. #10
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Date Column Modification

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. view and modification my date
    By mak_pj in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-12-2013, 02:33 PM
  2. Last Cell Modification Date?
    By mapjoel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-29-2013, 10:49 AM
  3. [SOLVED] Automatic date modification
    By numbers&stuff in forum Excel General
    Replies: 3
    Last Post: 10-17-2012, 05:56 PM
  4. modification date for a file
    By neta in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-05-2009, 12:31 PM
  5. flie modification date
    By manuelitros in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-29-2008, 09:12 PM
  6. Date modification
    By Jon Knights in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-28-2007, 01:18 PM
  7. Last Modification Date
    By Hal in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-22-2006, 03:50 PM
  8. [SOLVED] Excel file modification date
    By GROSNER in forum Excel General
    Replies: 5
    Last Post: 03-03-2005, 10:06 PM

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