Tracker
HOME    TRACKER    Excel

# Calculate Turn Around Times

## I need to calculate turn around time between two sets of data reflecting both a starting date and time and obviously an ending date and time. This should reflect work hours meaning a day that starts at 08:00 and end at 16:30. That would be a start and would be great if someone can show me how to calculate this. But obviously people don't work on Sundays and only work from 08:00 to 12:00 on Saturdays... Can't even imagine how one would take this into account.

Related Forum Messages:
Calculating Turn Around Time Between Two Times
i want to calculate the TAT between two times. the TAT target is <= 2 hours. i used the following formula

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.

Conditional Formatting (start And End Times Of The Shift To Turn Black Or Red)
I want the start and end times of the shift to turn black or red depending on whether the adjacent cell says "off" of "Hol" respectively. I have this working except for when I actually enter smething into these cell ie a shift, the cell turns black.

VBA Turn Off Auto-calculate
What line of code would I need to used to turn off the auto-calc upon opening a workbook?

Tick Box On Worksheet To Turn Auto Calculate On/off
i can place a tick box on a worksheet that will turn auto calc on/off? The same exact as going through the tools menu but for a lazy person?

Dates And Times To Calculate Together
I seem to have the worst luck when it comes to getting dates and times to calculate together. My logic just seems to go straight out the window.

What I am trying to do, the end result, is get a Time Worked (duration) that I have spent on a ticket, and a Total Time, the time a ticket was opened, to the time it was closed.

I guess we can try to do this one step at a time.

The first thing that I would like to know is if there is a way to calculate Date/Time in a MM/DD/YYYY, H:MM format?

IE:
Ticket Opened ...................Ticket Closed
5/16/2008 2:54 PM..............5/19/2008 10:47 AM
Total Time in Days / Hours / Minutes

Right now I have the Date and Time split into separate cells, simply because I couldnt figure out a way to subtract the combined date and time.

Turn #DIV//0! To 0 Or Even Turn It To A Blank Cell
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?

Calculate Start/End Times
Trying to get times worked in hours and mins, from a given start time to a given finished time.

I would like H18 to read 14
and H19 to read 13.5 and so on,
H23 and H24 are full 24 hour coverage,
but i get a 0, when using 08:00 - 08:00

as we only deal with a full or half hours, the figures should read whole numbers or half number as in th example above.

Also would like to total all hours worked in H28 to 117 as opposed to the figure given in the attached file.

DAY / DATESTARTENDHOURS
MON22-May200618:008:00 AM14:00
TUE23-May200617:006:30 AM13:30
WED24-May200617:307:30 AM14:00
THU25-May200618:007:30 AM13:30
FRI26-May200618:008:00 AM14:00
SAT27-May20068:008:00 AM0:00
SUN28-May20068:008:00 AM0:00

TOTAL2.875

Couldnt upload the file as it was 75kbs, a

Calculate Dates/times Using If Statements?
I'm not too hot with Excel formulas but I'll explain as best I can. I have 3 columns with dates and times

A - 01/08/2008 08:17:08
B - 01/08/2008 12:17:08
C - 01/08/2008 17:00:11

This is what I want to do...

If A > B then Calculate the time difference between A and C
If A <= B then Calculate time difference from B to C

I'd like to get this formula in 1 cell if possible?

Calculate Times That Span 2 Days
I have formated my cells as custom hh:mm and want to have a 24 hour clock calculate time.

In cell D6 I have starting time of 23:00
In cell E6 I have finish time of 07:00

This should equal 7:00 hours but I get ######### am I doing somthing wrong or do I have to format the cells differently.

The formula in F6 is E6-D6.

Calculate Difference Between Times In 1 Cell
I am working on an employee weekly schedule and would like to be able to calculate the amount of hours an employee is scheduled each day. For example; if you worked from 7am to 4pm, I want to have a formula that can determine that (7am to 4pm= 9 hours) then sum the total amount of hours for all employees scheduled that day.

