Calculating Turn Around Time ..

Mar 11, 2009

I've browsed the net searching for a solution to my problem and found one solution altough not entirely what i was after, but it was very close. It was provided on another forum, of which i'm not a member.

[url] ...

I have a TAT target of <= 2hours, however this is complicated by weekends, holidays and jobs received after working hours and on weekends. I've attached the file, which contains detailed information about my problem.

ADVERTISEMENT

Calculating Turn Around Time Between Two Times

Mar 12, 2009

i want to calculate the TAT between two times. the TAT target is <= 2 hours. i used the following formula

a1 has 3/13/2009 (received date)
b1 has 7:08 AM (received time)
c1 has 3/13/2009 (completed date)
d1 has 9:08 AM (completed time)
e1 has TAT formula :- '=IF((D1-B1)*1440<=120,"Met TAT","Not Met TAT")

however this formula does not work in the following conditions.

In these conditions, it is considered that TAT is met.
1. When the difference in time is <=2 hours .... for TAT calculation, on working days and working hours are taken into consideration.

To illustrate.
Day begins : 8:00 AM
Day ends : 4:00 PM

If job is received at 3:30 PM and completed the next working day by 9:30 AM, then it is considered TAT is met.
calculation = 4:00 PM - 3:30 PM = half hour + next day's 9:30 AM - 8:00 AM = 1.5 hours, therefore, total working hours used to complete the job is within the agreed TAT.

If job is received and completed on non working days and during non working hours, it is considered TAT met.

If job is received almost at the end of the day, say, 3.30 PM and job is completed at 8:00 PM same day, then it is considered TAT met, rationale, only half an hour of working hours used to complete the job.

View 11 Replies View Related

Calculating Turn Around Time In Multiple Scenario

May 4, 2007

1: Clock shoud tick monday to friday 8:00am to 4:pm only
2: there are 4 coloums where date recieved, date sent, time recieved, time sent are available.
3: the TAT should show the number of hours taken to from the time a case has been recieved to the time it has been sent

View 9 Replies View Related

Time Turn It Into Quaters Increments

May 7, 2008

I need an equation that will take time and turn it into quaters increments. Meaning, If we work on a computer for 1 hour and 15 minutes (1:15) then i need it to say 1.25.

1:00-1:15 =1.25
1:15-1:30 = 1.50
1:30-1:45 = 1.75
1:45 - 2 = 2.00
and so on... up to 3 hours.

If E2 = 1:00-1:15 then F2 =1.25
If E2 1:15 then F2 = 1.50

View 9 Replies View Related

Calculating Time Taken And Average Amount Of Units / Time Taken?

Jun 11, 2014

I am having a little trouble with a spreadsheet I am creating, the formulas and cell formatting I should be using to enable this to work.

Here goes:

in cell E3 I have a time started (e.g 12:45 pm), In F3 i have time finished (e.g 2:30 pm)

So, what i'm wanting is the time taken in G3 and also, i have the amount of units that is entered manually in H3. I would like I3 to show the time taken per unit.

View 2 Replies View Related

Calculating Time And A Half And Double Time (After 40 Hours)

Feb 20, 2009

I'm battling to come up with the formula to calculate time and a half and double time. My boss wants me to show the overtime worked AFTER 40 hours has been worked. I have attached a copy of my spreadsheet.

View 4 Replies View Related

Calculating Time Owed Based On Time Worked

Jun 23, 2009

I know the title is a bit vague, but I cant think how else to word it!

I have a sheet (attached) which works out hours worked, and if the amount is under a specified target, it counts how much time is owed. The problem occurs when someone works more hours than the specified target.

I guess I need an IF formula of some kind, to say if the figure is over the target, to put zero in the hours owed column.

View 9 Replies View Related

Turn #DIV//0! To 0 Or Even Turn It To A Blank Cell

Nov 6, 2009

I have a workbook which is set up to take an average heart rate of a participant from a series of data points. I have set the spreadsheet up before I have collected some of the data. (so I can review the project at the 3 months period and its an ongoing project).

