+ Reply to Thread
Results 1 to 7 of 7

Concatenate Month and Year (But not Day!) columns into a single column containing the date

  1. #1
    Registered User
    Join Date
    06-27-2009
    Location
    Miami, FL
    MS-Off Ver
    Excel 2010
    Posts
    7

    Concatenate Month and Year (But not Day!) columns into a single column containing the date

    This is probably quite simple, but I'm not sure how to solve it:

    I have a sheet with a month column (e.g. 3) and year column (e.g. 2001). I'd like to combine the two into a single column reading 3/2001, 3-2001, or any standard date format. I've tried to use the DATE function, but it seems to insist on a "DAY" input.

    I'd appreciate any advice. Thanks!

  2. #2
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Concatenate Month and Year (But not Day!) columns into a single column containing the

    Hi panaceus,

    Say A2 contains 3 and B2 contains 2001...

    If you just need a text version, then simply use =A2&"/"&B2
    or =TEXT(DATE(B2,A2,1),"m/yyyy")

    If you want it to read as a date, then use =DATE(B2,A2,1) then format your date as m/yyyy

  3. #3
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Concatenate Month and Year (But not Day!) columns into a single column containing the

    You can use the date function and give day 1 in your formula. Then you change the format of those cell to show only month and year. A format like mm-yyyy will do it.
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

  4. #4
    Registered User
    Join Date
    06-27-2009
    Location
    Miami, FL
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Concatenate Month and Year (But not Day!) columns into a single column containing the

    Thanks guys, that's perfect!

  5. #5
    Registered User
    Join Date
    06-13-2016
    Location
    Cambridge, England
    MS-Off Ver
    2007
    Posts
    4

    Re: Concatenate Month and Year (But not Day!) columns into a single column containing the

    I have used the formula =DATE(A2,B2,1)as recommended above and it works perfectly. The issue I have is that where there is no month or year I would like the formula to return blank. At present I am getting #VALUE!. I have tried to use ISNA, ISBLANK and IF <> " ", but clearly I am not putting them in the right place as I can't make the formula work so that it returns a date where the reference cells have values but a blank if the reference cells are blank. Advice would be gratefully received.

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Concatenate Month and Year (But not Day!) columns into a single column containing the

    Try
    =IFERROR(DATE(A2,B2,1),"")

  7. #7
    Registered User
    Join Date
    06-13-2016
    Location
    Cambridge, England
    MS-Off Ver
    2007
    Posts
    4

    Re: Concatenate Month and Year (But not Day!) columns into a single column containing the

    Perfect! Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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