Formula Help

[rehberger]

Is it possible to write a formula that will return operating hours based on the data below? I would like the results to say reflect the open hours for example the deli results would say 8am-1pm

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]7am[/TD]
[TD]8am[/TD]
[TD]9am[/TD]
[TD]10am[/TD]
[TD]11am[/TD]
[TD]12pm[/TD]
[TD]1pm[/TD]
[TD]2pm[/TD]
[TD]3pm[/TD]
[TD]4pm[/TD]
[/TR]
[TR]
[TD]Deli[/TD]
[TD]closed[/TD]
[TD]open[/TD]
[TD]open[/TD]
[TD]open[/TD]
[TD]open[/TD]
[TD]open[/TD]
[TD]open[/TD]
[TD]closed[/TD]
[TD]closed[/TD]
[TD]closed[/TD]
[/TR]
[TR]
[TD]Buffet[/TD]
[TD]open[/TD]
[TD]open[/TD]
[TD]open[/TD]
[TD]open[/TD]
[TD]open[/TD]
[TD]open[/TD]
[TD]closed[/TD]
[TD]closed[/TD]
[TD]closed[/TD]
[TD]closed[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Results[/TD]
[/TR]
[TR]
[TD]Deli[/TD]
[TD]8am-1pm[/TD]
[/TR]
[TR]
[TD]Buffet[/TD]
[TD]7am-12pm[/TD]
[/TR]
</tbody>[/TABLE]

 

[AliGW]

How about this?

Excel 2016 (Windows) 32 bit
	D	E	F	G	H	I	J	K	L	M	N
15		7am	8am	9am	10am	11am	12pm	1pm	2pm	3pm	4pm
16	Deli	closed	open	open	open	open	open	open	closed	closed	closed
17	Buffet	open	open	open	open	open	open	closed	closed	closed	closed
18
19	Deli	8am-1pm
20	Buffet	7am-12pm
Sheet2

Cell Formulas
Range	Formula
E19	=INDEX(E$15:N$15,,MATCH("open",E16:N16,0))&"-"&INDEX(E$15:N$15,,MATCH("open",E16:N16))

 

[snjpverma]

Based on Ali's formula, try this

I think you will have to use the Text function or else it will give you decimal numbers.

=TEXT(INDEX(E$15:N$15,,MATCH("open",E16:N16,0)),"hh AM/PM")&"-"&TEXT(INDEX(E$15:N$15,,MATCH("open",E16:N16)),"hh AM/PM")

 

[AliGW]

Why complicate it? My formula does what is requested - it has been tested.

 

[snjpverma]

Ali, I tried but it didn't work unless i give it a format using text function. So I thought of suggesting OP of the same.

 

[AliGW]

Well, it depends how you think it is set up. It looks as if you are assuming the the opening times across the top are times with a custom format, but I am assuming that they are just text (e.g. "7pm", etc.). We'll have to wait and see.

 

[snjpverma]

Ohh yes. This is exactly why I had to use the text function. I must say "Good catch".

 

[rehberger]

Thank You
this works great. I do just have the time as text

 

[snjpverma]

rehberger, thanks for the update

 

[AliGW]

You're welcome!