The problem is that if there is no data in a participants column then excel correctly gives you readout of #DIV//0!. On my results page this #DIV//0! makes it hard to read the spreadsheet. Is it possible to get excel to turn #DIV//0! to 0 or even turn it to a blank cell?

View 5 Replies View Related

Calculating Using Time

Nov 12, 2009

Firstly, i'd like cell G5 to show the amount of time worked i.e diff between E6 & F6. Secondly I'd like help with the formula for cells AF6 & AG6 which would require AB6-AC6 divided by the frames, every time I try it I get a messed up answer.

View 14 Replies View Related

Calculating Time In

Sep 9, 2009

=INDEX({"$5.00","$3.00","You Were Late"},MATCH(L17,{7:00am,7:05am,7:06am},1))

i have this formula in excel...this formula sanrv1f posted to help with another question (values changed) but i thought i would work with what i wanted but i get a (Value) error

so what im trying to do is base on the system time is if the person typed in

if they were early 6:45am to 7am they get Extra $5 on top of the $5 they get for being on time.

6:55am gets $5
7:00am they get $5.00 for being on time
if they type 7:01am to 7:05am they get $3.00
if they type 7:06 to 7:10am would return You Were Late
after 7:10am would return No Pay

so Ex

D4 *answers in E4 *

7am would get $5

7:03am am would get $3

7:07am would get You Were Late

7:11am Would get No Pay

if they were on time 5 times (ie 5days in a row) they would get a bonus of $20 i tried factoring this in could not do it :/

View 9 Replies View Related

Calculating Time

Feb 21, 2010

basically I need an excel sheet to calculate the period of time between cells.

For instance, in A1 I write:

18-Feb-10

In A2 I write:

13:00

Unfortunately I cannot write 18-Feb-09 13:00 in just one cell because it doesn't work.

Now, in B1 I write 19-Feb-10

and in B2 I write 15:14

Now we know that 26 hours and 14 minutes have elapsed between these two times. So if I want this to automatically be calculated in B3, what do I do?

Also, how do I apply the formula to all cells in column 3 so all I have to type in is the date (in column 1) and the time (in column 2)?

View 9 Replies View Related

Calculating Time If Value Is Less Than Or Greater Than?

Mar 30, 2014

I'm having a DICKENS of a time on this one. To explain, I have 3 columns where time is entered: a 'Time of Call', 'Time of Arrival', and 'Response Time'. The 'Response Time' cell autcalculates by simply subtracting the 'Time of Arrival' form the 'Time of Call' and using the difference to show the response time. To make time entry into the 'Time of Call' and 'Time of Arrival' simplier for my co-workers, I formatted those cells as 00:00 so that anytime you enter a number it will convert it to a time format (i.e., if you enter in the numbers 745, it will show as 7:45 in the cell). Because of this, when the response time is configured, it does it in hundreds instead of time format. So, if you enter in 7:45 as your time of call and 8:15 as your time of arrival, it shows 70 minutes as a response time because it sees the 7:45 as a whole number...745. Therefore, 815-745 DOES equal 70, but...that's not what I'm looking for. I want it too configure this as time...not as whole numbers.

View 2 Replies View Related

Calculating Time That Is Goes Into Days

Apr 2, 2014

I know how to calculate the duration elapsed between start and finish time. But how can I display the total time if for eg it's ends up being 300hrs?

I need the hours spent in the building per day and then the grand total per month.

View 1 Replies View Related

Calculating Time Variance

Jan 26, 2009

I'm trying to calculate the variance between planned date & time of arrival vs actual date & time of arrival.

I attach the workbook as am a bit useless at explaining myself....

What I've done is in H14 subtract the actual date of arrival (F14) from planned date of arrival (C14). This result is the only way I could think of dealing with crossing over midnight. As a result I14 should subtract the actual time of arrival (E14) from planned time of arrival (B14):

=SUM(E14-B14,H14)

This method works well when the arrival was later than expected but doesn't work if the arrival was sooner than expected.

View 6 Replies View Related

Calculating The Difference In Time?

Aug 17, 2013

I'm calculating the difference in time:

9:54-9:45 = 0:09

