Time sheet calculations Excel 2013

[polarnavs]

So I have a spreadsheet that I need to:

sum time for the week for the agent
sum time for the day of the week
count the number of agents that are scheduled during each half hour

Agent	Agent ID	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Total Hours
Jones, Charles	1849	OFF	9a-5p	9a-5p	10a-6p	7a-3p	8a-4p	OFF
Smith, Karen	1987	11P-7a	10P-6a	10p-6a	OFF	OFF	3p-11p	3p-11p
Bulfeeney, Mike	1876	4p-9p	9p-3a	2p-10p	OFF	OFF	OFF	1a-8a
Edwards, Karl	2098	6p-2a	1a-9a	5a-12p	9a-5p	9a-4p	OFF	OFF
Sanderson, Al	2187	OFF	9a-5p	9a-5p	10a-6p	7a-3p	8a-4p	OFF
Kennedy, Sherry	2287	11P-7a	5a-12p	1a-9a	8a-4p	OFF	10a-6p	OFF
Totals

<colgroup><col><col span="8"><col></colgroup><tbody>
</tbody>

I've done text to columns but can't get the split up cells to format to hours.
And I don't even know where to start on the calculation of how many agents scheduled for each half hour.

 

[vogel997]

	text to column		format-- > h:mm		formula
Tue	Tue
9a-5p	9a	5p	8:00		=MOD(TIMEVALUE(SUBSTITUTE(C4,RIGHT(C4,1)," "&RIGHT(C4,1)))-TIMEVALUE(SUBSTITUTE(B4,RIGHT(B4,1)," "&RIGHT(B4,1))),1)
10P-6a	10P	6a	8:00		=MOD(TIMEVALUE(SUBSTITUTE(C5,RIGHT(C5,1)," "&RIGHT(C5,1)))-TIMEVALUE(SUBSTITUTE(B5,RIGHT(B5,1)," "&RIGHT(B5,1))),1)
9p-3a	9p	3a	6:00		=MOD(TIMEVALUE(SUBSTITUTE(C6,RIGHT(C6,1)," "&RIGHT(C6,1)))-TIMEVALUE(SUBSTITUTE(B6,RIGHT(B6,1)," "&RIGHT(B6,1))),1)
1a-9a	1a	9a	8:00		=MOD(TIMEVALUE(SUBSTITUTE(C7,RIGHT(C7,1)," "&RIGHT(C7,1)))-TIMEVALUE(SUBSTITUTE(B7,RIGHT(B7,1)," "&RIGHT(B7,1))),1)
9a-5p	9a	5p	8:00		=MOD(TIMEVALUE(SUBSTITUTE(C8,RIGHT(C8,1)," "&RIGHT(C8,1)))-TIMEVALUE(SUBSTITUTE(B8,RIGHT(B8,1)," "&RIGHT(B8,1))),1)
5a-12p	5a	12p	7:00		=MOD(TIMEVALUE(SUBSTITUTE(C9,RIGHT(C9,1)," "&RIGHT(C9,1)))-TIMEVALUE(SUBSTITUTE(B9,RIGHT(B9,1)," "&RIGHT(B9,1))),1)
Total			21:00		=SUM(D4:D9)

<tbody>
</tbody>

 

[vogel997]

	text to column		format-- > h:mm		formula
Tue	Tue
9a-5p	9a	5p	8:00		=MOD(TIMEVALUE(SUBSTITUTE(C4,RIGHT(C4,1)," "&RIGHT(C4,1)))-TIMEVALUE(SUBSTITUTE(B4,RIGHT(B4,1)," "&RIGHT(B4,1))),1)
10P-6a	10P	6a	8:00		=MOD(TIMEVALUE(SUBSTITUTE(C5,RIGHT(C5,1)," "&RIGHT(C5,1)))-TIMEVALUE(SUBSTITUTE(B5,RIGHT(B5,1)," "&RIGHT(B5,1))),1)
9p-3a	9p	3a	6:00		=MOD(TIMEVALUE(SUBSTITUTE(C6,RIGHT(C6,1)," "&RIGHT(C6,1)))-TIMEVALUE(SUBSTITUTE(B6,RIGHT(B6,1)," "&RIGHT(B6,1))),1)
1a-9a	1a	9a	8:00		=MOD(TIMEVALUE(SUBSTITUTE(C7,RIGHT(C7,1)," "&RIGHT(C7,1)))-TIMEVALUE(SUBSTITUTE(B7,RIGHT(B7,1)," "&RIGHT(B7,1))),1)
9a-5p	9a	5p	8:00		=MOD(TIMEVALUE(SUBSTITUTE(C8,RIGHT(C8,1)," "&RIGHT(C8,1)))-TIMEVALUE(SUBSTITUTE(B8,RIGHT(B8,1)," "&RIGHT(B8,1))),1)
5a-12p	5a	12p	7:00		=MOD(TIMEVALUE(SUBSTITUTE(C9,RIGHT(C9,1)," "&RIGHT(C9,1)))-TIMEVALUE(SUBSTITUTE(B9,RIGHT(B9,1)," "&RIGHT(B9,1))),1)
Total			45		=SUM(D4:D9)*24

<colgroup><col width="101" style="width: 76pt; mso-width-source: userset; mso-width-alt: 3693;" span="3"> <col width="184" style="width: 138pt; mso-width-source: userset; mso-width-alt: 6729;"> <col width="6" style="width: 5pt; mso-width-source: userset; mso-width-alt: 219;"> <col width="1230" style="width: 923pt; mso-width-source: userset; mso-width-alt: 44982;"> <tbody> </tbody>

 

[vogel997]

	text to column		another approach
Tue	Tue
9a-5p	9a	5p
10P-6a	10P	6a
9p-3a	9p	3a
1a-9a	1a	9a
9a-5p	9a	5p
5a-12p	5a	12p
Total			One formula per day for all agents
	45		=SUMPRODUCT(MOD(--SUBSTITUTE(C4:C9,RIGHT(C4:C9,1)," "&RIGHT(C4:C9,1))---SUBSTITUTE(B4:B9,RIGHT(B4:B9,1)," "&RIGHT(B4:B9,1)),1))*24

<colgroup><col width="101" style="width: 76pt; mso-width-source: userset; mso-width-alt: 3693;" span="3"> <col width="1015" style="width: 761pt; mso-width-source: userset; mso-width-alt: 37120;"> <tbody> </tbody>

 

[polarnavs]

Can I add an IFERROR to this to get a 0 for #VALUE issues

 

[polarnavs]

First part is a huge help. Any idea on how to count the agents scheduled during a particular half hour.

12:00A - 12:30 A 3
12:30A - 1:00A 2

and so on

 

[vogel997]

Can I add an IFERROR to this to get a 0 for #VALUE issues

If error will not work, replace off with zero's in both cells.

<colgroup><col width="554" style="width: 416pt; mso-width-source: userset; mso-width-alt: 20260;"> <tbody> </tbody>

 

[polarnavs]

Figured this one out on my own. Thx

 

[polarnavs]

=iferror(mod(timevalue(substitute(m2,right(m2,1)," "&right(m2,1)))-timevalue(substitute(l2,right(l2,1)," "&right(l2,1))),1),0)