Using the ,21 (ISO) option when determining WEEKNUMBER - Sheet displays ok, Report doesn't

08/05/21
Accepted

Hi,

When using this formula to return a week number

=IF([Date completed]@row = "", "", WEEKNUMBER([Date completed]@row, 21))

the correct week number value displays accurately in the sheet which is good.

However, the Report fails to display the column value and displays

#INCORRECT ARGUMENT SET


Removing the ,21 parameter and using this formula

=IF([Date completed]@row = "", "", WEEKNUMBER([Date completed]@row))

ensures that the formula value displays ok on the report but leads to incorrect Week Numbers in both the sheet and report.


Has anyone found a workaround for this to enable the correct ISO Week number to display on the report?

Thanks,

Richard

Best Answers

  • Genevieve P.Genevieve P. admin
    Accepted Answer

    Hi @RichardJ

    Is this the same issue that was resolved on your other post, here?

    If it's a different issue, could you post screen captures of your sheet and report (but please block out any sensitive data!) It would also be helpful to know more about your [Date completed] column, how is this column populated and is it restricted to Dates Only?

    Thanks!

    Genevieve

  • RichardJRichardJ
    Accepted Answer

    Hi @Genevieve P. Thanks for the response and apologies for the delay.

    I'd found a workaround (applying the Week Number in Power BI rather than trying to use the smartsheet report).

    If one of the developers would like to resolve what I suspect to be a bug the prior detail should be enough to build a use case.

    i.e. Consolidate two columns within a sheet to a report

    Column A =IF([Date completed]@row = "", "", WEEKNUMBER([Date completed]@row, 21))

    Column B =IF([Date completed]@row = "", "", WEEKNUMBER([Date completed]@row))

    Column A and B will display ok in the Sheet

    Column A will not display on the report

    Column B will display on the report

    This suggests the ",21" parameter is generating an issue at the report level.

    Thanks,

    Richard

Answers

  • Genevieve P.Genevieve P. admin
    Accepted Answer

    Hi @RichardJ

    Is this the same issue that was resolved on your other post, here?

    If it's a different issue, could you post screen captures of your sheet and report (but please block out any sensitive data!) It would also be helpful to know more about your [Date completed] column, how is this column populated and is it restricted to Dates Only?

    Thanks!

    Genevieve

  • RichardJRichardJ
    Accepted Answer

    Hi @Genevieve P. Thanks for the response and apologies for the delay.

    I'd found a workaround (applying the Week Number in Power BI rather than trying to use the smartsheet report).

    If one of the developers would like to resolve what I suspect to be a bug the prior detail should be enough to build a use case.

    i.e. Consolidate two columns within a sheet to a report

    Column A =IF([Date completed]@row = "", "", WEEKNUMBER([Date completed]@row, 21))

    Column B =IF([Date completed]@row = "", "", WEEKNUMBER([Date completed]@row))

    Column A and B will display ok in the Sheet

    Column A will not display on the report

    Column B will display on the report

    This suggests the ",21" parameter is generating an issue at the report level.

    Thanks,

    Richard

  • Hi @RichardJ

    I'm glad you found a solution.

    Thank you for this, I'm able to replicate the issue with the ",21" parameter and I'll let our team know about the report view.

    Cheers,

    Genevieve

Sign In or Register to comment.