Now to convert this in minutes (in number format), I multiply this number with 1440:

0:09*1440 = 9

When I click the cell this number is actually 9.0000005, rather than exact 9. I need it to be exact 9 since I have to match this number from other excel sheet.

To sum up:

This is what I expect:

31.99999 should be changed to 32

10.000001 should be changed to 10.

View 3 Replies View Related

Calculating & Formatting Time..

Jun 2, 2009

I am trying to track how long a macro takes to run from start to end.

View 4 Replies View Related

Long Calculating Time

Aug 26, 2008

I managed to put together an array formula to calculate the last date that a rep made a sale. It checks two other tabs in the workbook to find the date, and if none is found, it leaves the cell empty.

{=IF(MAXA(IF('Daily Compliance'!A:A=B48,'Daily Compliance'!O:O),IF('Daily Compliance 11-07 to 4-08'!B:B=B48,'Daily Compliance 11-07 to 4-08'!A:A))=0,"",MAXA(IF('Daily Compliance'!A:A=B48,'Daily Compliance'!O:O),IF('Daily Compliance 11-07 to 4-08'!B:B=B48,'Daily Compliance 11-07 to 4-08'!A:A)))}

But it takes sometimes up to 4-5 minutes to make the calculations. Is there possibly a way to simplify it so that it calculates faster, with the same results?

View 3 Replies View Related

Calculating Time Between Two Dates?

May 15, 2012

I need to calculate an SLA.

I have 1 column called 'Date/Time Requested' containing the time and the date together, as in:

dd/mm/yy hh:00

I also have a second column called 'Date/Time Completed' containing the date and time in the same format as above.

I need a macro that compares the two and works out the following:

< 48 hours = "Within SLA"
> 48 hours = "Outside SLA"

View 4 Replies View Related

Calculating Task Time

Mar 18, 2007

I have a sheet to calculate Time Elapsed based on StartDT & EndDt taking into consideration the actual working hours in any working day and the function exclude the weekend. I am not a guru in excel but i found this formula in this website .

=IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT),HolidayList,0)))),0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((EndDT-INT(EndDT))-
(StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+
(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))
the formula works perfectly except for the fact that in our part of the world we have "Friday & Saturday" as the weekend days instead of Saturday and Sunday.

is there a way to get this formula or any similar formula to take different weekend days and do exactly what i need?

View 9 Replies View Related

Calculating Flexi Time

Sep 15, 2007

I have two questions about a spreadsheet where I am trying to calculate how much flexi time has been accured.

So far I have managed to do this: ....

View 9 Replies View Related

Calculating Time Blocks

Apr 11, 2009

I am trying to calculate the amount of time in blocks, using a pre-determined spread. For example:

AM block is anytime between 0600-1230
PM block is anytime between 1230-1830
Evening block is anytime between 1830-0000 and the Overnight block is anytime between 0000-0600

Sounds easy enough, yeah? Let me paint an example of how I want it to calculate though:

If an employee starts at 0600 and finishes at 1400, then the AM total is 6.5 and the PM total is 1.5.

If an employee starts at 1100 and finishes at 1900, then the AM total is 1.5, the PM is 6 and the Evening is 0.5.

If an employee starts at 2200 and finishes at 0800, then all of the 10 hours must report to the Overnight total, even though some of them fall within the Evening and AM blocks.

I just can't seem to get each block to show the accurate number of hours...and to bring in the rules that I require.

View 9 Replies View Related

Calculating Value, Based On Time

Apr 27, 2004

I am trying to write what I believe is a fairly simple set of calculations to determine non-qualified stock option values. Before this digresses into a discussion of the merits of Black-Sholes methodology, I must tell you that this is a "101" type sheet. I'm not an economist.

The deal is that my sister (for whom I'm constructing this worksheet), gets employer stock options which vest at different intervals.

In the past year, she's gotten some options that vest in two, four, and five year increments. I'm using Excel 2002, with the analysis toolpak, and stock pricing add-in from MS.

Have today's date, date of option grant, and can easily compute time (in years) until options are fully vested.

The trick is that options with a 2-year vesting period are 50% excerciseable in one year, 100% in two.

Four-year options are excerciseable 25% per year for 4 years; 5-year are 20% for 5 years, etc.

I'm trying to write a single date-driven formula to take each grant (individually), and divide it by the appropriate divisor (based on the current date), to figure out the current value.

I can't seem to quite figure it out how to write a formula which can handle:

Shares Strike Price Date of Grant Years Until Vested Value
100 26.57 1/4/2003 5
245 45.67 2/6/2001 4

Value should equal =

(if today's price is grater than Strike Price)

(Current Price * (Shares * (% vested which is based on "date of grant" + "years until vested")) )

-

(Strike Price * Shares * (% vested which is based on "date of grant" + "years until vested") )

how the math to make the "% vested multiplier" work...

View 9 Replies View Related

Calculating Time All (redundant) Systems Down

Sep 20, 2013

I have redundant system groups. One group may have (for example) two computers running. Another group may have eight computers running. But within any group only one computer needs to be running for the system to be operational.

I have a row with down start date/time (Column-A) and down end date/time (Column-B) for each downtime event. In then next columns I have the start times and end times for the other computer(s) in the group.

Finally, I have a date column to the right of all others (one row per day). Next to the date column I need to have the value that represents the common time all computers in that group were down.

I don't much care if this is done with a formula or by VBA. I can split the date and time values into separate columns and format the values however needed to facilitate the calculation.

I've placed an example workbook at the following location. [URL] ...

View 1 Replies View Related

Calculating Time Elapsed Over Two Days

Dec 3, 2013

I am trying to calculate the time elapsed. I have included a caption to show my formula. The problem I have is that some times are showing > 60 minutes instead of increasing the hour. When my day go over the midnight hour I get a negative number.

View 11 Replies View Related

Calculating The Difference Between Time Stamps?

Dec 12, 2013

I've got a spreadsheet of samples taken at certain time intervals for a period of 2.5 hours, they were collected in hh:mm:ss format. Most of them are approximately 5 minutes apart. What I'd like to do is convert the first time to 00:00:00 and then recalculate each sample from there. Is there an easy way to do this?

So, what looks like this right now:
8:45:00
8:45:36
8:50:36
8:55:36
9:00:36

Would look like this:
00:00:00
00:00:36
00:05:36
00:10:36
00:15:36
etc...

View 8 Replies View Related

Calculating Difference In Time After Midnight

Dec 14, 2013

I'm trying to calculate the available man hours I have for my shift/team. I have got the basic formula to deduct the time, and lost time for breaks ok, but when the shift end time crosses midnight the formula returns a "value" error message. i.e. start time = 18:00. End time = 24:00 breaks = 15 mins gives me a formula like this =TEXT(O17-N17-P17,"h:mm") and a result of 5:45

How I can make this formula work so it can calculate the variance even though the shift ends at 02:00am the following morning?

View 5 Replies View Related

Time Formula Not Calculating Correctly?

Feb 19, 2014

In cell BQ65, I have 7:00 pm. Next to that, in cell BR65, I have 6:00 am. This should total 11.00 hours, yet with the formula I have in cell BS65, the result is showing up as 13.00 hours, so I'm ending up with two extra hours, which is not good.

Here's the formula I have in cell BS65:

=IF(ABS((((BR65-BQ65)*1440)/60))=0,"",ABS(((BR65-BQ65)*1440)/60))

View 3 Replies View Related

Calculating Date AND Time Differences

Mar 21, 2006

Within 4 columns, I have a series of:
Start Dates / Start Times / End Dates / End Times
I need to create a formula which will give me the sum total of the difference between the data sets.

For example, the time difference between 8am on the 20th March and 1pm on the 21st March is 29 hours OR 1 day and 5 hours.
I need a formula which will calculate this for me.

View 11 Replies View Related

Calculating Time Across A 24 Hour Period

Nov 6, 2007

Calculate certain time increments for various work-shifts. I have a start time,finish time and increments of time across the spectrum of 24 hours. There are also multiple start time across the 24 hour period with some start times begining on one day and ending on the next day.

Example

In B5 Startime is 22:00
In C5 Finishtime is 06:30

In I3 increment begins at 00:00
In I4 increment ends at 00:30

The employee working the shift from 22:00 - 06:30 would fall into the time increment of 00:00 - 00:30 where another employee working a different shift (08:30 - 17:00) would not. I'm looking for a formula that would return a 1 in a cell if the employee fell into the 00:00 - 00:30 time increment and a 0 in a cell in the employee did not fall into the time increment.

View 11 Replies View Related

How Do I Reduce The Calculating Time In A Workbook

Apr 24, 2009

I have been building a spread sheet data base which I think is pretty simple.

It now has quite a few formula's and is taking time to calculate when I add data.

I have tried to simplify some formula's and get rid of complex one's that can be replaced with simple ones... Is there any advice or suggestions for reducing the calculating time..?

View 12 Replies View Related

Formula For Calculating Average Time?

Sep 24, 2010

I am trying to calculate the average Talk Time into Minutes and Seconds. Every time I have tried, I get the wrong average.

For example, if I have a total of 53 calls and the total talk time is 4:19:05, what would the average talk per call be? I tried doing everything in seconds (this would give me a total of 15545 seconds) but the total I was getting is 4:89.

What would the formula be to get the correct average time?

View 5 Replies View Related

Calculating Date And Time In Excel?

Sep 14, 2011

I am trining to calculate SLA times based on P2, P3 & P4 calls, the only issue is with P2's as the SLA includes the weekend, how to include the weekends into my formula?

AE2 = Priority
U2 = START DATE & TIME
V2 = END DATE & TIME

[Code]....

View 4 Replies View Related

Calculating Time (24 Hour Clock)

Jul 16, 2012

I've calculated the time of hours worked by staff using th 24 hour clock - "hh:mm" format.

When summing up the hours, it goes a bit haywire, i.e.

Staff A = 12:30
Staff B = 14:00
Sum = 02:30

Total should equal 26:30 (26 and a half hours), however I get returned 02:30. I assume it is down to the 24 hour clock and I so I tried adding 24 to it in the formula bar to get the right answer and it didn't work.

View 5 Replies View Related

Calculating Difference In Time Using Excel

Mar 7, 2013

I manage a team in a call centre and deal with lots of reports on excel.

I am dealing with a report on a daily basis that i need to use in order to calculate the total idle time that each of agents have. There are roughly 170 agents.

After I have formatted the cells to hh:mm:ss, the obvious thing to do would be : =(B1-A1)

Is there a way to write a macro (or anything) in order to reapply this to the report on a daily basis? Bearing in mind that this has to be done daily for 170 individual reports. They work a 8am - 5pm shift. So there is an entry every time they make a call.

Agent Name
Extension
Initiateddate
TerminatedDate

MSGR_nmasango
9363

28/02/2013 8:06:47 AM

28/02/2013 8:08:54 AM

[Code] ........

View 1 Replies View Related

Calculating Time Absent Before And After Lunch

Mar 18, 2013

I work at a call center and I have to report absent time for my agents.

I am working on a template that was previously saved, however, the absent hours do not account for when the person has not been to lunch yet. This is usually done as a manual update.

There are three main situations that I need my formula to account for:

1) Person that has been absent all day
2) Person that has been absent part of the day (after lunch)
3) Person htat has been absent only a few hours (before lunch)

