WEEKNUM system 1 and 2

  • Good afternoon everybody,


    I've been using the formula below for returning the week number for a given date range...


    =IF(WEEKNUM(A679,1)=WEEKNUM(B679,1),WEEKNUM(A679,1),WEEKNUM(A679,1)&-WEEKNUM(B679,1))


    When the date range cells are empty, the target cells return 0, which is great. I recently found out that this formula uses WEEKNUM system 1.


    Now that I'm entering information for the year 2021, I'm getting different week number results. After some research, it turns out that I need to use WEEKNUM system 2...


    =IF(WEEKNUM(A687,21)=WEEKNUM(B687,21),WEEKNUM(A687,21),WEEKNUM(A687,21)&-WEEKNUM(B687,21))


    The above formula also works perfectly well, but when the date range cells are empty the target cells return 52 and I'd prefer they were 0.


    I've tried all afternoon to get the target cells to return 0, but no luck. I've looked at functions like ISBLANK and =IF(Specific cell="","",Original formula), etc. but I just don't know where to place them. So, any assistance on this issue would be really appreciated.


    Regards,


    A

  • Quote

    =IF(Specific cell="","",Original formula), etc.

    Try:


    =If(a687="",0,IF(WEEKNUM(A687,21)=WEEKNUM(B687,21),WEEKNUM(A687,21),WEEKNUM(A687,21)&-WEEKNUM(B687,21)))

    or

    =If(or(a687="",b687=""),0,IF(WEEKNUM(A687,21)=WEEKNUM(B687,21),WEEKNUM(A687,21),WEEKNUM(A687,21)&-WEEKNUM(B687,21)))