Create Pulse with IF Statement

hnc0607

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.

Paul Newcome

Are you recording the Phase Completed dates anywhere?

Cory Page

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

hnc0607

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.

Paul Newcome

Are you recording the Phase Completed dates anywhere?

hnc0607

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

Paul Newcome

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.

hnc0607

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.

Paul Newcome

I understand that, but if we don't tell the formula to stop somehow, they are all eventually going to show as past due.

hnc0607

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.

Paul Newcome

So once Phase 2 starts, it doesn't matter what color phase 1 is?

hnc0607

Correct! Our master phase pulse will change as the account moves through the 5 phases.

Paul Newcome

Try switching the placement of "Red" and "Green" in the formula in your original post.

hnc0607

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

Paul Newcome

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.

hnc0607

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

Paul Newcome

Can you share a screenshot of the formula open in the sheet as if you are getting ready to edit it?