+ Reply to Thread
Results 1 to 3 of 3

Convert text to time and vlookup getting error.

Hybrid View

  1. 03-23-2015, 08:59 PM #1
    JuauM
    JuauM is offline
    Registered User
    Join Date
    02-12-2015
    Location
    Japan
    MS-Off Ver
    2013
    Posts
    25

    Convert text to time and vlookup getting error.

    Hi, I'm working with some data and need a help to convert a text to time then use the time to vlookup on another sheet.

    I'm attaching a sample file to explain where I have issues.

    the gray columns are the data I receive to work,the M and O columns are the text that I converted to time in the columns R and X.

    I need to use the converted data to vlookup that time in the sheet1, but I'm getting #N/A or inaccurate lookup values.

    What can I do to solve this problem?

    Thanks in advance and sorry about my bad english
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-24-2015, 12:26 AM #2
    NeedForExcel
    NeedForExcel is offline
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: Convert text to time and vlookup getting error.

    Try adding +0 at the end to the formulas and see if it works.. It should do the job..

    Cheers!
    Deep Dave
    Reply With QuoteRegister To Reply
  3. 03-24-2015, 12:38 AM #3
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,918

    Re: Convert text to time and vlookup getting error.

    I'm not sure what your vlookup is doing but I used:
    =IFERROR(TIME(INT(L2/100),MOD(L2,100),0),0)
    to convert text to time in columns Q, R, W & X
    Attached Files Attached Files
    Ben Van Johnson
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Time stored as text. How can I convert to data and time?
    By matthewbutterworth in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-07-2013, 10:19 AM
  2. [SOLVED] convert text to time
    By victoria74 in forum Excel General
    Replies: 7
    Last Post: 04-03-2012, 08:34 AM
  3. Convert Text to time value
    By Hillheader in forum Excel General
    Replies: 2
    Last Post: 03-02-2006, 04:50 PM
  4. How do I convert text to time
    By Curtis in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-09-2006, 03:35 PM
  5. [SOLVED] convert time imported as text to time format for calculations
    By batfish in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-27-2005, 07:05 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1