+ Reply to Thread
Results 1 to 2 of 2

Convert two sets of date and time text back to a value

  1. #1
    Registered User
    Join Date
    05-06-2010
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003-2007
    Posts
    2

    Convert two sets of date and time text back to a value

    Hi all/anyone

    I'm trying to get a time value for how long a trip has taken from the start of the trip to the end of the trip.

    The values that are provided a text (i.e. DD/MM/YYYY hh:mm AM/PM) but I'm unable to convert them back to a value.

    Ideally I want to get the value into column J in the attachment and if possible also get the number of nights a trip has taken.

    Any assistance greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Convert two sets of date and time text back to a value

    Hi.

    =MMULT(0+SUBSTITUTE(SUBSTITUTE(E2:F2,"PM"," PM"),"AM"," AM"),{-1;1})

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

+ 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] Text to Date, Convert to US Format from UK, Eliminate Time
    By ExcelQuestFL in forum Excel General
    Replies: 11
    Last Post: 10-15-2013, 02:47 PM
  2. [SOLVED] Convert date and time into Diffrent text.
    By visha_1984 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-17-2013, 04:24 AM
  3. Convert Text Dates, Perform Calculation, Convert back to Text
    By Orada in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-13-2012, 05:25 PM
  4. Convert Text Values To Date and Time
    By aftabn10 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-10-2012, 09:50 AM
  5. Convert text to date time format
    By nnaidoo1972 in forum Excel General
    Replies: 1
    Last Post: 11-11-2011, 05:18 AM
  6. Convert date and time to a text string
    By matpaulin in forum Excel General
    Replies: 2
    Last Post: 09-20-2008, 02:12 PM
  7. [SOLVED] Convert date/time to text
    By Pam in forum Excel General
    Replies: 5
    Last Post: 06-12-2006, 02:10 PM
  8. How to convert DATE and TIME to text
    By ice_pack in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 09-06-2005, 07:05 PM

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