+ Reply to Thread
Results 1 to 11 of 11

convert text YEAR to a date

  1. #1
    Registered User
    Join Date
    10-15-2004
    Location
    California, USA
    MS-Off Ver
    2016
    Posts
    42

    convert text YEAR to a date

    Hi all,
    I thought this would be a straightforward exercise. I have a column that is a YEAR, but it's a GENERAL data type. I need to convert that to a date, basically the last date of the year, 12/31/2018.

    example; 2018 --> 12/31/2018

    I tried to use CONCANTENATE, but when I use the formula in the first cell, I grip it and copy all the way down the column it just copy's the same value, 12/31/2018.

    Thanks for any help,
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: convert text YEAR to a date

    Maybe
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    10-15-2004
    Location
    California, USA
    MS-Off Ver
    2016
    Posts
    42

    Re: convert text YEAR to a date

    Thanks TMS,
    But this is the same result that I've been getting. The column that has YEAR, goes from 2018 to 2000. I need to prepend ALL THOSE YEARS, with 12/31.

    This is tricky.

  4. #4
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,104

    Re: convert text YEAR to a date

    Try this in A2, and copy down:
    Please Login or Register  to view this content.
    Or just
    Please Login or Register  to view this content.
    and copy down.

    Pete
    Last edited by PeteABC123; 10-13-2020 at 12:12 PM.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: convert text YEAR to a date

    No, it isn't. You have
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    ... all constants.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    DOES prepend the month (12) and day (31) to each year (in column B) AND gives you a numeric date.

    When you copy the formula down, the cell reference, B2, will auto-adjust to B3, B4, B5 all the way down to B3146 (31/12/2000)

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    will prepend "12/31" to the value in column B, BUT will give you a TEXT value,

    With UK Regional Settings,
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    will also work but you'd need to format the cells as Date.
    Last edited by TMS; 10-13-2020 at 12:57 PM.

  6. #6
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: convert text YEAR to a date

    A2=EOMONTH(DATE($A2,12,1),0)

    Copy down

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: convert text YEAR to a date

    @CARACALLA: why would you use two functions to achieve what you can get with one?

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: convert text YEAR to a date

    And that looks like a circular function.

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: convert text YEAR to a date

    araki

    Is calculation set to Automatic or Manual?
    If posting code please use code tags, see here.

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: convert text YEAR to a date

    In the sample I opened, calculation was automatic, but that may have been just because my open files were set that way.

    However, note that the original formula in the sample did not contain any cell references.

  11. #11
    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: convert text YEAR to a date

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)
    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

+ 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. Replies: 7
    Last Post: 07-11-2019, 11:27 AM
  2. [SOLVED] Convert Text Year and Month to Date Format
    By Barieq in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-20-2017, 02:07 AM
  3. [SOLVED] how to convert text number to year month and date
    By leakhna in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-03-2015, 05:19 AM
  4. [SOLVED] Extract Text from String then Convert to a Date (Month/Year)
    By sgrey24 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-14-2013, 06:11 PM
  5. [SOLVED] Use TEXT function as Criteria on Sumifs to convert month/year to date format
    By chico.corrales in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-08-2012, 07:49 PM
  6. Convert date to month/year text
    By FindAnswers in forum Excel General
    Replies: 2
    Last Post: 11-07-2012, 03:42 PM
  7. [SOLVED] convert date in TEXT into year
    By Exxcel Noob in forum Excel General
    Replies: 4
    Last Post: 08-16-2012, 05:21 AM

Tags for this Thread

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