Time Formula

I was given a file that looks like this:

Day of Wk Time Number of Minutes
Tuesday 1148A 3
Tuesday 1151A 1
Wednesday 0301P 1
Sunday 1202P 5
Saturday 0530P 2
Monday 0542P 10

Trying to determin how many minutes were spent on the phone during non-work
days/times (M-F, 8-5)

1. How do I convert the time into a useable format?
2. Please help w/formula

=TIME(LEFT(A6,2),MID(A6,3,2),0)+(RIGHT(A6)="P")*0.5

and format as time.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Cindy Smith" <CindySmith@discussions.microsoft.com> wrote in message
news:5215C6C6-D8A1-444A-9982-4AFEF0962FBC@microsoft.com...
> I was given a file that looks like this:
>
> Day of Wk Time Number of Minutes
> Tuesday 1148A 3
> Tuesday 1151A 1
> Wednesday 0301P 1
> Sunday 1202P 5
> Saturday 0530P 2
> Monday 0542P 10
>
> Trying to determin how many minutes were spent on the phone during
non-work
> days/times (M-F, 8-5)
>
> 1. How do I convert the time into a useable format?
> 2. Please help w/formula

You can also use:

=TIMEVALUE(LEFT(A3,2)&":"&MID(A3,3,2)&" "&RIGHT(A3,1))

Hi Cindy

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.

--
Regards

Roger Govier


"Cindy Smith" <CindySmith@discussions.microsoft.com> wrote in message
news:5215C6C6-D8A1-444A-9982-4AFEF0962FBC@microsoft.com...
>I was given a file that looks like this:
>
> Day of Wk Time Number of Minutes
> Tuesday 1148A 3
> Tuesday 1151A 1
> Wednesday 0301P 1
> Sunday 1202P 5
> Saturday 0530P 2
> Monday 0542P 10
>
> Trying to determin how many minutes were spent on the phone during
> non-work
> days/times (M-F, 8-5)
>
> 1. How do I convert the time into a useable format?
> 2. Please help w/formula