Mon Tue Wed Thrs Fri Sat Sun Total
Employee 1 7-4 7-4 8-5 off 2-10 5-10 off 40
Employee 2 8-5 11-8 off 7-4 1-8 off 8-5 43

Hours 18 18 9 9 15 5 9 83

Calculate Times Greater Than 24 Hours
I have problem to calculate the time which is more than 23:00:00hrs in a file. I need to compare the benchmark time and the difference of time(start time and end time) and show pass/fail depending on benchmark set time. For few of the times which is more than 23:00:00 hrs the format is taking different(example: for 49:15:48, it is showing 02/01/1900 01:15:48 in formaula bar) for which i am not able to use formula and know pass/fail.

Calculate Hours And Minutes Between Two Dates/times..
Im trying to calculate hours and minutes between two dates/times over more than 24hrs. I have:-

A1 is 18/10/08 13:14, B1 is 20/10/2008 12:20, C1 is 1:23:05

Using the formula C1 =INT(B1-A1)&TEXT(B1-A1,":hh:mm")

But what I really need is just Hours and Minutes so the above should read 47:05.

Calculate Hours Worked When Times Are Spread
I got a problem with a time calculation, if it's even possible.

I want to calculate the amount of hours that have been woork in a slot.

Example:
First slot is from 08:00am till 17:00pm, time cost 100%
Second slot is from 1700am till 24:00, time cost 110%
Third slot is from 24:00 till 08:00 am, time cost 120%

I'm working from 08:00am till next day 01:00am
So my hours are:
9 in slot 1,
7 in slot 2,
1 in slot 3.

Is this possible to calculate in Excel?

I got a file attached with a sort of layout.

Calculate Workday Difference Between Dates/Times
I have a spreadsheet with Received Date/Time in C2 and Resolved Date/Tim in D2, Work Start Time in E2, Work End Time in F2. C2 and D2 are in dd/mm/yyyy hh:mm AM/PM format:04/03/2008 10:27 AM06/03/2008 1:06 PM9:00:00 AM5:00:00 PM

I need to be able to calculate the resolution time of each request in hours/mins excluding weekends and public holidays. I've tried using the NETWORKDAYS function but it doesn't seem to pick up multiple days between the Received and Resolved dates/times. I have put the Public Holidays in my spreadsheet ( attached) in AA2-AG2.

Calculate Multiple Times Residing In Single Cells
My goal is to take a list of times which are exported from a database into 1 cell and change the string in that cell to become a function that adds all the times....

Calculate If Time Value In Cell Is Between Times Located In Two Other Cells
I'm trying to calculate whether a time value located in a cell is between the times in two other cells. Here is what I tried:

