0

I have a column [Meeting Date] which is used to determine [Initial Date]. The user when creating a new list item, inserts a date for [Meeting Date] which is automatically set to Today's date. [Initial Date] is set to [Meeting Date] + 2 but notice the following formula I used the WEEKDAY formula with the (2) return type to see if the value is a Thursday or Friday to calculate for the weekend/only business days:

[Initial Date] Formula
=IF(
    WEEKDAY(
        [Meeting Date],
        2
    ) = 5,
    [Meeting Date] + 4,
    IF(
        WEEKDAY(
            [Meeting Date],
            2
            ) = 4, 
            [Meeting Date] + 4,
            [Meeting Date] + 2
    )
)

The column that I am having issues with is the [Final Date] combined with the use of another column [Submitted To] which is a yes/no column. If [Submitted To] is not checked, then the [Final Date] is equal to [Initial Date] column (which already skips over the weekend dates and only accounts for business days). If [Submitted To] is checked, then [Final Date] will equal [Initial Date] + 5. The issue is that if the [Submitted To] is checked, it does not account for weekends.

[Final Date] Formula (Not accounting for weekends that works =IF([Submit To PO],IF(ISBLANK([Initial Submission Due Date]),"",[Initial Submission Due Date]+5),[Initial Submission Due Date])

I receive The formula contains a syntax error or is not supported. when I try the below formula

Please see formula below:

[Final Date] Formula
=IF(
    [Submitted To],
    IF(
        ISBLANK(
            [Initial Date]
        ),
        "",
        ),
        IF(
            WEEKDAY(
            [Initial Date],
            2
            ) = 1,
            [Initial Date] + 7
            ),
        IF(
            WEEKDAY(
            [Initial Date],
            2
            ) = 2,
            [Initial Date] + 7
            ),
        IF(
            WEEKDAY(
            [Initial Date],
            2
            ) = 3,
            [Initial Date] + 7
            ),
        IF(
            WEEKDAY(
            [Initial Date],
            2
            ) = 4,
            [Initial Date] + 7
            ),
        IF(
            WEEKDAY(
            [Initial Date],
            2
            ) = 5,
            [Initial Date] + 7
            ),
    [Initial Date]
)

1 Answer 1

1

Syntax for IF function in calculated column formula is:

IF(logical_test,value_if_true,value_if_false)

So, you have use nested IF functions true and false values in above format.

Try using formula like:

=IF(
    [Submitted To],
    IF(
        ISBLANK([Initial Date]),
        "",
        IF(
            WEEKDAY([Initial Date],2) = 1,
            [Initial Date] + 7,
            IF(
                WEEKDAY([Initial Date],2) = 2,
                [Initial Date] + 7,
                IF(
                    WEEKDAY([Initial Date],2) = 3,
                    [Initial Date] + 7,
                    IF(
                        WEEKDAY([Initial Date],2) = 4,
                        [Initial Date] + 7,
                        IF(
                            WEEKDAY([Initial Date],2) = 5,
                            [Initial Date] + 7,
                            [Initial Date] + 5
                        )
                    )
                )
            )
        )
    ),
    [Initial Date]
)

You can change [Initial Date] + 5 parameter in above formula as per your requirements.

Note:

  1. Sometimes comma(,) does not work in formula (it is based on language or regional settings on your site). So in that case use semicolon(;) instead of comma(,). For example: If you are using German language for your SharePoint site

Official Documentations:

  1. Calculated Field Formulas
  2. IF function
6
  • Ganesh, thank you for your response. When I try this formula, I get the same invalid syntax error!
    – BeerusDev
    Commented Jan 12, 2023 at 13:41
  • Are you using correct column names in your formula? Column names in formula are case sensitive. You have to use the correct display name of columns. Commented Jan 12, 2023 at 13:56
  • The column names are correct, I even tried typing in the formula instead of pasting it, same error
    – BeerusDev
    Commented Jan 12, 2023 at 14:12
  • I even tried putting in NP++, and finding all/replacing all column names with the correct display name copied and pasted from SharePoint column settings. Still says invalid syntax or is not supported
    – BeerusDev
    Commented Jan 12, 2023 at 14:32
  • 1
    It works perfect!
    – BeerusDev
    Commented Jan 12, 2023 at 15:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.