Need help creating a formula, or at least lead me towards the right way

[Braco]

Hi,
I need help in regards to making the below work, or is it even possible? Just head me towards the right direction and I will appreciate it. Would I use If/then for the below I am trying to do?

So I need to have a formula in the Category Column that can do the below based on the Start Date/Call Time/ Call Duration. There are usually over 100 calls, the below is jut a sample.

So the formula should first take the time it calculates for Mandatory, and then randomly select a few other calls from the list and mark them as Monitored and it should all equal around 1 hour ~.
Mandatory - Any call done on a weekend (start date), and any call before 8:00:00AM and any call after 6:00:00PM on a weekday.
Monitored - Are going to be random remaining calls that add up to 1 hour total.
Excluded - Everything else that remains blank.

Start date <tbody> </tbody><colgroup><col></colgroup>	Call Time <tbody> </tbody><colgroup><col></colgroup>	Duration <tbody> </tbody><colgroup><col></colgroup>	Call Duration <tbody> </tbody><colgroup><col></colgroup>	Category (Excluded, Mandatory, Monitored) <tbody> </tbody><colgroup><col></colgroup>
6/2/17 <tbody> </tbody><colgroup><col></colgroup>	8:37:47 AM <tbody> </tbody><colgroup><col></colgroup>	58 <tbody> </tbody><colgroup><col></colgroup>	0:00:58 <tbody> </tbody><colgroup><col></colgroup>
6/3/17 <tbody> </tbody><colgroup><col></colgroup>	8:40:34 AM <tbody> </tbody><colgroup><col></colgroup>	4 <tbody> </tbody><colgroup><col></colgroup>	0:00:04 <tbody> </tbody><colgroup><col></colgroup>
6/4/17 <tbody> </tbody><colgroup><col></colgroup>	12:26:26 PM <tbody> </tbody><colgroup><col></colgroup>	14 <tbody> </tbody><colgroup><col></colgroup>	0:00:14 <tbody> </tbody><colgroup><col></colgroup>
6/5/17 <tbody> </tbody><colgroup><col></colgroup>	8:57:57 AM <tbody> </tbody><colgroup><col></colgroup>	26 <tbody> </tbody><colgroup><col></colgroup>	0:00:26 <tbody> </tbody><colgroup><col></colgroup>

<tbody>
</tbody>

 

[AlanY]

I've extended the data for testing

Excel 2012
	A	B	C	D	E	F	G	H	I	J
1	Start date	Call Time	Duration	Call Duration	Category (Excluded, Mandatory, Monitored)					3
2	06/02/2017	7:37:47 AM	58	00:00:58	Mandatory	M		1	18
3	06/03/2017	8:40:34 AM	4	00:00:04		0.350658	6	2	23	41
4	06/04/2017	12:26:26 PM	14	00:00:14	Monitored	0.673426	3	3	14	55
5	06/05/2017	8:57:57 AM	26	00:00:26	Mandatory	M		4	11	66
6	06/06/2017	9:00:00 AM	18		Monitored	0.934719	1	5	10	76
7	06/07/2017	11:00:00 PM	16		Mandatory	M		6	4	80
8	06/08/2017	4:00:00 AM	17		Mandatory	M		7	15	95
9	06/09/2017	1:00:00 PM	10			0.437668	5	8	#N/A	#N/A
10	06/10/2017	12:00:00 PM	23		Monitored	0.923007	2	9	#N/A	#N/A
11	06/11/2017	2:00:00 AM	13		Mandatory	M		10	#N/A	#N/A
12	06/12/2017	4:00:00 AM	12		Mandatory	M		11	#N/A	#N/A
13	06/01/2018	3:00:00 AM	11		Mandatory	M		12	#N/A	#N/A
14	06/02/2018	8:00:00 AM	15			0.133506	7	13	#N/A	#N/A
15	06/03/2018	9:00:00 AM	11			0.614734	4	14	#N/A	#N/A
Sheet2

Cell Formulas
Range	Formula
E2	=IF(ISNUMBER(G2),IF(G2<=$J$1,"Monitored",""),"Mandatory")
F2	=IF(OR(WEEKDAY(A2,2)>5,OR(B2<8/24,B2>18/24)),"M",RAND())
G2	=IFERROR(RANK(F2,$F$2:$F$15),"")
H2	=ROW(F2)-ROW($F$1)
I2	=INDEX($C$2:$C$15,MATCH(H2,$G$2:$G$15,0))
J1	=LOOKUP(60,J2:J15,H2:H15)

Cols F to G are the working part of the table.

1) Col F - work out the Mandatory and marked with "M", and create a random no for the rest
2) Col G - rank the cells with a random no, i.e. not Mandatory.
3) Col H - nos create for Col I
4) Col I - read out the duration (Col C) for the non Mandatory cells
5) Col J - accumulate the duration for Col I
6) Cell J1 - work out no of calls add up to 60 mins.
7) Col E - results

 

[Braco]

Thank you Alan! Will try this out.