Forum Discussion

bbsin's avatar
bbsin
Iron Contributor
Jun 23, 2022

Need help on formula if there is. year and date month

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 formula =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.

 

and is there any formula to auto pick the date into year?  

if date range falls

1 Apr 2022 to 31 Mar 2023= Year 2022/23

1Apr 2021 to 31 Mar 2022 = Year 2021/22

 

 

 

Thank you

Formulas and Functions

1 Reply

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jun 23, 2022

    bbsin 

    =IFERROR(MONTH(C2),"")
    =IFERROR(IF(MONTH(C2)<=3,YEAR(C2)-1&"/"&RIGHT(YEAR(C2),2),YEAR(C2)&"/"&RIGHT(YEAR(C2)+1,2)),"")

     Maybe with these formulas in cells A2 and B2 in the example.