ExcelBanter - View Single Post

Claus Busch

Hi,

Am Thu, 29 Nov 2012 11:48:05 -0800 (PST) schrieb :

A (name) B (Target time) C (actual time)
1 John 16:30:00 16:35:00
2 Lenny 16:30:00 15:10:00
3 John 14:30:00 16:50:00
4 Lisa 16:00:00 16:50:00
5 John 15:30:00 17:00:00
6 John 16:30:00 13:00:00
....
....

3) How many times John is to late but between a set time frame:
3A) Up to 30 minutes late
3B) up to 60 minutes late

up to 30 minutes late:
=SUMPRODUCT(--(A1:A10="John"),--(C1:C10<=B1:B10+TIME(,30,)),--(C1:C10B1:B10))
more than 30 minutes up to 60 minutes:
=SUMPRODUCT(--(A1:A10="John"),--(C1:C10<=B1:B10+TIME(1,,)),--(C1:C10B1:B10+TIME(,30,)))
more than 60 minutes late:
=SUMPRODUCT(--(A1:A10="John"),--(C1:C10B1:B10+TIME(1,,)))

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2