Forum Discussion
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...
eszterm Could you post a sample workbook by any chance? Or share a link to one?
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.
I've tried your formula, but now all the hours are just 01 and doesn't even add the + minute to it. Also it thinks the seconds are the minutes in the formula. I understand the formula but honestly don't get it, what is the problem.
TX Date New start time Time frame AMR% 2023.09.02 22:27:35 01:35 – 01:35 #N/A 2023.09.03 01:19:19 01:19 – 01:19 #N/A 2023.09.03 19:57:42 01:42 – 01:42 #N/A
