Stumped by a formula!
![Tzippy](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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)
Answers
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!