Lunches are 30 minutes long

Whenever a person is absent or late, we put their time in two columns called ABS in and ABS out. It basically marks the start and end of their absent time.

ABS IN is when their schedule would have startedABS OUT is when they arrived if they were late, or their out time if they were absent all day.

Here is what my spreadsheet would kind of look like... I need to know how many absent hours there were to calculate the actual worked hours.

I cannot simply deduct abs out minus ABS in because then I am not factoring in the lunch times.

This is how my spreadsheet roughly looks like:

Agent
SCH TIME IN
Lunch
SCH TIME OUT
ABS TIME IN
ABS TIME OUT
Total Sch Hrs
ABS Hours
Actual Worked Hrs

Person absent all day
8:00
12:00
16:30
8:00
16:30
8.00

Arrived late (before lunch)
8:00
12:00
16:30
8:00
10:00
8.00

Arrived late (after lunch)
8:00
12:00
16:30
8:00
13:00
8.00

On Time
8:00
12:00
16:30
8.00

View 1 Replies View Related

Calculating Work Time Formula?

Jul 11, 2013

I'd like a calculation that gives me a total of 7.50 hours instead of 8:00 hours (I'm hoping to add up the time worked into a meaningful hour:minute format)

View 2 Replies View Related

Calculating Time, Timesheet Calculation

