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

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. Employee Admin
    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

  • RichardJ
    RichardJ ✭✭
    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. Employee Admin
    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

  • RichardJ
    RichardJ ✭✭
    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

  • Genevieve P.
    Genevieve P. Employee Admin

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!