=IF((AND(\$A62>=C\$55,\$A62

Calculate The Number Of Times Lampard Has Taken And Inswing Delivery
I want to calculate the number of times Lampard has taken and Inswing delivery, and how many times an attempt at goal ocurs from these deliveries.

The equation i'm using is =sum(if(a1:a100="Lampard",if(a3:a100="inswing",1,0)))

However, this is not calculating the correct number, and instead just equating 1.

Calculate Specific Hours Between 2 Dates & Times
I need to calculate the hrs between two date and time and deduct any time outwith the hrs of operation (09:00-19:00), if the date and time falls outwith these times.
I have used the formula 24*IF(A2>G2,G2+1-A2,G2-A2), where G2 IS 21/04/2008 11:45 and A2 is 20/04/2008 00:22, but can't get it right.

Calculate Total Time Worked. Decimal Times
I need to create a formula to calculate monthly hours worked (144.20) by hourly rate (£14.25). So far everything ive tried has given me awrong answer. I cant seem to make the total right. From what ive come up with (Not good) the total accepts the hours-just not the minutes.

Calculate Billable Hours Between 2 Dates & Times
I need to calculate billable hours.I have a start date/time and end date/time.Then I need to subtract out all time between 05:00:00.000 and 20:00:00.000. These are not billable hours.But I can include all weekend time.And I can include all holiday time.I need to accomplish this with formulas, no macros.

Calculate/Display Times Greater Than 24 Hours In TextBox Control
I have made a userform where I calculate how long time an operation takes. If the time fe.g. is 25 hours and 24 minutes then I get the result 1:24. I have attached my userform as it looks now. If you write 540 in the bar and 550 in volume and press "Beregn" then "Norm tid + 10%" will write 1:24 and not 25:24. Is it possible to have the Userform to write 25:24 or 1day and 1 hour and 24minutes?

Calculate Hours Worked Based On 2 Start/End Times
It's been several years sine I had to look at calculating amount of time worked.
Can you please look at this old spreadsheet of mine and verify that the formula is correct?

It appears to be ok to me, but I don't want any errors when it comes to paying my employees

Formula: ...

Total/SUM Various Times: Calculate Various Lengths Of Time Within A Column
Need a ormula that will calculate various lengths of time within a column.

For example: I would like to be able to add
02:43 (2 minutes 43 seconds) plus
01:10 (1 minute 10 seconds) plus
05:15 (5 minutes and 15 seconds)
..and accurately arrive at the sum of
09:08 (9 minutes 8 seconds)

Currently, whenever I input the value of 02:43- and assign the value of 'TIME'.. it reads it as 14:23 (clock time..not length of time.) I will only be using data that follows the format of mm:ss (minutes:seconds).

Calculate Total Hours Difference For Date & Times
I have call data in date/time 06/07/08 2:00 PM custom format for a 2 month period. I have my regular opening times eg Monday 9-3, Tuesday 10-4. For each call I want to know if it was made during opening times or not.

Calculate Duration Based On Start & End Times
I have a spreadsheet with 4 columns - Start Date, Start Time, End Date and End Time. In the 5th column, I need to fill in the "Duration" which is calculated as follows :- Duration = (End Date,End Time) - (Start Date,Start Time)

Formula To Calculate Speed Knowing Distance, Destination Time And Checkpoint Times
On my spreadsheet i have a bunch of variables:

A1 = Arrival time at Checkpoint 1 - 5:53:08
A2 = Time to destination from Checkpoint 1 - 1:10:18

B1 = Arrival time at Checkpoint 2 - 6:00:56
B2 = Time to destination from Checkpoint - 1:02:30

C1 = Total distance from Start - 2.83
C2 = Arrival Time at Destination - =SUM(A1,A2)

D1 = Speed determined from the other variables
D1 is SUPPOSED to equal 25.00

It also is where I am stumped!!

Not only can I not figure out the math but also how to format the function to get the right answer.

ps: Arrival Time at Destination is only included in case it would be useful to find the answer!

Conditional Properties: If A Number In A Different Cell Is Above A Certain Number It Will Turn Green And Another Cell To Turn Red
can I set a cell in excel so that if a number in a different cell is above a certain number it will turn green and another cell to turn red. I have attached the file.

Work Accurately With Times To Calculate The Work Progress Of The People In The Workshop
In a project i am compiling i need to work accurately with times to calculate the work progress of the people in the workshop thus....here goes....

I have in work book #1 (7) sheets mon to fri + complete week + a sheet where all job numbers are collected.

From monday to friday the workmen log their times as a start time and a end time. This has to be then calculated to a total hours:mins spent per job, wich in turn then has to be calculated to a total hours:mins spent per day. And the on the complete week sheet recalculated as a total time worked per week.

Find Duplicates In Column A And Calculate Difference Between Times In Column B
I love this forum, and am usually able to find the help I need without bothering anyone However this one has me stumped and I wonder if anyone can help. It feels like it should be a fairly simple solution, but they can often be the ones that are most eluding LOL!

I have two columns; in column A are incoming telephone numbers and in column B are the date and time the calls were made. (I've put a few hashes in column A just to maintain confidentiality of the numbers, but in reality the cell is formatted as text in order to maintain the leading zero, and entries will follow the format 01234567890)

A sample would look like this:

0##6270####01-Mar-2009 00:01:440##6271####01-Mar-2009 00:03:020##6271####01-Mar-2009 00:03:040##6272####01-Mar-2009 00:16:330##6273####01-Mar-2009 00:30:490##6274####01-Mar-2009 00:55:470##6274####01-Mar-2009 01:06:170##6274####01-Mar-2009 01:07:420##6275####01-Mar-2009 01:08:360##6275####01-Mar-2009 01:11:410##6276####01-Mar-2009 01:13:45

Some numbers only call in once, I need to identify them as only called once.

Some numbers call twice, if they do I need to be able to show time it took between call 1 and call 2.

Some numbers call more than twice. For each successive call I need to be able to show the time since the previous call.

In my mind, the results table would need to look something like this:

NumberTime of callTime between
1st and 2nd call
Time between
2nd and 3rd call
Time between
3rd and 4th call
0##6270####01-Mar-2009 00:01:44Only called once0##6271####01-Mar-2009 00:03:0200:00:020##6272####01-Mar-2009 00:16:33Only called once0##6273####01-Mar-2009 00:30:49Only called once0##6274####01-Mar-2009 00:55:4700:10:3000:01:250##6275####01-Mar-2009 01:08:3600:03:050##6276####01-Mar-2009 01:13:45Only called once

Macro Loop  Find Specific Times, Extract Date, Average Values Of Times
In my workbook I have about 20 sheets (less in the attached sample), and on most sheets Id like to query data for each day and find an average value based on the time and then copy that result into another sheet. Im not sure if Ill need 2 macros for this or if one can be used.

Heres a quick description:

Assuming that I am currently in the sheet I want to run the macro, then I will either input 1:45 or 0:45 into a popup box, or I could always simply input the 1:45 or 0:45 in the code itself. Im flexible

1:45:

If I input 1:45, then Id like the macro to find the first 1:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 1:45 is located, then the macro moves up to find 22:45 in Column B and the value in the same row in Column F will be used in the calculation.....

Convert Start Times And End Times And Minus
I need a formula in excel to convert start times and end times and minus out lunch time taken and then give total hours worked. For instance,

Column D Column E Column F Column G
Start End Lunch Total Hours Worked
10:01AM 7:08PM 1:01 (formula to convert hours worked)

Turn Off * As A Wildcard
The use of * as a wildcard in excel can be extremely useful, however, can it be turned off? I work in the education sector and am currently number crunching GCSE results. When it comes to calculating A* grades versus A grades, B grades, etc, I cannot seem to do it because of the wildcard.

Turn Off Calculations
I have attached a sample file. All the data is inserted into this file via a text file, except one column "G". Someone in this forum was kind enough to help me in being able to include the city name 'G' to a parcel number 'A'. However, the page is constantly trying to complete 'Calculations' and won't let me do anything without first hitting 'Control Break'.

This is fine except that when I try to save the file into .txt, I don't have the option of 'Control Break' and I have to end up closing the file. As the 'real' file has over 100,000 rows, nothing happens very fast. I have tied changing the 'Calculation Options' but that doesn't seem to change anything.

Turn Off Control F4
I have a spreadsheet that is a timed test. One of the people taking the test accidently hit Control-F4 which closed the file and ended their test before the time was up. Is there a way I can turn off Control-F4 without turning off other keyboard functions?

Turn Off Vlookup With A Checkbox
Not sure if this can be done. Here is a example of my project

Using VBA To Turn Off Sound
How can I silence the annoying beep heard whenever a msgbox pops up or an error box appears?

Can I Turn A Cell Into A Drop Down
I know I can right click in cell and choose from the items, but can I actually make the cell a drop-down box, like on websites? Meaning, can I choose which options/numbers/letters/choices I want in the drop down, and be able to right click and highlight one?

Code To Turn Share On/off
Need code to disable and enable "Protect and Share Workbook"?

Turn Dates Into Month
I have many dates 1/21/06,2/1/06,3/16/06,1/23/06 etc...I wish to convert into month only such as
1/21/06 ---> Jan
2/1/06 ---> Feb
3/16/06 ---> Mar etc...

I tried month(1/21/06) but it does not work. Is there a function to do this?

Turn Off Conditional Formatting
I have a color change alert in a cell using Conditional Formatting. Is there a formula that will turn off the alert in that cell once a value, (any value), is entered into the cell?

Find Replace And Turn To Value
I import alot of data from a banking site and for some reason I get a column of data which should be values but is text. It also has a large number of different currency symbols etc preceeding them.
ie:

\$343
£565
3453
Yen54645
RBL60

always text

Can anyone think of a way a macro could be run down the column and strip out the symbols and prefixes and leave the remain numbers as a Value not Text.

Turn Off Assume Formula
I am working with a spreadsheet created by someone else. I am trying to change a date in the spreadsheet from "12/19/07" to "5/1/08". When I type in the new date, excel assumes that I am entering a formula, places an "=" in front and displays 1/1/1900.

I can open a blank spreadsheet, type in the date "5/1/08" and copy it into the cell in the existing spreadsheet and it reads it as a date just fine.

Turn The F1 Key Off In Spreadsheets
I'm going to turn the F1 key off in my spreadsheets so that I do not hit it by accident so manytimes, but how do I bring up help without it?

Turn Calculation To Manual
I'm trying to turn calculation to manual, but there does not seem to be an Options button under tools on the mac I'm using. I've checked another mac and it is also missing. I'll probably end up using a pc for the calculations anyway, but I was wondering if anyone knew what was up?

Calculating Turn Around Time ..
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.

Turn The List Into A Table
I have a long list (almost 17,000 rows) with two columns (ID and Code). There are about 1400 IDs, each with multiple codes:

ID Code
1 ab
1 mn
1 hh
2 mn
2 hh
3 ab

etc.

I need to turn the list into a table that looks like this:

ID Code Code Code ...
1 ab mn hh
2 mn hh
3 ab

Each ID has a different number of codes (anywhere from 1 to 89).

Turn Off All #value! Errors In A Workbook
I have a workbook that contains several formulas that return #value! errors. My formulas are correct and I can remove the error by using an "if" function, but there are a lot of these cells. I just want to make the error show blank to make my workbook look nicer without having to write formulas to avoid the error messages. Is there something I can add in the VBA worksheet function to turn off these errors?

Turn Off Custom Views
I'm dealing with a spreadsheet that uses custom views. Before I do anything with it, I want to make sure that there is no custom view enabled (e.g. just display all). I've tried Chip Pearson's ThisWorkbook.CustomViews("View1").Show suggestion, but it just throws "Runtime Error 5: Invalid procedure call or argument". My actual code is: ThisWorkbook.CustomViews("All (Est)").Show

Turn Values Into Words
is there a way or a formula that turns numbers into their respective words:

ie Cell A1: 8500
Cell A2: Eight Thousand Five Hundred

Big ask I guess but its for a banking model so I can't be the first to have considered this

Turn Off AutoFilter Mode
i have a userform that uses auto filter the filter the search results. I am wondering if someone can show me how to turn the auto-filter off once action (printing) is completed.

below is the code I am using:

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Set ws = Sheets("ComplaintData")

With ws
.AutoFilterMode = False
With . Range("A1:L1")
.AutoFilter
If ComboBox1.Value = "Month" Then
.AutoFilter Field:=11, Criteria1:=ComboBox2

Also, I was given a code in this forum to handle the "Item not found" situation. Where would I insert it in this code. If the searched item doesnt exist, then I would like the message box to come up before it shows the printer selection box and if the searched item exists then directly show the printer selection box.....following were the two lines

On Error Goto ErrFindClick

ErrFindClick: