@Need help with a formula

[smoss]

Sorry if in the wrong category...........

My Formula in D6
=IF(AND(ISNUMBER(C6),ISNUMBER(C5)),IF(IF(C6-C5>$N$3,C6-C5-$H$3,IF(C6-C5>0,C6-C5,0))=0,"",IF(C6-C5>$N$3,C6-C5-$H$3,IF(C6-C5>0,C6-C5,0))),"")

C5 = Employee start time
C6 = Employee stop time
D5 = 1 or .5 for lunch
I would like the number that I enter into D5 (either 1 or .5) to be
subtracted from D6

Thanks In Advance

 

[Tom Hutchins]

Can't you just add -D5 to the end of your formula in D6?

Hope this helps,

Hutch

 

[David Biddulph]

I assume that in the cases where you're asking D6 to return an empty string,
you still want an empty string?

Do you want the answer to be in D6 or in another cell?

If you want the answer in another cell, use the formula =IF(D6="","",D6-D5)

If you want the answer in D6, change your existing formula from
=IF(AND(ISNUMBER(C6),ISNUMBER(C5)),IF(IF(C6-C5>$N$3,C6-C5-$H$3,IF(C6-C5>0,C6-C5,0))=0,"",IF(C6-C5>$N$3,C6-C5-$H$3,IF(C6-C5>0,C6-C5,0))),"")
to
=IF(AND(ISNUMBER(C6),ISNUMBER(C5)),IF(IF(C6-C5>$N$3,C6-C5-$H$3,IF(C6-C5>0,C6-C5,0))=0,"",IF(C6-C5>$N$3,C6-C5-$H$3,IF(C6-C5>0,C6-C5,0))-D5),"")

But (in either case) if the numbers in C6 and C5 are Excel times (which are
measured in days) and if your D5 is in hours, then you'll need to convert
(such as replacing
-D5 by
-D5/24).

 

[smoss]

I get a #value error if D5 is blank <emp. is not working that day> or a
########

 

[smoss]

Answer is in D6

D6 cell format is h:mm

I am not sure what you are asking about in the empty string...if there is no
input in D5 then I want it to not calculate that, but still calculate C6 and
C5

I tried your formula and got a ######## error

 

[Tom Hutchins]

Since you are working with hours & minutes, I think you need to enter 1:00 or
0:30 in D5 instead of 1 or .5. D5 being empty should not cause an error; I
get #value if C5 or C6 is empty or if C5 < C6. Of course, I don't have your
spreadsheet, just a mock-up.

Hope this helps,

Hutch

 

[David Biddulph]

If you're getting #####, then it sounds as if you've either got the column
too narrow for the data, or you've got a negative time as a result. [You
can check the latter by temporarily formatting the result as General or
Number]. If, of course, you don't want the answer as time, but as a number
of hours, you can multiply the answer by 24, and then it won't object to a
negative number.

If your formula in the repeated IF statement might be coming to less than D5
(or D5/24), then you can either limit the answer to zero, or output as an
empty string "".

 

[smoss]

The answer would be time h:mm format and it is coming up as a negative number.

It has got to be the time format that I have in D5.


--
smoss

If you're getting #####, then it sounds as if you've either got the column
too narrow for the data, or you've got a negative time as a result. [You
can check the latter by temporarily formatting the result as General or
Number]. If, of course, you don't want the answer as time, but as a number
of hours, you can multiply the answer by 24, and then it won't object to a
negative number.

If your formula in the repeated IF statement might be coming to less than D5
(or D5/24), then you can either limit the answer to zero, or output as an
empty string "".

 

[David Biddulph]

If you want to show a negative time in Excel time format, you'll have to use
the 1904 date system (but beware of problems going to & fro between 1900 &
1904 systems).

Instead you could produce text that looks like Excel time format by using a
formula like
=IF(D5<0,"-"&TEXT(-D5,"[h]:mm"),TEXT(D5,"[h]:mm"))
--
David Biddulph

The answer would be time h:mm format and it is coming up as a negative
number.

It has got to be the time format that I have in D5.

If you're getting #####, then it sounds as if you've either got the
column
too narrow for the data, or you've got a negative time as a result.
[You
can check the latter by temporarily formatting the result as General or
Number]. If, of course, you don't want the answer as time, but as a
number
of hours, you can multiply the answer by 24, and then it won't object to
a
negative number.

If your formula in the repeated IF statement might be coming to less than
D5
(or D5/24), then you can either limit the answer to zero, or output as an
empty string "".
--
David Biddulph

Answer is in D6

D6 cell format is h:mm

I am not sure what you are asking about in the empty string...if there
is
no
input in D5 then I want it to not calculate that, but still calculate
C6
and
C5

I tried your formula and got a ######## error
--
smoss
:

I assume that in the cases where you're asking D6 to return an empty
string,
you still want an empty string?

Do you want the answer to be in D6 or in another cell?

If you want the answer in another cell, use the formula
=IF(D6="","",D6-D5)

If you want the answer in D6, change your existing formula from
=IF(AND(ISNUMBER(C6),ISNUMBER(C5)),IF(IF(C6-C5>$N$3,C6-C5-$H$3,IF(C6-C5>0,C6-C5,0))=0,"",IF(C6-C5>$N$3,C6-C5-$H$3,IF(C6-C5>0,C6-C5,0))),"")
to
=IF(AND(ISNUMBER(C6),ISNUMBER(C5)),IF(IF(C6-C5>$N$3,C6-C5-$H$3,IF(C6-C5>0,C6-C5,0))=0,"",IF(C6-C5>$N$3,C6-C5-$H$3,IF(C6-C5>0,C6-C5,0))-D5),"")

But (in either case) if the numbers in C6 and C5 are Excel times
(which
are
measured in days) and if your D5 is in hours, then you'll need to
convert
(such as replacing
-D5 by
-D5/24).
--
David Biddulph
Sorry if in the wrong category...........

My Formula in D6
=IF(AND(ISNUMBER(C6),ISNUMBER(C5)),IF(IF(C6-C5>$N$3,C6-C5-$H$3,IF(C6-C5>0,C6-C5,0))=0,"",IF(C6-C5>$N$3,C6-C5-$H$3,IF(C6-C5>0,C6-C5,0))),"")

C5 = Employee start time
C6 = Employee stop time
D5 = 1 or .5 for lunch
I would like the number that I enter into D5 (either 1 or .5) to be
subtracted from D6

Thanks In Advance