Stumped by a formula!

Tzippy · 08/31/22

I cant figure out why this formula isn't working and am hoping someone can help me.

I have a discipline column, a current date column and a in service required column.

based on the time of year and discipline selected, a certain number of in-service hours is required.

I.E. from January 1st to April 1st = 6 (hours) , from April 1st to October 1st = 3 (hours) only if PCA is selected.

The first part of the formula works (if the date is before April 1st the number 6 appears in the in-service column. however, if I select a date past April 1st it stays blank.

(disregard the 6 column names to the right)

Sameer Karkhanis · 08/31/22

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

Tzippy · 08/31/22

@Sameer Karkhanis

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!

Sameer Karkhanis · 08/31/22

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

Tzippy · 08/31/22

@Sameer Karkhanis

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

Sameer Karkhanis · 08/31/22

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

Tzippy · 09/01/22

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

@Sameer Karkhanis where, after the 6? Its there

Sameer Karkhanis · 09/01/22

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

Tzippy · 09/01/22

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

Sameer Karkhanis · 09/01/22

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

Tzippy · 09/08/22

@Sameer Karkhanis

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

Sameer Karkhanis · 09/08/22

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

Sameer Karkhanis · 09/08/22

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.

Tzippy · 09/09/22

@Sameer Karkhanis

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

Sameer Karkhanis · 09/09/22

I am talking about the bracket as shown below not the one at the end

Stumped by a formula!

Answers

Help Article Resources

Categories