Calculating turaround time using IF function

I'm just learning to use the IF function, and it is not calculating
correctly, so am looking for help.  I'm using Excel 2000.

For my spreadsheet, if a document is made available after 1700 hours
or before 0700 hours the following day, I want it to calculate the
turnaround time for those reports to begin at 0700 hours; otherwise
calculate the remainder of the reports from the time the report became
available to completion.  Here's the IF statement I'm attempting to
use with little success.

E=Time report available
I=Finish time

=IF(OR(E>"17:00:00",E<"07:00:00),0700-I,I-E)

0
MT_Cybertyper
1/13/2010 3:25:32 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
1267 Views

Similar Articles

[PageSpeed] 5

MT_Cybertyper wrote:
> I'm just learning to use the IF function, and it is not calculating
> correctly, so am looking for help.  I'm using Excel 2000.
> 
> For my spreadsheet, if a document is made available after 1700 hours
> or before 0700 hours the following day, I want it to calculate the
> turnaround time for those reports to begin at 0700 hours; otherwise
> calculate the remainder of the reports from the time the report became
> available to completion.  Here's the IF statement I'm attempting to
> use with little success.
> 
> E=Time report available
> I=Finish time
> 
> =IF(OR(E>"17:00:00",E<"07:00:00),0700-I,I-E)
> 

Copy your formula directly from the worksheet to the post if you want help.  The 
one you typed above is not valid.

Is it possible a report is made available before 17:00 but finished the next day?

You might be looking for something like this:

=IF(OR(E1>--"17:00:00",E1<--"07:00:00"),I1-"07:00:00",I1-E1)
0
Glenn
1/13/2010 3:52:52 PM
On Jan 13, 10:52=A0am, Glenn <addr...@not.valid> wrote:
> MT_Cybertyper wrote:
> > I'm just learning to use the IF function, and it is not calculating
> > correctly, so am looking for help. =A0I'm using Excel 2000.
>
> > For my spreadsheet, if a document is made available after 1700 hours
> > or before 0700 hours the following day, I want it to calculate the
> > turnaround time for those reports to begin at 0700 hours; otherwise
> > calculate the remainder of the reports from the time the report became
> > available to completion. =A0Here's the IF statement I'm attempting to
> > use with little success.
>
> > E=3DTime report available
> > I=3DFinish time
>
> > =3DIF(OR(E>"17:00:00",E<"07:00:00),0700-I,I-E)
>
> Copy your formula directly from the worksheet to the post if you want hel=
p. =A0The
> one you typed above is not valid.
>
> Is it possible a report is made available before 17:00 but finished the n=
ext day?
>
> You might be looking for something like this:
>
> =3DIF(OR(E1>--"17:00:00",E1<--"07:00:00"),I1-"07:00:00",I1-E1)


Tried it out and yes, this works, except for those reports that are
available before 17:00.  When this occurs, it would need calculated
from the time in that cell to 17:00 and then from 07:00 until
completion the next day.  Not sure how to set this up.

Thanks for your help

0
MT_Cybertyper
1/14/2010 5:50:48 PM
Reply:

Similar Artilces:

Looking for an excel function which can mirror opposite the data
Dear sir, There are 2 data sets is assumed running in A1 down, viz.: From A1 down to A10, are showing a set of number: from 1 to 10 In the cell from B1 to B10, are showing from 10 to 1, which is decreasing from 10 to 1. My question: I want to know is there any excel formula which I can place in B1 and pick A10's number; B2 pick A9's number and etc. I know there is an easy way to do it, just type "=A10" in the cell of B1, but it is quite difficult to do it when I have more than 200 number, e.g. A1 to A200. Thanks for your advice, Wilchong -- Message posted via OfficeKB...

Decimal to time conversion
How do i convert 75.2 minutes to show 75 minutes & 11 seconds (75:11) or 4511 seconds to show 75 minutes & 11 seconds (75:11) in Excel can it be done or am i being thick???:confused: :confused: ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Try tips posted here http://www.exceltip.com/show_tip/Excel_Time/Converting_time_to_decimals_in_Microsoft_Excel/96.html ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~...

Publishing Free/Busy Time
I was reading in the Help section that I can set our PCs to publish each person's Free/Busy time to our local server. Can anyone give me any extra information on hos I do this? I went to Tools/Options/Calendar Options/Free-Busy Time and foudn where I can setr it to do that. THe question is how exactly do I specify where to publish the information to, and how do I get all my PCs to be able to retireve the informatino to make it useful to everybody? THere's no Browse button so do I need to just type in a folder such as (X:\schedule) or something different? Just can't get it to wo...

User defined functions aware of what cell they are placed in?
Hi, I would like to make a user defined function which needs to know in what cell and what worksheet it is placed in. I will be using this UDF in multiple cells on multiple worksheets. I originally just passed the cell row and column as parameters to the UDF however this ended up updating all worksheets and not just the one the UDF was on. Is there any way to do this? Option Explicit function myfunct(something as somethingelse) as something msgbox application.caller.address & vblf _ & application.caller.parent.name & vblf _ & application.caller.pare...

How do I display daily duty timings (day/night) of employees for .
I am looking for a template that would permit me to display the work timings of my 30 employees. They either work the day shift or the night shift. ...

How do I convert time format to text?
I have a cell with a formula of =text(c3-b3,"h:mm") as the difference from cell b3 and c3. Assume the result is 5:00. I need to minus another cell value that is formatted as general text. eg. =text(c3-b3,"h:mm")-G3 Thanks....Andrew Hi Andrew- Try the VALUE() fx and format that cell with your choice of Time Formats. HTH |:>) "Andrew" wrote: > I have a cell with a formula of =text(c3-b3,"h:mm") as the difference from > cell b3 and c3. Assume the result is 5:00. I need to minus another cell value > that is formatted as general text. ...

Switching companies using SQL Passthrough
I have an application that uses SQL_Passthrough. As part of the code you must execute a statement that uses the appropriate database. The code looks like this: set SQL_Statements to "use MYDB"; status = SQL_Execute(SQL_connection, field SQL_Statements); This works fine, but my application can be used for any number of databases. At first, I modified it to use the Dex.ini file, which works. Here is the modification: dbname = Defaults_Read("SQLDB"); dbopencommand = "use " + dbname; set SQL_Statements to dbopencommand; status = SQL_Execute(SQL_connection,...

Run-time error 2465 after database import
I'm trying to tidy up a database by starting with a fresh, blank database (Access 2000) and importing everything from the original database. Here are the steps I've gone through: 1. Started with a blank database. 2. Set all the properties to be the same as the original database. I've set the properties in: - File / Database Properties - Tools / Startup - Tools / Options - Visual Basic Editor - Tools / database Properties - Visual Basic Editor - Tools / Options - Visual Basic Editor - Tools / References 3. Selected File / Get External Data / Import and ...

Using Word 2003 in Vista: Opening dictionary shuts down Word
This is a problem in Vista; it did not occur when I used Word 2003 in XP. Whenever I try to open the dictionary in Word 2003, either by clicking its icon, or hitting Alt+click over a word, Word shuts down. Vista Business, Service Pack 2 Thinkpad T400, Intel Core 2 Duo CPU, 3GB RAM ...

Using namespaces? I've some messy nested contexts that I want to clean up...
Hi, I've got the following code structure Class A { ... private: Class B { public: enum C { ENUM X } C MyVar; C MyFunc() } } So for function definitions in B I have to write A::B::C A::B::MyFunc() and for objects of B in A if(pb->MyVar==B::ENUM_X) It's all just a bit messy. Isn't it. Someone please help. Regards. ...

=IF(NOT(ISNA(VLOOKUP Function
I am using Excel 2003 I am trying to produce a summary shett from five differnet worksheets. I am using the following function =IF(NOT(ISNA(VLOOKUP(E7,Tests!D1:Tests!S147,18,FALSE))),VLOOKUP(E7,Tests!D1:Tests!S147,18,FALSE),""). It works fine until I reach 18 and above and it returns #REF!. Does this formula not work after 17 or is there another formula that I should be using. Colin Hi, The VLOOKUP() table array cannot span across sheets. Do let us know what you are trying to do? -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com ...

Old emails not displayed (Multiple PC's using same inbox)
I have 2 machines running XP and Outlook 2000. Both mahcines are setup to using the same Exchange Server email account. When i open outlook in one machine all mail are visable is the inbox, and new mail is received correctly. But if i opne Outlook on the second machine my inbox is empty. Any new mails will appear quickly then disappear. I cannot get the second machine to display old emails. If I leave the first machine off, all new mail will stay in the second machines inbox, but if I open outlook on the second machine all mail disappears and is shown in the first machines inbox. A...

ProbleM: when I restore a mailbox using Exmerge with a pst file, nothing is transferred.
Hi, I am practising Exmerge for a big remote site migration in a couple of weeks. One thing I dont understand is that I can backup one test mailbox fine using Exmerge (I know this works, as I have opened the mailbox pst file within outlook and everything is there), but when I perform the restore using the pst file, nothing happens. There is no error messages, and Emerge goes through the motions (though it finishes supsiciously quick), but when I open the mailbox, no emails have been restored. Although it is great that Exmerge is working for the backup part of the stage, I am disappointed it i...

Formula to process 3 cells using IF statements
I have 3 columns of experimental data (C:E). Row 30 contains the sums (C30:E30). I need a formula that will examine the three sums and return the column number that has the lowest sum. If more than one column is lowest, select one randomly. Example: C30 D30 E30 Result 10 11 12 1 (C) 22 20 21 2 (D) 32 31 30 3 (E) 40 41 40 Randomly select 1 or 3 51 50 50 Randomly select 2 or 3 60 60 60 Randonly select 1, 2, or 3 Can this be done with IF statements or do I need to write a macro? Well, this is a bit cumbersome, but it se...

Windows Installer Configuration EVERY TIME!!!
Ok, so I am sure this sounds pretty familiar. Every time I open an office application, or an office document, I get a pop up of the windows installer and it starts running some mystery configuration process. I have tried uninstalling/reinstalling. I have tried repairing the registry permissions. I have ripped all office traces out by force and done a clean install. I have rebuilt the registry, modified it, added some conditional entries, but to no avail. I have done it in the user account, and the built in administrator account. I have done both custom and full installs. ...

Pointing to correct macro path using excel custom toolbar
I have created an excel 2000 template (.xlt) containing a number o macros. When I open copies of this template on various pcs, the macro function correctly, except I cannot successfully run the macros usin the custom toolbar I created, because (I think) within the toolbar th paths to the macros are pointed to the original location on my pc. An advice on how I can resolve this would be gratefully received -- Message posted from http://www.ExcelForum.com Have you thought about building the toolbar when the file opens? Or maybe separating the worksheet portion of the template from the code pa...

how do i enter data for a # of years using a formula?
i am working on excel and the book asks that i enter data s=using formulas for specifically the last three years of what i am referencing to. and i have to know how to us the copy command button. can anyone help ...

Entire Visio page moves whenever I use the directional arrow keys instead of the object I've clicked on
Gurus, Running Visio 2003. For some reason lately, whenever I click on an object and try to move it using the right, left, or up or down arrow keys, the whole page moves instead of the object I've clicked on. This is really annoying! It didn't used to be this way. I'm not sure what I changed. I simply want the object I've highlighted to move whenever I use the arrow keys not the whole Visio page itself! -- Spin On Fri, 24 Oct 2008 17:29:14 -0400, "Spin" <Spin@invalid.com> wrote: >Gurus, > >Running Visio 2003. For some reason lately, whe...

Do a calculation in cells with text data format
I have a few columns of cells having a mixed data format of number and text. Is it possible to convert the first row of numbers in text data format for further calculation? Your guidance to accomplish it is appreciated. Thanks, Ray Example? -- Regards, Peo Sjoblom "Ray" <NoSpam-ZQLi@GMail.com> wrote in message news:ei$Jbmy$FHA.216@TK2MSFTNGP15.phx.gbl... > I have a few columns of cells having a mixed data format of number and text. > Is it possible to convert the first row of numbers in text data format for > further calculation? Your guidance to accomplis...

Calculating the days in a month
I have a Control which is a Date field named 'NEWTOPD'. Based on the date entered in the field it calculates the days offor the whole month making amends for Leap-year as well. If I want only the number of days in that month starting from the date entered in 'NEWTOPD' - to be entered in a calculated text field (which is a number field) named 'Text361' how should I modify the following code? Text361 stores the data in (PDDAYS). 'UPDATE (PDDAYS) BASED ON NEWTOPD DATE Dim iMon As Integer Dim sDate As String ...

Access 2007 pivot
I have a complex query that is based on ERP data exports. I am supporting a multi-dimensional matrix organization. A pivot table would be a perfect tool for analysing sales, margins, and changes for different business units. I have been using Excel pivots a lot and also in this case started with excel. However, the amount of data and usability of reports limits the possibility to distributing information in excel. With Excel I was able to all the tricks especially using the calculated item functionality, which is missing from the access pivot component. What I would like to ha...

How Do I create a Timing Tool
All, I wonder if I can call on your expert advice. I am looking to create a Timing Tool within Excel which will be able t capture timings of specific user defined areas. For Example looking to time specific sections of say for a telephon call and the interaction the agent has with the Computer System..ho long specific actions take...and then from that be able to submit th data into a sheet..and move on to another one. This is so that summary data can be captured and analysed. All help appreciated. Regards Tim Hardin -- Message posted from http://www.ExcelForum.com Tim, It depends upo...

.NET 3.5
Hello, Does anyone of you know how I can force vb.net to remove the handle of a local DLL which I used beforehand with Reflection. Here is a fragment of the code. I have a base class and a derived class. This app only knows the base class and creates an Instance of the derived class by using Reflection which works fine. The only problem I have is that the local DLL file is blocked (I can't delete it) as long as I don't close the Application Dim GetCustomModule as BaseClass Dim dll As Assembly = Assembly.LoadFrom("c:\tools\DerivedCass.dl...

Time calculation #2
I am trying to figure out a production problem that deals with job completion and shifts. There are 3 shifts working: 1st shift: 07:30-15:30 2nd shift: 15:30-23:30 3rd shift: 23:30-07:30 A job can be started and completed in 3 possibilities. 1) Starts and finshes on 1 shift >= 1 hour before end of a shift. 2) Starts and finishes on 1 shift < 1 hour before end of a shift. 3) Starts on 1 shift and ends on another shift. I will have a column for job start time and a column for job finish time. I need a third column to show the following possibilities: 1st shift (job was started 1st s...

Cannot change cost basis calculation method
The online help for Money 2003 says that I should be able to change the method used to calculate the cost basis of a mutual fund. However, when I go to the details page for a mutual fund, no such option exists. Is this a bug in the documentation, or the program? In microsoft.public.money, Paul Galbraith wrote: >The online help for Money 2003 says that I should be able to change the >method used to calculate the cost basis of a mutual fund. However, when I >go to the details page for a mutual fund, no such option exists. Is this a >bug in the documentation, or the program? ...