# Stumped by a formula!

✭✭

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)

• ✭✭✭✭✭✭
edited 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)
)
)
```

• ✭✭

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.

• ✭✭

@Sameer Karkhanis where, after the 6? Its there

• ✭✭✭✭✭✭

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

• ✭✭

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

• ✭✭✭✭✭✭

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!