Hi Cindy
I posted you a reply to this question which you posted in Excel.misc
yesterday.
In case you can't see it I repeat it below.
If you didn't see my post, you may not have seen the response from Bob
Philips either, to which I refer in my posting.
Bob posted
=TIME(LEFT(A6,2),MID(A6,3,2),0)+(RIGHT(A6)="P")*0.5
I posted
Both Bob and Linc have given you solutions for the first part of your
question. My own solution was slightly different, but no better, so I
won't confuse you by adding it to those already received.
However, there was a corollary to your question about how to calculate
the number of minutes which were outside of normal work hours.
With Day in column A, Time in B and Minutes in C I put my formula to
convert the text of time to real time in column F (you will need to
change the references in the formulae you have to be column B, instead
of columnA).
Then in column G enter
=IF(LEFT(A1)="S",C1,IF(F1+TIME(0,C1,0)>TIME(17,30,0),
(F1+TIME(0,C1,0)-TIME(17,30,0))*1440,IF(F1<TIME(8,0,0),
(F1+TIME(0,C1,0)-TIME(8,0,0))*1440,0)))
Format the cell as General and copy down the column.
This does literally what you asked for and would give results of
0,0,0,5,2,4 for the 6 lines of example data you posted
The first 3 lines would all be 0 because the calls originate within the
working week.
Lines 4 and 5 take the total minutes, as they both originate on a
weekend.
Line 6 call starts during the working week, but ends 4 minutes outside
the working week, so 4 of the total 10 minutes would be returned.
If your request for time is more simply only those that began outside
the working week, regardless of whether their durations took them to
within the working week, the above formula could be simplified, but I
would be inclined to make it easier by converting all time to decimal
hours. This could be achieved by multiplying the formulae you already
have by 24. For example, with Bob's formula, (adjusted for column B), it
would be in column F
=(TIME(LEFT(B10,2),MID(B10,3,2),0)+(RIGHT(B10)="P")*0.5)*24
and the simplified formula would be
=IF(LEFT(A1)="s",C1,IF(I1<8,C1,IF(I1>17.5,C1,0)))
which would return results of 0,0,0,5,2,0
I hope this helps.
