Identifying which date range includes a particular hour.

[AntBlabby8]

I have a table of data for hours worked, beginning time and ending time. Many overlap. I need to create a table that counts the number of people at work, at any given hour of the day, in one hour intervals, e.g. 9am-10am, noon-1pm, 10pm-11pm, etc. I thought I could add columns across the raw data, with each column header representing one hour segment. But I'd need to have a formula for each individual that looks at the range of the hours worked (e.g., 8:00am-Noon, Noon-8pm, etc.) and then to lookup a a time at the top of the column fell within the range of their start and finish time and says True or false. This is really a thorny one, and I'm going to have to do it every week for 1800 people!!! Help!

Name

Start Time

End Time

12:01am-1:00am

1:01am-2:00am

2:01am-3:00am

3:01am-4:00am

4:01am-5:00am

5:01am-6:00am

6:01am-7:00am

7:01am-8:00am

8:01am-9:00am

9:01am-10:00am

10:01am-11:00am

Amy

11:00:00 AM

7:30:00 PM

Bill

6:00:00 AM

2:30:00 PM

Cathy

9:00:00 AM

7:30:00 PM

Donald

5:00:00 AM

2:00:00 PM

Edward

5:00:00 AM

4:00:00 PM

Frank

5:00:00 AM

9:00:00 AM

George

5:00:00 AM

4:00:00 PM

Harold

12:30:00 AM

8:00:00 AM

Isaac

1:00:00 PM

9:00:00 PM

Jim

2:00:00 PM

10:00:00 PM

Kimberly

8:00:00 AM

4:00:00 PM

Lawrence

2:00:00 PM

10:00:00 PM

Martha

11:30:00 AM

10:30:00 PM

 

[Gimics]

Hey AntBlabby,

Can you separate your header row into two rows? Or just use a single value to either represent the start time or the end time?

This would make the formula much easier as you could just check if the time is before or after your starting/finishing times for each person.

 

[AntBlabby8]

Hey AntBlabby,

Can you separate your header row into two rows? Or just use a single value to either represent the start time or the end time?

This would make the formula much easier as you could just check if the time is before or after your starting/finishing times for each person.

Yes to the second, I believe. Not so much the first.

 

[Gimics]

That should work for you - do you want a walkthrough or are you going to give it a shot?

Should be as easy as changing the times across the top and then just using "IF" formulas to add something like a "Y" or a 1 to each cell.

If you use a "Y" you can "COUNTA" or "COUNTIF" the number of times the "Y" is there.
If you use a 1 you can SUM them.

 

[awoohaw]

try this: It is essently a GANTT chart:

mr excel questions 13.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Name	Start Time	End Time	12:01am-1:00am	1:01am-2:00am	2:01am-3:00am	3:01am-4:00am	4:01am-5:00am	5:01am-6:00am	6:01am-7:00am	7:01am-8:00am	8:01am-9:00am	9:01am-10:00am	10:01am-11:00am
2				00:00	01:00	02:00	03:00	04:00	05:00	06:00	07:00	08:00	09:00	10:00	11:00	12:00	13:00	14:00	15:00	16:00	17:00	18:00	19:00	20:00	21:00	22:00	23:00
3	Joe	01:00	06:30	0	1	2	2	2	2	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4	Amy	11:00	19:30	0	0	0	0	0	0	0	0	0	0	0	1	2	2	2	2	2	2	2	0	0	0	0	0
5	Bill	06:00	14:30	0	0	0	0	0	0	1	2	2	2	2	2	2	2	0	0	0	0	0	0	0	0	0	0
6	Cathy	09:00	19:30	0	0	0	0	0	0	0	0	0	1	2	2	2	2	2	2	2	2	2	0	0	0	0	0
7	Donald	05:00	14:00	0	0	0	0	0	1	2	2	2	2	2	2	2	2	0	0	0	0	0	0	0	0	0	0
8	Edward	05:00	16:00	0	0	0	0	0	1	2	2	2	2	2	2	2	2	2	2	0	0	0	0	0	0	0	0
9	Frank	05:00	08:30	0	0	0	0	0	1	2	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10	George	05:00	16:00	0	0	0	0	0	1	2	2	2	2	2	2	2	2	2	2	0	0	0	0	0	0	0	0
11	Harold	00:30	08:00	1	2	2	2	2	2	2	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
12	Isaac	13:00	21:00	0	0	0	0	0	0	0	0	0	0	0	0	0	1	2	2	2	2	2	2	2	0	0	0
13	Jim	14:00	22:00	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	2	2	2	2	2	2	2	0	0
14	Kimberly	08:00	16:00	0	0	0	0	0	0	0	0	1	2	2	2	2	2	2	2	0	0	0	0	0	0	0	0
15	Lawrence	14:00	22:00	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	2	2	2	2	2	2	2	0	0
16	Martha	11:30	22:30	0	0	0	0	0	0	0	0	0	0	0	1	2	2	2	2	2	2	2	2	2	2	0	0
Sheet3

Cell Formulas
Range	Formula
E2:AA2	E2	=D2+1/24
D3:AA3,AA4:AA16	D3	=((--(D$2>$B3))+(--((D$2+(59/(24*60)))>$B3)))*(--(D$2<=$C3))
D4:Z16	D4	=((--(D$2>$B4))+(--((D$2+(59/(24*60)))>$B4)))*(--(E$2<=$C4))

 

[awoohaw]

in the above you can use the formula in cell D3 for conditional formatting and color format the cells where the times match.

 

[awoohaw]

@AntBlabby8 , the mini workbook i posted above is done in 365. If you have earlier version 2010 and up, you may need to wrap the formula in a SUMPRODUCT function and use CNTL-SHFT-ENTER to enter the formula. (You may want to update your profile to indicate which version of excel you use so future questions can be more tailored to your version.)