Need help trying to quantify hours scheduled per hour

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

duplicate post:

http://www.excelforum.com/excel-form...-per-hour.html

If you rearrange your input data as shown in columns B:D, you could do this:

Row\Col	B	C	D	E	F	G	H	I	J	K
4	Employee	In	Out		Time	On Duty	Duration		Employee	Hours
5	Alan	Mon 01/19/2015 08:00	Mon 01/19/2015 15:00		Mon 01/19/2015 08:00	3	0:30		Alan	52:00
6	Barb	Mon 01/19/2015 08:00	Mon 01/19/2015 16:00		Mon 01/19/2015 08:30	4	0:30		Barb	52:00
7	Cain	Mon 01/19/2015 08:00	Mon 01/19/2015 16:30		Mon 01/19/2015 09:00	5	0:30		Cain	53:00
8	Dana	Mon 01/19/2015 08:30	Mon 01/19/2015 16:00		Mon 01/19/2015 09:30	6	0:30		Dana	50:30
9	Eric	Mon 01/19/2015 09:00	Mon 01/19/2015 16:00		Mon 01/19/2015 10:00	11	5:00		Eric	48:00
10	Fran	Mon 01/19/2015 09:30	Mon 01/19/2015 16:00		Mon 01/19/2015 15:00	9	1:00		Fran	49:00
11	Gary	Mon 01/19/2015 10:00	Mon 01/19/2015 15:00		Mon 01/19/2015 16:00	8	0:30		Gary	44:00
12	Hana	Mon 01/19/2015 10:00	Mon 01/19/2015 16:00		Mon 01/19/2015 16:30	7	0:30		Hana	43:00
13	Ivan	Mon 01/19/2015 10:00	Mon 01/19/2015 18:00		Mon 01/19/2015 17:00	9	1:00		Ivan	45:00
14	Jane	Mon 01/19/2015 10:00	Mon 01/19/2015 16:00		Mon 01/19/2015 18:00	8	3:00		Jane	45:00
15	Kent	Mon 01/19/2015 10:00	Mon 01/19/2015 16:00		Mon 01/19/2015 21:00	5	2:00		Kent	40:00
16	Leah	Mon 01/19/2015 16:00	Mon 01/19/2015 23:00		Mon 01/19/2015 23:00	0	9:00		Leah	39:30
17	Mark	Mon 01/19/2015 16:00	Mon 01/19/2015 23:00		Tue 01/20/2015 08:00	3	0:30		Mark	34:00
18	Nina	Mon 01/19/2015 16:00	Mon 01/19/2015 21:00		Tue 01/20/2015 08:30	4	0:30		Nina	32:00
19	Otto	Mon 01/19/2015 16:00	Mon 01/19/2015 21:00		Tue 01/20/2015 09:00	5	0:30		Otto	32:30
20	Peri	Mon 01/19/2015 16:00	Mon 01/19/2015 21:00		Tue 01/20/2015 09:30	6	0:30		Peri	27:30
21	Quin	Mon 01/19/2015 16:00	Mon 01/19/2015 23:00		Tue 01/20/2015 10:00	10	5:00		Quin	29:00
22	Rene	Mon 01/19/2015 17:00	Mon 01/19/2015 23:00		Tue 01/20/2015 15:00	8	1:00		Rene	12:00
23	Seth	Mon 01/19/2015 17:00	Mon 01/19/2015 23:00		Tue 01/20/2015 16:00	6	0:30		Seth	6:00
24	Alan	Tue 01/20/2015 08:00	Tue 01/20/2015 16:00		Tue 01/20/2015 16:30	7	0:30
25	Barb	Tue 01/20/2015 08:00	Tue 01/20/2015 16:00		Tue 01/20/2015 17:00	7	4:00
26	Cain	Tue 01/20/2015 08:00	Tue 01/20/2015 16:00		Tue 01/20/2015 21:00	6	0:30
27	Dana	Tue 01/20/2015 08:30	Tue 01/20/2015 16:00		Tue 01/20/2015 21:30	5	0:30
28	Eric	Tue 01/20/2015 09:00	Tue 01/20/2015 16:30		Tue 01/20/2015 22:00	4	1:00
29	Fran	Tue 01/20/2015 09:30	Tue 01/20/2015 16:00		Tue 01/20/2015 23:00	0	9:00
30	Gary	Tue 01/20/2015 10:00	Tue 01/20/2015 15:00		Wed 01/21/2015 08:00	3	0:30
31	Hana	Tue 01/20/2015 10:00	Tue 01/20/2015 17:00		Wed 01/21/2015 08:30	4	0:30
32	Ivan	Tue 01/20/2015 10:00	Tue 01/20/2015 16:00		Wed 01/21/2015 09:00	6	1:00

See the last tab in the workbook at https://app.box.com/shared/97plvyn4ie

deleted -- threads merged.

I rearraged things a bit. can you use this at all?

Firsttimemanager, please do not post duplicate threads. If this was due to a forum hiccup, then please disregard this. Otherwise, please wiat for members to respond

I will merge your 2 threads

Mmmm. I always use military time. It's unambiguous. To enter as such, just enter hh then colon ( : ) then mm.

Of course, if you prefer AM-PM format, simply format the cells to hh:mm AM/PM as in the attached. No change to the formulas is needed, as all the formatting is doing is changing the way you see the time, and not changing its underlying value.