Skip to main content

How to turn a Blank or NaN field into a Number

  • January 20, 2018
  • 10 replies
  • 296 views
S

I need to run some calculations off of the results of a DATETIME_DIFF field. The problem is some of the fields are populated with time spans and some have NaN. I am unable to calculate anything with an NaN.

My thought was to first ask if the first date was an empty field, and if so, to simply state 0. If there is a date, it would render the number of days since that date has passed.

If (Delivered = BLANK(), 0, DATETIME_DIFF(TODAY(), Delivered, ‘days’))

So if I needed to calculate how much money I’ve spent on renting an item, I could take the number of days since it was delivered and multiply it by the price. Items which were not rented would not need to add to the cost.

As far as I could tell, this formula should work. Can you please show me where I am going wrong?

    10 replies

    A
    • Ali_Aboutera
    • Participating Frequently
    • January 20, 2018

    I did a field called “Date” and I wrote this in the “Formula” field

    IF(Date="",“0”,DATETIME_DIFF(TODAY(),Date))

    This should work.

      R
      Forum|alt.badge.img+2
      • Robin_Travis
      • Inspiring
      • January 22, 2018

      I have a column that calculates the number of days I held a trade. If I bought and sold on the same day, I was getting NaN. Usually NaN indicates a divide by 0. On the date calculation if I bought and sold on the same day I did an If statement and forced the contents to “” rather than 0 i.e. leaves the cell empty. On our CRM table, when we enter a new enquiry, we manually set the enquiry value to 1 to avoid the divide by 0 and this allows the totals to show again. Hope this helps.

      S
      • Sharon_Clark
      • Author
      • New Participant
      • January 22, 2018

      Thanks! I got it to work.

        O
        • OSU_EHS
        • New Participant
        • January 30, 2019

        I’m having the exact same issue but cannot get my IF statement to work:
        IF({Date sent to HR}="",“0”,DATETIME_DIFF({Date sent to HR},{EIR Rcv’d},‘days’))

        If I delete everything before DATETIME the calculation works fine, DATETIME_DIFF({Date sent to HR},{EIR Rcv’d},‘days’), but I’m left with NaN wherever the Date Sent to HR field is blank.

        Also, is there a way to get a summary block to show an average that doesn’t include blanks?

        E
        Forum|alt.badge.img+17

        I’m having the exact same issue but cannot get my IF statement to work:
        IF({Date sent to HR}="",“0”,DATETIME_DIFF({Date sent to HR},{EIR Rcv’d},‘days’))

        If I delete everything before DATETIME the calculation works fine, DATETIME_DIFF({Date sent to HR},{EIR Rcv’d},‘days’), but I’m left with NaN wherever the Date Sent to HR field is blank.

        Also, is there a way to get a summary block to show an average that doesn’t include blanks?


        That is not a number, it’s a string because it has quotes.

        Those are not included by default:

        O
        • OSU_EHS
        • New Participant
        • January 30, 2019

        Thanks, Elias! Here’s how I wound up resolving it:
        IF({Date sent to HR}="",BLANK(),DATETIME_DIFF({Date sent to HR},{EIR Rcv’d},‘days’))

          O
          • OSU_EHS
          • New Participant
          • January 30, 2019

          Also, I’m apparently getting the NaN result in summary view because the field I am averaging contains the NaN results, not blanks.

          O
          • OSU_EHS
          • New Participant
          • January 30, 2019

          Also, I’m apparently getting the NaN result in summary view because the field I am averaging contains the NaN results, not blanks.


          This resolved once I got the above formula corrected by using BLANK() instead of “” or 0. Thanks again, Elias!

            F
            Forum|alt.badge.img
            • FLib
            • New Participant
            • June 4, 2020

            Suggestion: add like excel formulas for IFERROR, IFNA, …something like IFNAN({NaN Field},“if true do it”)

              C
              Forum|alt.badge.img+5
              • Cressinad
              • New Participant
              • September 17, 2020

              This resolved once I got the above formula corrected by using BLANK() instead of “” or 0. Thanks again, Elias!


              IF({Date sent to HR}="",0,

              I just remove the “” for the 0 to show a number and not a quote, it works fine :thumbs_up:

              Terms & ConditionsAccessibility statement