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
[TABLE="width: 738"]
<colgroup><col><col span="8"><col></colgroup><tbody>[TR]
[TD]Agent[/TD]
[TD]Agent ID[/TD]
[TD] Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Total Hours[/TD]
[/TR]
[TR]
[TD]Jones, Charles[/TD]
[TD="align: right"]1849 [/TD]
[TD] OFF[/TD]
[TD]9a-5p[/TD]
[TD]9a-5p[/TD]
[TD]10a-6p[/TD]
[TD]7a-3p[/TD]
[TD]8a-4p[/TD]
[TD]OFF[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Smith, Karen[/TD]
[TD="align: right"]1987[/TD]
[TD] 11P-7a[/TD]
[TD]10P-6a[/TD]
[TD]10p-6a[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]3p-11p[/TD]
[TD]3p-11p[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Bulfeeney, Mike[/TD]
[TD="align: right"]1876 [/TD]
[TD]4p-9p[/TD]
[TD]9p-3a[/TD]
[TD]2p-10p[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]1a-8a[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Edwards, Karl[/TD]
[TD="align: right"]2098[/TD]
[TD] 6p-2a[/TD]
[TD]1a-9a[/TD]
[TD]5a-12p[/TD]
[TD]9a-5p[/TD]
[TD]9a-4p[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sanderson, Al[/TD]
[TD="align: right"]2187[/TD]
[TD] OFF[/TD]
[TD]9a-5p[/TD]
[TD]9a-5p[/TD]
[TD]10a-6p[/TD]
[TD]7a-3p[/TD]
[TD]8a-4p[/TD]
[TD]OFF[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Kennedy, Sherry[/TD]
[TD="align: right"]2287 [/TD]
[TD]11P-7a[/TD]
[TD]5a-12p[/TD]
[TD]1a-9a[/TD]
[TD]8a-4p[/TD]
[TD]OFF[/TD]
[TD]10a-6p[/TD]
[TD]OFF[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Totals[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
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.
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
[TABLE="width: 738"]
<colgroup><col><col span="8"><col></colgroup><tbody>[TR]
[TD]Agent[/TD]
[TD]Agent ID[/TD]
[TD] Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Total Hours[/TD]
[/TR]
[TR]
[TD]Jones, Charles[/TD]
[TD="align: right"]1849 [/TD]
[TD] OFF[/TD]
[TD]9a-5p[/TD]
[TD]9a-5p[/TD]
[TD]10a-6p[/TD]
[TD]7a-3p[/TD]
[TD]8a-4p[/TD]
[TD]OFF[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Smith, Karen[/TD]
[TD="align: right"]1987[/TD]
[TD] 11P-7a[/TD]
[TD]10P-6a[/TD]
[TD]10p-6a[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]3p-11p[/TD]
[TD]3p-11p[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Bulfeeney, Mike[/TD]
[TD="align: right"]1876 [/TD]
[TD]4p-9p[/TD]
[TD]9p-3a[/TD]
[TD]2p-10p[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]1a-8a[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Edwards, Karl[/TD]
[TD="align: right"]2098[/TD]
[TD] 6p-2a[/TD]
[TD]1a-9a[/TD]
[TD]5a-12p[/TD]
[TD]9a-5p[/TD]
[TD]9a-4p[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sanderson, Al[/TD]
[TD="align: right"]2187[/TD]
[TD] OFF[/TD]
[TD]9a-5p[/TD]
[TD]9a-5p[/TD]
[TD]10a-6p[/TD]
[TD]7a-3p[/TD]
[TD]8a-4p[/TD]
[TD]OFF[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Kennedy, Sherry[/TD]
[TD="align: right"]2287 [/TD]
[TD]11P-7a[/TD]
[TD]5a-12p[/TD]
[TD]1a-9a[/TD]
[TD]8a-4p[/TD]
[TD]OFF[/TD]
[TD]10a-6p[/TD]
[TD]OFF[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Totals[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
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.