WEEKNUM system 1 and 2

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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)))

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login