Excel Functions in combination with Filters (Date Diffrance Calucaltion)

[maximilian]

Hi guys, I’m so pleased I found this forum I hope you can help me out
with a rather tricky problem.


I’m trying to set up an Excel Template with a number of function built
in it so the average Joe shmo user can easily copy/paste a list of rows
in to my Template and by using the filters and functions, to do he’s
work.

Right now there is just one thing that’s in my way of completing my
task.
[image:
Here’s the situation:

The Template I’m working on has over 15 columns but right now we only
need 3 of them:

http://www.theforumisdown.com/uploadfiles/0103/exl123.JPG]

Coolum A: Shows the date on which a “call” has been made.
Coolum B: Shows the date the “call” has been closed OR the date of last
action taken ( so the “call” is still open.
Column C: Shows the status of the Call. O: Open Call G: Closed
Call.

What I want to accomplish?

I want the difference of Column A and B shown in a new Column, (D)
Based on its status.

So, If a Call is closed I want to know how long it took for it to be
closed. Thus:
IF Column C.Filter= G then ColumnB – ColumnA

And If a Call is still open, I want to know
IF Column C filter = O NOW() – ColumnA


Still with me? I hope so.

My too major problems are:

a) How do I make a function that calculates the entire sheet. Everyday
the number of Rows may vary. So one day it might be 10000 rows, next
day it can be half.
b) I want the results to be shown in ONE Column for both OPEN and
CLOSED calls.

I know I could make 2 Columns for open and closed calls but I rather
not, and if do I’d still have problem a).

I would be more than happy to answer any question about my complicated
problem.

Thank you very much for any help.

 

[Arvi Laanemets]

Hi

D2=IF(A2="","",IF(C2="G",B2,NOW())-A2)
and copy it down as much as you do need.

When the started call is closed, B-A is calculated;
When the started call isn't closed, running time interval from last
Calculation is calculated (when Calculation is set to automatic, the sheet
is recalculated when you change something on it. You can enforce
recalculation manually, pressing F9)
When starting time is empty, nothing is returned (it allows you have
formulas inserted for future use)

To avoid shrinking of range with formulas, don't use deleting on this
sheet - use Clear Contents instead. As bonus you haven't bother about
messing up your formulas too.


Arvi Laanemets

 

[maximilian]

Hi,, thanks for your reply.

But the function you gave me isnt working, I get an error at * "","",IF
*


is it posible for me to send you the sheet so you could have a better
look?

 

[JMay]

Maybe/Try:
D2 >>> =IF(A2="","",IF(C2="G",B2,NOW()-A2))
HTH

 

[maximilian]

nothing, again an error.

Lets try something more simple, I’ll just make 2 columns, one for open
calls and one for close.


only thing I need to know now is how to set up a function per Column so
that it calculates all the rows at once.


This is how things look like,
http://www.theforumisdown.com/uploadfiles/0103/helpmeeeee.JPG

 

[maximilian]

bump.

 

[maximilian]

bump.