Finding a DATE in a range of TEXT

  • Hi, I am new to posting to this forum so thanks in advance!


    I am trying to use INDEX MATCH to find a value in a table based on a date. The table that I am search is from an external system and the dates all came across as text (left aligned in the cell). If I hit enter on the date cell, the correct value will return. The issue is that I am pulling 3 years worth of data and I dont want to hit enter on every date. Is there a way to use datevalue on a range or another method to use?


    C19 is my date
    $J$2:$CD$2 is my range of "dates" stored as text.


    =(INDEX($J$5:$ES$16,MATCH($C22,$A$5:$A$14,0),MATCH($C$19,$J$2:$CD$2,0)))


    Thanks!

  • Re: Finding a DATE in a range of TEXT


    Hi tkaz4


    Code
    1. Welcome to the forum

    .


    I assume you are trying to do look up of your date. Try below


    MATCH(DateValue($C22),$A$5:$A$14,0)


    Otherwise post a sample workbook.



    Regards,



    Makbool

  • Re: Finding a DATE in a range of TEXT


    Hi All,


    I am a newbie and this site is just wonderful. Apologies as I posted in this thread bec I seem to think i have a similar but slightly different scenario.


    Spending already 2 days in excel and still cannot seem to arrive at a correct formula for my needs, tho. I have a date and time in A1 which I need to test to return a new date and time value or retain its value. Test parameter is simple, if A1's time is outside 9PM to 6AM, date should change to next workday with time set at 9PM. If A1's time falls between 9PM-6AM, all is good and no change is necessary.


    I have tried a couple of formulas from IF AND, tried separating the date from the time, nested it with Workday, etc, but I still cannot get it. :(


    Hope someone can help.


    [ATTACH=CONFIG]70104[/ATTACH]


    Cheers,
    C


    [hr]*[/hr]


    Requested new thread opened: http://www.ozgrid.com/forum/showthread.php?t=201020

  • Re: Finding a DATE in a range of TEXT


    Hi Maqbool,


    Please see attached. Basically, help I am looking for are the following:


    1. If E2 is a workday and time falls 9AM-6PM, retain value in D2
    2. If E2 is a workday but time falls before 9AM, retain date but move time to 9AM
    3. If E2 is a workday but time falls after 6PM, move date and time to next workday at 9AM
    4. If E2 is not a workday, move to the next workday at 9AM


    I know I am missing on something :(, any help will be appreciated.



    Thanks,
    C
    ozgrid.com/forum/core/index.php?attachment/70108/

    Files