Unable to count time before 08:00:00 am

Shweta · Jun 12, 2011

Hi there.......

I have a spreadsheet containing the following data. Now I have to calculate the RNR amount for agents but it is applicable only when they have not taken their break between 04:00:00 am to 08:00:00 am.

The data is like:
Sheet 1

<TABLE style="WIDTH: 217pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=289 border=0><COLGROUP><COL style="WIDTH: 106pt; mso-width-source: userset; mso-width-alt: 5156" width=141><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 106pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=141 height=17>Agent's Name

</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=67>Bill Count

</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 61pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=81>Rnr Amount

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl90 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>A

</TD><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">93

</TD><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">80

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl90 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>B

</TD><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">120

</TD><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">150

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl90 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C

</TD><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">144

</TD><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">174

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl90 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>D

</TD><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">75

</TD><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">75

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl90 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>E

</TD><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">60

</TD><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">35

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl90 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>F

</TD><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">71

</TD><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">50

</TD></TR></TBODY></TABLE>

Sheet 2

<TABLE style="WIDTH: 188pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=250 border=0><COLGROUP><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4132" width=113><COL style="WIDTH: 103pt; mso-width-source: userset; mso-width-alt: 5010" width=137><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 85pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=113 height=17>Agent's Name

</TD><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 103pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=137>Break starttime

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A

</TD><TD class=xl93 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">6/7/2011 4:38:13 AM

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A

</TD><TD class=xl94 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">6/7/2011 8:06:52 AM

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>B

</TD><TD class=xl94 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">6/7/2011 8:10:00 AM

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>B

</TD><TD class=xl94 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">6/7/2011 10:05:00 AM

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>B

</TD><TD class=xl94 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">6/7/2011 12:30:00 PM

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C

</TD><TD class=xl94 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">6/7/2011 5:37:00 AM

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C

</TD><TD class=xl94 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">6/7/2011 10:38:00 AM

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C

</TD><TD class=xl94 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">6/7/2011 12:34:00 PM

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>E

</TD><TD class=xl94 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">6/7/2011 8:07:05 AM

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>E

</TD><TD class=xl93 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">6/7/2011 10:05:08 AM

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>E

</TD><TD class=xl94 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">6/7/2011 12:48:00 PM

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>F

</TD><TD class=xl94 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">6/7/2011 6:32:00 AM

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>F

</TD><TD class=xl94 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">6/7/2011 8:45:00 AM

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>F

</TD><TD class=xl94 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">6/7/2011 12:55:00 PM

</TD></TR></TBODY></TABLE>

According to this data A,C and F should not get the RNR amount as they have taken their break between 04:00:00 am to 08:00:00 am. What should be the formula in RNR amount field in sheet 1 so that it shows 0 amount for these agents.

Note :- In sheet2 Agent's name is repeating twice or thrice as an agent is allowed to take maximum 3 breaks in a day.

Please tell me the formula for Rnr field.
I am thinking of to insert a new columm it will show yes if the break starttime is before 08:00:00 am and false if it's not.
Formula would be =IF(SUMPRODUCT(--((C4<E2)*($C4>F2)))=1,"Yes","False")

E2 is 08:00:00 am and F2 is 04:00:00 am

but the problem is how to connect it with agent's name as it is repeating thrice.

REPLY ME AS SOON AS POSSIBLE AS I NEED THIS DATA URGENTLY.

Thanks.

venkat1926 · Jun 13, 2011

see sheet 2 below with additional column C and formula in C2 which is copied down
after copying sort data ascending according to "agent's namae" and then according to "result" so that in each agent's name if the result is "no" it comes first.

Excel Workbook

A

B

C

1

Agent's Name

Break starttime

result

2

A