Forum Discussion
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 if the date column has no date or when it input as NA in Date Col.
Example
Thank you
7 Replies
To throw another option into the mix, a valid date will be a positive number. Hence
= IF(ISNUMBER(date), MONTH(date), "")works in most instances. and
= IF(ISNUMBER(date)*(date>0), MONTH(date),"")would also pick up zeros.
Note: I have used the name 'date' for an array of one or more possible dates.
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.
@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 youbbsin 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))
