Create Pulse with IF Statement
I am trying to complete multiple IF statement to create a pulse on where a partner is on their project, but am not getting the correct color based on my formula:
Here is my timeframe for each phase:
An example of one of my formulas:
=IF(ISBLANK([Phase 2 Date]@row), " ", IF([Phase 2 Date]@row < TODAY(3), "Green", IF([Phase 2 Date]@row <= TODAY(4), "Yellow", IF([Phase 2 Date]@row >= TODAY(5), "Red"))))
Each phase has this formula with it's respective Phase Date, but all should be red based on date:
I'm somewhat new to formulas, so trying to figure out what I am doing wrong.
Best Answer
-
Are you recording the Phase Completed dates anywhere?
Answers
-
@hnc0607 Good afternoon,
The issue is your formula is actually working correctly as this part is being satisfied "IF([Phase 2 Date]@row < TODAY(3)" so its not failing to move onto the next if statement.
humm,
So for the first example Phase 2 Date, which result are you expecting? we need to add an "And" to your if statement in order to have a date range but I am not really sure what that looks like compared to your needs.
04-11-23 <"today(3) =07/29/23", in this case it is true ="Green"
should it only flag green if 4-11-23 is within 3 days of 7-29-23.
-
So Phase 2 would need to be completed within 2-5 days from when Smartsheet records the date that it's been moved into Phase 2. It would be green if we are within 3 days of that date, yellow on days 4-5, and then red starting on day 6, since it would be past due at that point.
-
Are you recording the Phase Completed dates anywhere?
-
I am not, we are keeping track of status as an account moves through the different phases and that formula is working correctly:
=IF(Phase@row = "Phase 6 - Account Transition", [Pulse 6]@row, IF(Phase@row = "Phase 5 - Installs/Invoicing", [Pulse 5]@row, IF(Phase@row = "Phase 4 - Processing Orders", [Pulse 4]@row, IF(Phase@row = "Phase 3 - Rollout Initiated", [Pulse 3]@row, IF(Phase@row = "Phase 2 - CMT Processing", [Pulse 2]@row, IF(Phase@row = "Phase 1 - Information Gathering", [Pulse 1]@row))))))
I just cant seem to figure out the pulse for the individual phases
-
You will need to record the date a phase is completed if you want to compare the completed date to the due date. Otherwise everything will eventually turn to red because as time goes on TODAY() will gradually creep past the due date of every phase.
-
We are just needing to see if they go past due from the initial recorded date of when they are moving through the different phases. I can't seem to get the formula correct on it though.
-
I understand that, but if we don't tell the formula to stop somehow, they are all eventually going to show as past due.
-
We have a master pulse that works off what phase each account is in, so if phase 1 turns red, but our employee has moved on to phase 2 on setting up an account and it's green, the master pulse is following it through the phases:
My big issue is that I can't quite get the formula right on the individual phase pulses. On the second row, phase 2 should have turned red by now, but it's still showing green.
-
So once Phase 2 starts, it doesn't matter what color phase 1 is?
-
Correct! Our master phase pulse will change as the account moves through the 5 phases.
-
Try switching the placement of "Red" and "Green" in the formula in your original post.
-
I replaced the red and green, but it's still not reading the formula correctly:
=IF(ISBLANK([Phase 1 Date]@row), " ", IF([Phase 1 Date]@row < TODAY(6), "Red", IF([Phase 1 Date]@row < TODAY(4), "Yellow", IF([Phase 1 Date]@row < TODAY(2), "Green"))))
The 7/24 dates should be yellow and the last date of 7/26 should be green
-
I didn't mean to switch the entire arguments around. Just the red and green outputs so that red is "less than or equal to TODAY(3)" and green is "greater than or equal to TODAY(5)".
Literally just the words "Red" and "Green" should be switched in your original formula.
-
Okay just switched those words and it's still not updating my pulse
=IF(ISBLANK([Phase 1 Date]@row), " ", IF([Phase 1 Date]@row <= TODAY(3), "Red", IF([Phase 1 Date]@row <= TODAY(4), "Yellow", IF([Phase 1 Date]@row <= TODAY(5), "Green"))))
-
Can you share a screenshot of the formula open in the sheet as if you are getting ready to edit it?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 458 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!