+ Reply to Thread
Results 1 to 7 of 7

Combine Date, time and AM/PM columns

  1. #1
    Registered User
    Join Date
    10-14-2015
    Location
    Perth
    MS-Off Ver
    2010
    Posts
    58

    Combine Date, time and AM/PM columns

    Hi all

    I am having an issue with a formula.

    I have inherited some raw data where the date (MM/DD/YYYY), time, and AM/PM values have all been split into three separate columns.

    This would be an easy fix if I was concatenating general cells. However because I am combining date and time Excel cannot determine whether or not the time is an AM or PM. I would also like to switch the format of the date column into DD/MM/YYYY, currently it is MM/DD/YYYY.

    I have attached an example with values and an expected outcome.

    Thank you !
    Attached Files Attached Files

  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,810

    Re: Combine Date, time and AM/PM columns

    https://theexceltrainer.co.uk/conver...xt-to-columns/
    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
    Registered User
    Join Date
    10-14-2015
    Location
    Perth
    MS-Off Ver
    2010
    Posts
    58

    Re: Combine Date, time and AM/PM columns

    Hi Alan

    Thanks for your response. Unfortunately this methoid does not swap from MM/DD to DD/MM.

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Combine Date, time and AM/PM columns

    Hope below formula helps

    Please Login or Register  to view this content.
    Quang PT

  5. #5
    Registered User
    Join Date
    10-14-2015
    Location
    Perth
    MS-Off Ver
    2010
    Posts
    58

    Re: Combine Date, time and AM/PM columns

    Hi bebo

    You are nearly there!

    The only issue I am running into is how the formula treats any time startingon the 12th hour (midnight AM). Your formula is displaying this as a PM value rather than AM value, any ideas?

    Thank you

  6. #6
    Registered User
    Join Date
    10-14-2015
    Location
    Perth
    MS-Off Ver
    2010
    Posts
    58

    Re: Combine Date, time and AM/PM columns

    Hi again

    In order to solve the issue with the midnight hour be incorrectly converted to a PM I have added an additional calculation as follows,

    =IF(AND(HOUR(B2)=12,C2="am"),E2-0.5,E2)

    Thanks

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Combine Date, time and AM/PM columns

    Please Login or Register  to view this content.
    custom format: DD/MM/YYYY hh:mm
    Ben Van Johnson

+ 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] Combine A Date And A Time To Create A Unique Date
    By Jenn68 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-18-2019, 06:58 AM
  2. [SOLVED] combine separate date and time columns into a single column
    By jbporcher in forum Excel General
    Replies: 7
    Last Post: 05-17-2018, 12:50 PM
  3. Replies: 7
    Last Post: 06-19-2017, 06:10 PM
  4. Date & Time combine
    By thong127 in forum Excel General
    Replies: 9
    Last Post: 03-04-2014, 02:33 PM
  5. combine data associated with multiple date/time columns
    By dannybten23 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-10-2013, 05:11 PM
  6. Combine 2 Date + Time Columns?
    By gkk2001@alltel. in forum Excel General
    Replies: 2
    Last Post: 03-26-2009, 02:49 AM
  7. Combine date and time
    By BN-CD in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-22-2006, 11:31 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