| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
@Need help with a formula
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-C50,C6-C5,0))=0,"",IF(C6-C5$N$3,C6-C5-$H$3,IF(C6-C50,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 -- smoss |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
@Need help with a formula
Can't you just add -D5 to the end of your formula in D6?
Hope this helps, Hutch "smoss" wrote: 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-C50,C6-C5,0))=0,"",IF(C6-C5$N$3,C6-C5-$H$3,IF(C6-C50,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 -- smoss |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
@Need help with a formula
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-C50,C6-C5,0))=0,"",IF(C6-C5$N$3,C6-C5-$H$3,IF(C6-C50,C6-C5,0))),"") to =IF(AND(ISNUMBER(C6),ISNUMBER(C5)),IF(IF(C6-C5$N$3,C6-C5-$H$3,IF(C6-C50,C6-C5,0))=0,"",IF(C6-C5$N$3,C6-C5-$H$3,IF(C6-C50,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 "smoss" wrote in message ... 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-C50,C6-C5,0))=0,"",IF(C6-C5$N$3,C6-C5-$H$3,IF(C6-C50,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 -- smoss |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
@Need help with a formula
I get a #value error if D5 is blank <emp. is not working that day or a
######## -- smoss "Tom Hutchins" wrote: Can't you just add -D5 to the end of your formula in D6? Hope this helps, Hutch "smoss" wrote: 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-C50,C6-C5,0))=0,"",IF(C6-C5$N$3,C6-C5-$H$3,IF(C6-C50,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 -- smoss |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
@Need help with a formula
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 "David Biddulph" wrote: 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-C50,C6-C5,0))=0,"",IF(C6-C5$N$3,C6-C5-$H$3,IF(C6-C50,C6-C5,0))),"") to =IF(AND(ISNUMBER(C6),ISNUMBER(C5)),IF(IF(C6-C5$N$3,C6-C5-$H$3,IF(C6-C50,C6-C5,0))=0,"",IF(C6-C5$N$3,C6-C5-$H$3,IF(C6-C50,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 "smoss" wrote in message ... 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-C50,C6-C5,0))=0,"",IF(C6-C5$N$3,C6-C5-$H$3,IF(C6-C50,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 -- smoss |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
@Need help with a formula
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 "smoss" wrote: I get a #value error if D5 is blank <emp. is not working that day or a ######## -- smoss "Tom Hutchins" wrote: Can't you just add -D5 to the end of your formula in D6? Hope this helps, Hutch "smoss" wrote: 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-C50,C6-C5,0))=0,"",IF(C6-C5$N$3,C6-C5-$H$3,IF(C6-C50,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 -- smoss |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
@Need help with a formula
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 "smoss" wrote in message ... 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 "David Biddulph" wrote: 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-C50,C6-C5,0))=0,"",IF(C6-C5$N$3,C6-C5-$H$3,IF(C6-C50,C6-C5,0))),"") to =IF(AND(ISNUMBER(C6),ISNUMBER(C5)),IF(IF(C6-C5$N$3,C6-C5-$H$3,IF(C6-C50,C6-C5,0))=0,"",IF(C6-C5$N$3,C6-C5-$H$3,IF(C6-C50,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 "smoss" wrote in message ... 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-C50,C6-C5,0))=0,"",IF(C6-C5$N$3,C6-C5-$H$3,IF(C6-C50,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 -- smoss |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
@Need help with a formula
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 "David Biddulph" wrote: 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 "smoss" wrote in message ... 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 "David Biddulph" wrote: 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-C50,C6-C5,0))=0,"",IF(C6-C5$N$3,C6-C5-$H$3,IF(C6-C50,C6-C5,0))),"") to =IF(AND(ISNUMBER(C6),ISNUMBER(C5)),IF(IF(C6-C5$N$3,C6-C5-$H$3,IF(C6-C50,C6-C5,0))=0,"",IF(C6-C5$N$3,C6-C5-$H$3,IF(C6-C50,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 "smoss" wrote in message ... 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-C50,C6-C5,0))=0,"",IF(C6-C5$N$3,C6-C5-$H$3,IF(C6-C50,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 -- smoss |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
@Need help with a formula
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 "smoss" wrote in message ... 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 "David Biddulph" wrote: 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 "smoss" wrote in message ... 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 "David Biddulph" wrote: 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-C50,C6-C5,0))=0,"",IF(C6-C5$N$3,C6-C5-$H$3,IF(C6-C50,C6-C5,0))),"") to =IF(AND(ISNUMBER(C6),ISNUMBER(C5)),IF(IF(C6-C5$N$3,C6-C5-$H$3,IF(C6-C50,C6-C5,0))=0,"",IF(C6-C5$N$3,C6-C5-$H$3,IF(C6-C50,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 "smoss" wrote in message ... 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-C50,C6-C5,0))=0,"",IF(C6-C5$N$3,C6-C5-$H$3,IF(C6-C50,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 -- smoss |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