Apr 11, 2007

I am working on a project involving calculating time. It is a timesheet calculation. I was able to design the following layout:

.....A............B..........C..........D.......E.....F
1....Date.........Time IN....Time OUT...Hours... Total
2....01/01/07.....1830.......1930.......01:00...01:00
3....01/02/07.....1930.......2330.......04:00...05:00
4....01/03/07......830.......1900.......10:30...15:30
5
Column A is formatted for DATE. Columns B and C are GENERAL. Columns D and E are DATE format customized as '[hh]:mm'

The formula to calculate the time difference between the numbers in column B and C is located in column D. It is as follows:
=IF(C4<1000,TIMEVALUE(LEFT(C4,1)&":"&RIGHT(C4,2)),TIMEVALUE(LEFT(C4,2)&":"&RIGHT(C4,2)))-IF(B4<1000,TIMEVALUE(LEFT(B4,1)&":"&RIGHT(B4,2)),TIMEVALUE(LEFT(B4,2)&":"&RIGHT(B4,2)))..................

View 4 Replies View Related

Basic - Calculating Interest And Time Till Pay Off

Feb 20, 2014

I've uploaded my spreadsheet. I asked my professor if our formula had to take into account whether or not our payment would be changing. As long as I calculated the initial amount - which worked out to be around 30 dollars, I could assume I paid that amount for the entirety.

ASSIGNMENT 4

My latest Mastercard statement shows a balance due of $1,696.96.

Based on recent bills, assume that the minimum payment amount is computed as follows

(balance_owed)*(annual_interest_rate/12) * 2, then rounded to the nearest whole dollar amount.

The minimum payment amount is the amount calculated above or $25.00, whichever is the greater amount.

The annual interest rate for purchases is 11.24%. If I make no more purchases and always pay my bill on time how many years and months will it take be pay it off at the current minimum payment amount each month?

How much total interest would I pay if I repay the loan this way?

What fixed monthly payment amount would pay off the debt in 2 years?

Assume monthly compounding of interest. All calculations are to be done in the Excel worksheet.

The functions NPER() and PMT() will be useful. Note that either the Present Value or Payment amount should be negative (not both) because of conventions concerning the direction of cash flow."

View 3 Replies View Related

Tracking Log Calculating Time Between Email Notifications?

Apr 27, 2014

I receive email, the forward information, then receive email from Help Desk that opens and closes the ticket.

View 13 Replies View Related

Calculating Time To Track Work Hours

Jul 24, 2009

I can't figure out how to do a proper formula for calculating time. For instance I have one column that says "Time In", the next is "Time Out" and the other is "Lunch Time". My calculation needs to be this:

Find the total hours between the Time In and Time Out and then Minus the lunch time to get total hours worked for the day. The only way I can get this to work is using 24 Hour time format. Is there another way?

View 3 Replies View Related

Calculating Hours With Midnight As A Start Time

Oct 1, 2009

Im working on a timesheet and I need to separate the hours worked that are before 6 am from all the others. For example if someone works 1:00 am to 8:00 am I need a cell to populated with 5 representing the hours worked before 6 am. The formula below works fine except when the start time is 12:00 am. I am also having trouble if the start time is before midnight like in a 11pm to 5 am shift.

=(IF(AND(S3<=$AG$97,S3>=$AG$73),(($AG$97-S3))*24,0))

S3 is the start time

AG97 is 6:00 am

AG73 is 12:00 am

View 4 Replies View Related

ADVERTISEMENT