+ Reply to Thread
Results 1 to 8 of 8

CONCATENATE Day column, month column, and year column to make a date column

  1. #1
    Registered User
    Join Date
    08-09-2015
    Location
    Kabul, Afghanistan
    MS-Off Ver
    2013
    Posts
    2

    Angry CONCATENATE Day column, month column, and year column to make a date column

    Hi All,
    This is my first time posting here, but I really would like to seek a guru who might be able to help me.

    I have 3 columns that tell me the date of the record. Which is great when I just want to sort my month. However if I want to use excel timelines or manipulate the date, then I need to somehow create a date column as Excel doesn't see the 3 columns as a date field

    YEAR MONTH DAY
    2015 8 9
    2015 7 8

    Therefore, with me trying to be clever, I create a new column and CONCATENATE the data
    E.g.
    =CONCATENATE(F4,"/",E4,"/",D4)

    I get the following result:

    9/8/2015
    8/7/2015


    HOWEVER - my problem. Excel doesn't recognise the change when I "format cell", and change the category from "general" to "date." **

    What am I doing wrong... is there an easier way of
    A) - getting Excel to recognise 3 separate columns combined as a date format?
    B) - getting my CONCATENATE data category to be recognised as a date format by Excel.

    ** I also tried to copy the CONCATENATE data and just paste value without success.

    Please also note I will continue to receive data in this 3 column date format every week with a few thousand lines of data, so I am also looking at a method of how to convert this going forward.


    Your help, suggestions, guidance will be very much appreciated.

    Thanks

    Peter

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    12,915

    Re: CONCATENATE Day column, month column, and year column to make a date column

    Try
    Please Login or Register  to view this content.
    Let me know if you have any questions.

  3. #3
    Valued Forum Contributor HSV's Avatar
    Join Date
    02-12-2011
    Location
    The Netherlands
    MS-Off Ver
    Excel 2003 / 2007
    Posts
    763

    Re: CONCATENATE Day column, month column, and year column to make a date column

    Hello,

    Try;

    Please Login or Register  to view this content.
    Edit:
    too late
    Kind regards, Harry.

  4. #4
    Registered User
    Join Date
    08-09-2015
    Location
    Kabul, Afghanistan
    MS-Off Ver
    2013
    Posts
    2

    Re: CONCATENATE Day column, month column, and year column to make a date column

    Dear JeteMC and HSV, Thank you so much!

    Now for the bonus (sorry for being cheeky). I also have two other columns of data - Hour and AM/PM.
    e.g.
    HOUR AM/PM
    1 AM
    1 PM
    2 AM

    Is there a way of getting excel to recognise these two columns as a time category or does it need to be linked to the date fields that you just helped me with?

    Thanks

    Peter

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

    Re: CONCATENATE Day column, month column, and year column to make a date column

    Try this.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by Norie; 08-09-2015 at 03:15 PM.
    If posting code please use code tags, see here.

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.54 for Mac (home)
    Posts
    7,369

    Re: CONCATENATE Day column, month column, and year column to make a date column

    assuming your hours are in G and am/pm is in H, this will work for you...
    =--(E4&"/"&F4&"/"&D4&" "&G4&" "&H4), then format as date 3/14/01 1:30 pm from the format list.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  7. #7
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,403

    Re: CONCATENATE Day column, month column, and year column to make a date column

    Hi,

    If you want your times separately from the dates, you can use the 'TIME' function to join your hours and AM/PM in a similar way to how you used the 'DATE' function above. Where DATE expects year,month,day - TIME expects hour,minute,second. You need to know that Excel treats hours as fractions of a day - so 0.5 formatted as time equals 12:00 noon, whilst 0.75 equals 6:00pm, etc.

    If your hours are all whole hours, you can use this formula (assuming the hours are in A and AM/PM in B:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The first two bits of the formula (with the ANDs) are needed to deal with 12AM being midnight and 12PM being noon.
    If your hours aren't whole hours, you'll need to do some calculations on them to extract the minutes/seconds before inputting those to the TIME function. As I don't know exactly how your times are recorded, I haven't worked on that - but if you need more help, just ask.

    Regards,
    Aardigspook

    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
    You don't need to give me rep if I helped, but thanks are appreciated.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,389

    Re: CONCATENATE Day column, month column, and year column to make a date column

    Or perhaps this (building on Jete)...
    =DATE(F1,G1,H1)--(I1&" "&J1)
    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

+ 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: 6
    Last Post: 06-13-2016, 11:23 AM
  2. combining month, date and year into a single column
    By Yogarajah in forum Excel General
    Replies: 6
    Last Post: 08-05-2014, 03:18 AM
  3. Replies: 1
    Last Post: 04-10-2014, 04:29 AM
  4. Replies: 3
    Last Post: 01-17-2013, 07:20 PM
  5. Replies: 5
    Last Post: 01-16-2013, 08:42 PM
  6. Counting month and year from a date column
    By RamboDanbo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-15-2008, 04:26 PM
  7. [SOLVED] make a Year to date column
    By Nick in forum Excel General
    Replies: 3
    Last Post: 04-20-2005, 02:06 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