Forum Discussion

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...

Copper Contributor

Oct 12, 2023

Here is the link:

https://1drv.ms/x/s!AhR-IjyrXUwUgiscT40_8dXE1a9F?e=cO3W8y

Thanks.

MVP

Oct 12, 2023

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.

eszterm
Copper Contributor
Oct 13, 2023
HansVogelaar

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.
https://1drv.ms/x/s!AhR-IjyrXUwUgiscT40_8dXE1a9F?e=xsFeKC
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
- HansVogelaar
  MVP
  Oct 13, 2023
  eszterm
  
  Here is the workbook with the changes that I proposed.
  
  I removed the apostrophe from B6.
  Sample_for_time_index_problem 3.xlsx5.8 MB
  - eszterm
    Copper Contributor
    Oct 13, 2023
    HansVogelaar
    
    I think the problem is with my system time settings... I use Hungarian and if I switch to English (US), it works. But the problem is, everybody else use Hungarian in the office so they cannot see the working formula.
    I will try to find some solution for it, but I really appreciate your help!!!
    Thank you!

Resources