Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time 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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time Formula
You can also use:
=TIMEVALUE(LEFT(A3,2)&":"&MID(A3,3,2)&" "&RIGHT(A3,1)) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time Formula
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(I117.5,C1,0))) which would return results of 0,0,0,5,2,0 I hope this helps. -- Regards Roger Govier "Cindy Smith" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time formula, complicated? | Excel Discussion (Misc queries) | |||
How do I write the formula to calculate someones time worked | Excel Worksheet Functions | |||
Formula is entering a default time when it comes across an empty cell.. | Excel Worksheet Functions | |||
Formula to deduct unpaid breaks in time sheet | Excel Discussion (Misc queries) | |||
time formula question... | Excel Discussion (Misc queries) |