Forum Discussion

eszterm's avatar
eszterm
Copper Contributor
Oct 12, 2023

Time format problem in index formula

Hi All,

I have to find/match numbers on two sheets.
1st sheet: what is the data for a time frame? (e.g. 02:05:00 - 02:05:59)
2nd sheet: has the exact date (e.g. 02:05:22) and the data (e.g. 11,2)

But the formula (index and match) sometimes gives me N/A. Mostly when the time frame has 01 in hours, or 03, 08 in minutes.
So I have data for this: 02:00:00 – 02:00:59 or 21:05:00 - 21:05:59
But I do not have data for this: 01:19:00 - 01:19:59 or 20:08:00-20:08:59

What is the solution / different formula for this? Could you please help me with that?

excel

8 Replies

Sort By
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Oct 12, 2023

        eszterm 

        Change the formula in C6 to

         

        =CONCATENATE(TEXT(B6,"hh:mm")," – ",TEXT(B6+TIME(0,1,0),"hh:mm"))

         

        (note that the dash is not the ordinary hyphen but the en-dash) and the formula in D6 to

         

        =INDEX(data!$B$2:$AE$1441,MATCH($C6,data!$A$2:$A$1441,0),MATCH($A6,data!$B$1:AE$1,0))

         

        The ,0 makes the matches exact.

         

Resources