+ Reply to Thread
Results 1 to 6 of 6

Year in Column different making all same year

  1. #1
    Registered User
    Join Date
    04-10-2008
    Posts
    3

    Wink Year in Column different making all same year

    I am trying to figure out how to change the Year in date format so the year in different rows will reflect the same year on all dates so it can be alphabetise.

    Example 5/12/2012 Joe Doe 1000 Drury Lane
    7/12/2014 Lee Jone 1200 lee
    8/15/2015 Mark Jones 1200 Dotsy
    9/19/2016 Jame Drake 1500 Morgan

    would like to change the years to 2018 in all dates above so the other columns with information can be alphabetize correctly instead it breaking them down into years and alphabetizing?

    Results to show
    5/12/2018 Joe Doe 1000 Drury Lane
    7/12/2018 Lee Jone 1200 Lee
    8/15/2018 Mark Jones 1200 Dotsy
    9/19/2018 James Drake 1500 Morgan

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Year in Column different making all same year

    Assuming that the dates are in Column A starting in Row 1, the following VBA will do what you want.

    Please Login or Register  to view this content.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Year in Column different making all same year

    If you dont want to use VBA, put this in a helper column and copy down as needed - it will do what you want, then just sort on the helper...
    =DATE(2018,MONTH(A1),DAY(A1))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    04-10-2008
    Posts
    3

    Re: Year in Column different making all same year

    Thank you for helping as I have tried several ways and none of them worked.
    DonS

  5. #5
    Registered User
    Join Date
    04-10-2008
    Posts
    3

    Re: Year in Column different making all same year

    Thank you for the reply, I have been trying several ways to do it and did not come up with a solution.
    DonS

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Year in Column different making all same year

    Was that to both Alan's and my suggestions?

    My suggestion requires the use of a helper column to work, you cannot do it the cell containing the dates

+ 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. [SOLVED] Place Year 1, Year 2, etc. of revenue in proper date column on cash flow forecast
    By Rzzberry in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-31-2017, 01:19 PM
  2. Replies: 4
    Last Post: 08-05-2015, 06:39 PM
  3. Automatically update year interval cycles from year to year
    By trumptight in forum Word Programming / VBA / Macros
    Replies: 8
    Last Post: 08-21-2014, 10:38 PM
  4. [SOLVED] replace year in a date with year in another column
    By kuntalnr in forum Excel General
    Replies: 3
    Last Post: 06-17-2014, 12:05 PM
  5. Replies: 3
    Last Post: 09-04-2013, 10:49 AM
  6. If Column A Month and Year = ColumnB Todays Month and Year then send email
    By HACCStaff in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-15-2013, 12:47 AM
  7. Replies: 3
    Last Post: 12-30-2010, 07:24 AM

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