Forum Discussion

Iron Contributor

Jun 23, 2022

if the date column is NA or 0 how to show 0 in the auto formula?

hi I have a date column and there are some row that will have not date in it. I want to auto pick the date into MONTH in numbers with the formuala =MONTH(date col) but how to show empty or 0...

Formulas and Functions

Riny_van_Eekelen

Platinum Contributor

Jun 23, 2022

bbsin Not sure why you have a value error where you have a date and 4 when the date in B is in May

Suppose the first date in your example is in B2, then the formula in A2 should be:

=IF(ISBLANK(B2),"",MONTH(B2))

and copy it down. This will return the month number or a blank if B2 is empty.

bbsin
Iron Contributor
Jul 07, 2022
@Riny, thanks it works,

One question, what if the date col I put as NA? If I change to
=IF(ISNA(B2),"",MONTH(B2)) the return show 1, is there any way to show empty if input is NA?

Thank you
- Riny_van_Eekelen
  Platinum Contributor
  Jul 07, 2022
  bbsin ISNA check for the error value #N/A that usually is the result of a lookup function where no match is found. If you are just entering text like NA, you should change the formula to:
  
  =IF(B2="NA","",MONTH(B2))
  - bbsin
    Iron Contributor
    Jul 11, 2022
    Riny_van_Eekelen
    
    Thank you the formula works, however, may I know why is there an icon appear?
    
    Do I need to do anything about it? Thank you