none
What is the formula in a calculated field for Not Available? RRS feed

  • Question

  • hi

    for calculated difference between two times is used this formula in a calculated field named "total time"

    =TEXT(closed-Created,"h:mm")



    this works fine but when the field closed has no data or is zerro then i want to put NA (nog availible) in that field

    how must look the formula for that

    thanks again

    greetings

    fr
    • Edited by Mike Walsh FIN Monday, January 5, 2009 2:51 PM Subject was "is null error" -which didn't give a good indication of the problem
    Monday, January 5, 2009 2:47 PM

Answers

  • Mattias

    thanks for your answer
    you was right , this was the problem

    thanks

    frank
    fr
    • Marked as answer by fve Wednesday, January 7, 2009 11:10 PM
    Wednesday, January 7, 2009 11:10 PM

All replies

  • Try this formula-

    =IF(closed=0,"N/A",TEXT(closed-Created,"h:mm"))

    Also- for future references on different common calculations, refer to the link below:

    http://office.microsoft.com/en-us/sharepointserver/HA101054791033.aspx?pid=CH101760291033
    ~Jennifer Mason My Blog
    • Marked as answer by fve Monday, January 5, 2009 5:48 PM
    • Unmarked as answer by fve Wednesday, January 7, 2009 3:12 PM
    Monday, January 5, 2009 3:19 PM
  •  Jennifer

    thanks for this reply it works fine now with your answer

    greetings
    fr
    Monday, January 5, 2009 5:48 PM
  • hi,

    i used the formula that jennifer gives me but i have another question about this

    =IF(closed=0,"N/A",TEXT(closed-Created,"h:mm"))

    in my field closed (calculated field) stays the closed date and time
    else it is "No"

    when i change the formula to

    =IF(closed=No,"N/A",TEXT(closed-Created,"h:mm"))

    then it gives me an error

    "The formula refers to a column that does not exist.  Check the formula for spelling mistakes or change the non-existing column to an existing column."

    is this syntax wrong?

    thanks


    fr
    Wednesday, January 7, 2009 3:17 PM
  • Hi,

    Don't you need to have the value No within quotation marks since it's a string? Like this: =IF(closed="No","N/A",TEXT(closed-Created,"h:mm"))

    Wednesday, January 7, 2009 9:59 PM
  • Mattias

    thanks for your answer
    you was right , this was the problem

    thanks

    frank
    fr
    • Marked as answer by fve Wednesday, January 7, 2009 11:10 PM
    Wednesday, January 7, 2009 11:10 PM
  • Mark HIS reply as the answer, not your own reply thanking him...
    Wednesday, January 7, 2009 11:16 PM