Stumped by a formula!

I think there is a type or copy paste error in your formula (= sign before the nested IF). Below is the corrected formula,

=IF(AND([Current date]@row >= DATE(YEAR(TODAY()),1,1), [Current date]@row < DATE(YEAR(TODAY()),4,1)),
    IF(Discipline@row = "pca", 6
        IF(AND([Current date]@row >= DATE(YEAR(TODAY()),4,1), [Current date]@row < DATE(YEAR(TODAY()),10,1)),
            IF(Discipline@row = "pca", 3)
        )
    )
)

You can improve the formula by first checking if the Discipline is PCA and then the dates, like below,

=IF(Discipline@row="pca",
    IF(AND([Current date]@row >= DATE(YEAR(TODAY()),1,1), [Current date]@row < DATE(YEAR(TODAY()),4,1)), 6,
        IF(AND([Current date]@row >= DATE(YEAR(TODAY()),4,1), [Current date]@row < DATE(YEAR(TODAY()),10,1)),3)
    )
)

Cool! I tried your improved formula and it worked-thank you!

I want to add another formula to the same cell. Basically in addition to the above I want to add that if its a HHA (instead of a PCA) then until May 1st it should be 12 (hours), from May1st until November 1st should be 6

How can I add this to the existing formula?

Thanks!

=IF(Discipline@row="PCA",
    IF(AND([Current date]@row >= DATE(YEAR(TODAY()),1,1), [Current date]@row < DATE(YEAR(TODAY()),4,1)), 6,
        IF(AND([Current date]@row >= DATE(YEAR(TODAY()),4,1), [Current date]@row < DATE(YEAR(TODAY()),10,1)),3)
    ),
    IF(Discipline@row="HHA",
        IF(AND([Current date]@row >= DATE(YEAR(TODAY()),5,1), [Current date]@row < DATE(YEAR(TODAY()),11,1)), 6)
    )
)

Having trouble with that. This is what I did and I got the unparseable error message.

=IF(Discipline@row = "PCA", IF(AND([Current date]@row >= DATE(YEAR(TODAY()), 1, 1), [Current date]@row < DATE(YEAR(TODAY()), 4, 1)), 6, IF(AND([Current date]@row >= DATE(YEAR(TODAY()), 4, 1), [Current date]@row < DATE(YEAR(TODAY()), 10, 1)), 3))), IF(Discipline@row = "HHA", IF(AND([Current date]@row >= DATE(YEAR(TODAY()), 5, 1), [Current date]@row < DATE(YEAR(TODAY()), 11, 1)), 6))

That's coz you missed copying the bracket at the end.

https://community.smartsheet.com/discussion/comment/341962#Comment_341962

@Sameer Karkhanis where, after the 6? Its there

Yes, after the 6. There should be total 3 closing brackets.

https://community.smartsheet.com/discussion/comment/342067#Comment_342067

@Sameer Karkhanis yes that's what I have and its still giving me the error message for some reason...

May be a screenshot of your sheet with the formula may help identify what could be wrong.

I just renamed one column but everything is still the same and still getting the unparseable error message.

Can you please copy paste the formula (text, not the screenshot), so that I can debug?)

I think I know where the issue is. You have 3))) in your formula, whereas mine has 3)), so please remove the extra bracket. Hopefully that should fix it.

The parenthesis automatically populate to as many as needed when I click enter.