|
example if IF(ISNA()) function
Hi
Please could someone assist. I have a formular: =IF((E34="Not Done"),NOW()-C34,"") which works but I am trying to return a "" value when "Not done" is not displayed. I have tried using =IF(isna((E35="Not Done"),NOW()-C35,""),"",((E35="Not Done"),NOW()-C35,"")) but this is not working. Thks Gus |
Not sure what you're trying to do but definately you have some
bracket-problems on the second if-statement. Maybe the one below helps: = IF( ISNA(E35 = "Not Done"), "", IF( E35 = "Not Done", NOW() - C35, "")) What is wrong with the first if-statement (it already returns "" if "Not done" is not displayed) JP "Gus" schreef in bericht ... Hi Please could someone assist. I have a formular: =IF((E34="Not Done"),NOW()-C34,"") which works but I am trying to return a "" value when "Not done" is not displayed. I have tried using =IF(isna((E35="Not Done"),NOW()-C35,""),"",((E35="Not Done"),NOW()-C35,"")) but this is not working. Thks Gus |
Hi JP
Many thks for replying! I am trying to calculate the number of days between a deadline date (column C) and todays date if "Not done" is entered into column E, alternative if text (or blank) is in Column C, then nothing is returned. The problem I have with the 1st IF statement (& also the one you reccommended) is #VALUE! is displayed in the column when I have text entered in column c. RD Gus "Johannes" wrote: Not sure what you're trying to do but definately you have some bracket-problems on the second if-statement. Maybe the one below helps: = IF( ISNA(E35 = "Not Done"), "", IF( E35 = "Not Done", NOW() - C35, "")) What is wrong with the first if-statement (it already returns "" if "Not done" is not displayed) JP "Gus" schreef in bericht ... Hi Please could someone assist. I have a formular: =IF((E34="Not Done"),NOW()-C34,"") which works but I am trying to return a "" value when "Not done" is not displayed. I have tried using =IF(isna((E35="Not Done"),NOW()-C35,""),"",((E35="Not Done"),NOW()-C35,"")) but this is not working. Thks Gus |
try
=IF( AND( ISNUMBER(C34), E34="Not Done"), NOW()-C34,"") problem is that the function NOW() - C34 is #VALUE if C34 is not a number. works better now? JP "Gus" schreef in bericht ... Hi JP Many thks for replying! I am trying to calculate the number of days between a deadline date (column C) and todays date if "Not done" is entered into column E, alternative if text (or blank) is in Column C, then nothing is returned. The problem I have with the 1st IF statement (& also the one you reccommended) is #VALUE! is displayed in the column when I have text entered in column c. RD Gus "Johannes" wrote: Not sure what you're trying to do but definately you have some bracket-problems on the second if-statement. Maybe the one below helps: = IF( ISNA(E35 = "Not Done"), "", IF( E35 = "Not Done", NOW() - C35, "")) What is wrong with the first if-statement (it already returns "" if "Not done" is not displayed) JP "Gus" schreef in bericht ... Hi Please could someone assist. I have a formular: =IF((E34="Not Done"),NOW()-C34,"") which works but I am trying to return a "" value when "Not done" is not displayed. I have tried using =IF(isna((E35="Not Done"),NOW()-C35,""),"",((E35="Not Done"),NOW()-C35,"")) but this is not working. Thks Gus |
Many thanks, greatlly appreciated your formular has worked wonders!
Gus "Johannes" wrote: try =IF( AND( ISNUMBER(C34), E34="Not Done"), NOW()-C34,"") problem is that the function NOW() - C34 is #VALUE if C34 is not a number. works better now? JP "Gus" schreef in bericht ... Hi JP Many thks for replying! I am trying to calculate the number of days between a deadline date (column C) and todays date if "Not done" is entered into column E, alternative if text (or blank) is in Column C, then nothing is returned. The problem I have with the 1st IF statement (& also the one you reccommended) is #VALUE! is displayed in the column when I have text entered in column c. RD Gus "Johannes" wrote: Not sure what you're trying to do but definately you have some bracket-problems on the second if-statement. Maybe the one below helps: = IF( ISNA(E35 = "Not Done"), "", IF( E35 = "Not Done", NOW() - C35, "")) What is wrong with the first if-statement (it already returns "" if "Not done" is not displayed) JP "Gus" schreef in bericht ... Hi Please could someone assist. I have a formular: =IF((E34="Not Done"),NOW()-C34,"") which works but I am trying to return a "" value when "Not done" is not displayed. I have tried using =IF(isna((E35="Not Done"),NOW()-C35,""),"",((E35="Not Done"),NOW()-C35,"")) but this is not working. Thks Gus |
Hi
shorter: =NOW()-N(C34) to avoid the #VALUE error -- Regards Frank Kabel Frankfurt, Germany "Johannes" schrieb im Newsbeitrag ... try =IF( AND( ISNUMBER(C34), E34="Not Done"), NOW()-C34,"") problem is that the function NOW() - C34 is #VALUE if C34 is not a number. works better now? JP "Gus" schreef in bericht ... Hi JP Many thks for replying! I am trying to calculate the number of days between a deadline date (column C) and todays date if "Not done" is entered into column E, alternative if text (or blank) is in Column C, then nothing is returned. The problem I have with the 1st IF statement (& also the one you reccommended) is #VALUE! is displayed in the column when I have text entered in column c. RD Gus "Johannes" wrote: Not sure what you're trying to do but definately you have some bracket-problems on the second if-statement. Maybe the one below helps: = IF( ISNA(E35 = "Not Done"), "", IF( E35 = "Not Done", NOW() - C35, "")) What is wrong with the first if-statement (it already returns "" if "Not done" is not displayed) JP "Gus" schreef in bericht ... Hi Please could someone assist. I have a formular: =IF((E34="Not Done"),NOW()-C34,"") which works but I am trying to return a "" value when "Not done" is not displayed. I have tried using =IF(isna((E35="Not Done"),NOW()-C35,""),"",((E35="Not Done"),NOW()-C35,"")) but this is not working. Thks Gus |
Many thanks for this shorter formula, I found that it returned ####### where
the deadline date was in the future. RD gus "Frank Kabel" wrote: Hi shorter: =NOW()-N(C34) to avoid the #VALUE error -- Regards Frank Kabel Frankfurt, Germany "Johannes" schrieb im Newsbeitrag ... try =IF( AND( ISNUMBER(C34), E34="Not Done"), NOW()-C34,"") problem is that the function NOW() - C34 is #VALUE if C34 is not a number. works better now? JP "Gus" schreef in bericht ... Hi JP Many thks for replying! I am trying to calculate the number of days between a deadline date (column C) and todays date if "Not done" is entered into column E, alternative if text (or blank) is in Column C, then nothing is returned. The problem I have with the 1st IF statement (& also the one you reccommended) is #VALUE! is displayed in the column when I have text entered in column c. RD Gus "Johannes" wrote: Not sure what you're trying to do but definately you have some bracket-problems on the second if-statement. Maybe the one below helps: = IF( ISNA(E35 = "Not Done"), "", IF( E35 = "Not Done", NOW() - C35, "")) What is wrong with the first if-statement (it already returns "" if "Not done" is not displayed) JP "Gus" schreef in bericht ... Hi Please could someone assist. I have a formular: =IF((E34="Not Done"),NOW()-C34,"") which works but I am trying to return a "" value when "Not done" is not displayed. I have tried using =IF(isna((E35="Not Done"),NOW()-C35,""),"",((E35="Not Done"),NOW()-C35,"")) but this is not working. Thks Gus |
Excel doesn't like negative dates... if you just use number-format you could
have the deadline in days and a negative nubmer would mean days left.... "Gus" schreef in bericht ... Many thanks for this shorter formula, I found that it returned ####### where the deadline date was in the future. RD gus "Frank Kabel" wrote: Hi shorter: =NOW()-N(C34) to avoid the #VALUE error -- Regards Frank Kabel Frankfurt, Germany "Johannes" schrieb im Newsbeitrag ... try =IF( AND( ISNUMBER(C34), E34="Not Done"), NOW()-C34,"") problem is that the function NOW() - C34 is #VALUE if C34 is not a number. works better now? JP "Gus" schreef in bericht ... Hi JP Many thks for replying! I am trying to calculate the number of days between a deadline date (column C) and todays date if "Not done" is entered into column E, alternative if text (or blank) is in Column C, then nothing is returned. The problem I have with the 1st IF statement (& also the one you reccommended) is #VALUE! is displayed in the column when I have text entered in column c. RD Gus "Johannes" wrote: Not sure what you're trying to do but definately you have some bracket-problems on the second if-statement. Maybe the one below helps: = IF( ISNA(E35 = "Not Done"), "", IF( E35 = "Not Done", NOW() - C35, "")) What is wrong with the first if-statement (it already returns "" if "Not done" is not displayed) JP "Gus" schreef in bericht ... Hi Please could someone assist. I have a formular: =IF((E34="Not Done"),NOW()-C34,"") which works but I am trying to return a "" value when "Not done" is not displayed. I have tried using =IF(isna((E35="Not Done"),NOW()-C35,""),"",((E35="Not Done"),NOW()-C35,"")) but this is not working. Thks Gus |
Thanks, that is very helpful indeed!!
"Johannes" wrote: Excel doesn't like negative dates... if you just use number-format you could have the deadline in days and a negative nubmer would mean days left.... "Gus" schreef in bericht ... Many thanks for this shorter formula, I found that it returned ####### where the deadline date was in the future. RD gus "Frank Kabel" wrote: Hi shorter: =NOW()-N(C34) to avoid the #VALUE error -- Regards Frank Kabel Frankfurt, Germany "Johannes" schrieb im Newsbeitrag ... try =IF( AND( ISNUMBER(C34), E34="Not Done"), NOW()-C34,"") problem is that the function NOW() - C34 is #VALUE if C34 is not a number. works better now? JP "Gus" schreef in bericht ... Hi JP Many thks for replying! I am trying to calculate the number of days between a deadline date (column C) and todays date if "Not done" is entered into column E, alternative if text (or blank) is in Column C, then nothing is returned. The problem I have with the 1st IF statement (& also the one you reccommended) is #VALUE! is displayed in the column when I have text entered in column c. RD Gus "Johannes" wrote: Not sure what you're trying to do but definately you have some bracket-problems on the second if-statement. Maybe the one below helps: = IF( ISNA(E35 = "Not Done"), "", IF( E35 = "Not Done", NOW() - C35, "")) What is wrong with the first if-statement (it already returns "" if "Not done" is not displayed) JP "Gus" schreef in bericht ... Hi Please could someone assist. I have a formular: =IF((E34="Not Done"),NOW()-C34,"") which works but I am trying to return a "" value when "Not done" is not displayed. I have tried using =IF(isna((E35="Not Done"),NOW()-C35,""),"",((E35="Not Done"),NOW()-C35,"")) but this is not working. Thks Gus |
| All times are GMT +1. The time now is 07:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com