i need to seperate total time from billable time & non billable ti

L

Lorie

My spread sheet that has start time and stop time, then total time worked in
minutes using =(C3-B3)*1440
Here is the problem - that is total time…. We have to list the type of
service we are providing and we do that by entering 1-6 in another column,
but only 1-5 is billable time, and 6 is Unbillable time. How can I create a
column that will only show billable time in one column and non billable time
in another column. Such as if in the one column were we enter the type of
service (1 thru 6) the bill able time would only pull the data for the
numbers 1-5 and the non billable column will only pull those numbered 6. I
can’t seem to figure out a formula to do this.

Can any of you help me?
 
L

Lorie

I have a column and we enter a number which is 1 thru 6

1 through 5 are all billable, and 6 is non billable.

We are case managers, and some of our time is non billable... the rest of
the things we do are billable
 
R

Ragdyer

Say you use Column K to show the type of service (1 - 6).
Say Column D is total time.
Column E is billable time.
Column F is non-billable.

To account for times that might cross midnight, enter this formula in D3:
=MOD(C3-B3,1)*1440

In E3 enter:
=(K3<>6)*D3

In F3 enter:
=(K3=6)*D3

Then, select D3, E3, and F3,
And drag down the 3 cell selection to copy as needed.
 
L

Lorie

The Billable worked, but the non billable doesn't seem to work....

where there is a 6 for the type of service (which is non billable), it comes
back with a 0 but we would like to know how much time we do spend that is non
billable for each client...

I feel your close to the answer......

my fingers are crossed....
 
L

Lorie

I hope your not giving up on me.... *smile* I am still trying and i just am
not getting anywhere with the non billable time showing up.....the formula's
i try just aren't getting it... the one you gave me give's me a 0, but i need
to show the time that we do non billable stuff.....

Thank you for trying..... i keep checking back to see if you have replied
back again...
it is so frustrating to be so close, yet so far....
 
D

David Biddulph

The formulae you are given should give the right answers, so you need to
check your implementation of the formula in F3 and make sure you haven't
mistyped. Also check that you've really got numbers in the cells, not text
which looks like numbers (possibly with extra spaces or non-printing
characters). If in doubt check with =ISNUMBER(K3), also check to see what
=K3=6 gives.

If you want to show a blank, rather than a zero, in the column which isn't
applicable for each row, you could use
In E3 =IF(K3<>6,D3,"")
In F3 =IF(K3=6,D3,"")
 
R

RagDyeR

One of the main issues with these NGs is trying to understand *exactly* the
requests of the OPs (original poster - you!).

You have 3 columns, calculating your keyed in times.

D = Total time for row
E = Billable time for the row
F = Non-billable time for the row

NOW, try to explicitly indicate exactly what you are looking for ... that
these 3 columns are not giving you.

Do you want additional columns with other information?

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

I hope your not giving up on me.... *smile* I am still trying and i just
am
not getting anywhere with the non billable time showing up.....the formula's
i try just aren't getting it... the one you gave me give's me a 0, but i
need
to show the time that we do non billable stuff.....

Thank you for trying..... i keep checking back to see if you have replied
back again...
it is so frustrating to be so close, yet so far....
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top