Formula Based on Multiple Criteria

[kumara_faith]

Hi,

I have the following sheet that tracks system downtime at outlets.Column G to T is the business opening and closing hours. I need to tag as Yes or No in column U if the start or end time for the downtime is within the business hours of the outlets.

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
2	*	Downtime				Monday	Monday	Tuesday	Tuesday	Wednesday	Wednesday	Thursday	Thursday	Friday	Friday	Saturday	Saturday	Sunday	Sunday	*
3	Location	Start Date	Start Time	End Date	End Time	Start Time	End Time	Start Time	End Time	Start Time	End Time	Start Time	End Time	Start Time	End Time	Start Time	End Time	Start Time	End Time	Affected
4	Tesco Kajang Sales Counter	2/14/2011	10.05 AM	2/14/2011	11.09 PM	9.30 AM	10.30 PM	9.30 AM	10.30 PM	9.30 AM	10.30 PM	9.30 AM	10.30 PM	9.30 AM	11.00 PM	9.30 AM	11.00 PM	9.30 AM	10.30 PM	No
5	Concept-BHN Sek. U3 Shah Alam (Subang Perdana)	2/20/2011	1.06 PM	2/21/2011	12.57 PM	9.00 AM	7.00 PM	9.00 AM	7.00 PM	9.00 AM	7.00 PM	9.00 AM	7.00 PM	9.00 AM	7.00 PM	9.00 AM	7.00 PM	9.00 AM	7.00 PM	#N/A
6	Concept-BHN Giant Bukit Tinggi	2/20/2011	10.32 PM	2/21/2011	8.39 AM	10.00 AM	10.00 PM	10.00 AM	10.00 PM	10.00 AM	10.00 PM	10.00 AM	10.00 PM	10.00 AM	10.00 PM	10.00 AM	10.00 PM	10.00 AM	10.00 PM	#N/A
Downtime (2)

Th answer for the all the above should be Yes.

Is there a way to amend the above formula to get it right ?

I have a cross post here:

http://www.excelforum.com/excel-general/765165-formula-based-on-multiple-criteria.html

 

[jim may]

I'd Suggest a User Defined Function (VBA Custom Write).

 

[baodinh]

You need to format the time in time format eg: 10:05 AM, which Excel stores as a value 0.420138888888889. You then need to create a lookup table with a unique lookup code for opening time and closing time for each location and weekday. Then use a simple VLOOKUP formula to compare for the corresponding weekday if the start of the outage is before the closing time and the end of the outage is after the closing time, then "YES". You may need an additional check where the outage is across weekdays. Hope this points you in the right direction. If not, ask for more details and I'll see if I have the time to post a detailed solution.

 

[kumara_faith]

Hi baodinh,

My apologies. My skills at formula is very basic.I got the existing formula from the forum. I am not really sure how to create this formula. I hope you could provide some examples on how I can do this.

Appreciate your valuable time and effort.

 

[kumara_faith]

bump

 

[kumara_faith]

bump.

 

[kumara_faith]

bump.

 

[T. Valko]

The question you're asking certainly doesn't match the complexity of those formulas.

If your question is asked correctly then all you need to do is test that the start and end of the down time is >=store open time and <=store close time.

With that being said why should the result for row 6 be Yes?

The start of the down time is 10:32 PM which is past the store close time of 10:00 PM and the end of the down time is 8:39 AM which is before the store open time.

 

[kumara_faith]

Dear #Name?,

You are correct. My apologies.

The answer for row 6 should be No.

Is there a way to amend the formula to populate the correct answer ?

 

[T. Valko]

Dear #Name?,

You are correct. My apologies.

The answer for row 6 should be No.

Is there a way to amend the formula to populate the correct answer ?

Try this...

Entered in U4 and copied down to U6:

=IF(OR(AND(D4>=MIN(G4:T4),D4<=MAX(G4:T4)),AND(F4>=MIN(G4:T4),F4<=MAX(G4:T4))),"Yes","No")