# Create Pulse with IF Statement

Options
✭✭✭

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

• ✭✭✭✭✭✭
Answer ✓
Options

Are you recording the Phase Completed dates anywhere?

«1

## Answers

• ✭✭✭✭✭
Options

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

• ✭✭✭
Options

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.

• ✭✭✭✭✭✭
Answer ✓
Options

Are you recording the Phase Completed dates anywhere?

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!