Formula for filling Heart symbol based on Created date?

Justin Bray

I need help with a formula to populate my "health" column with a hearts based on the row "Created" date and I hope someone can help. I just did the three day class, but this wasn't covered and I didn't think of it until this morning.

If the "Created Date" is >74 days from today() it should be 0, "Empty".

If the "Created Date" is 70-73 days from today() it should be 1, "Two".

If the "Created Date" is 60-69 days from today() it should be 2, "Two".

If the "Created Date" is 50-59 days from today() it should be 3, "Three".

If the "Created Date" is 40-49 days from today() it should be 4, "Four".

If the "Created Date" is 0-39 days from today() it should be 2, "Five".

Thank you in advance.

Cheers, Justin

Nick Korna

Hi @Justin Bray,

You can do this with a nested IF formula:

=IF(TODAY() - [Created Date]@row <= 39, "Five", IF(TODAY() - [Created Date]@row <= 49, "Four", IF(TODAY() - [Created Date]@row <= 59, "Three", IF(TODAY() - [Created Date]@row <= 69, "Two", IF(TODAY() - [Created Date]@row <= 73, "One", "Empty")))))

Sample data & Output:

I would guess your Created Date would normally be system generated, but this serves to illustrate how the formula works.

Hope this helps, but if I've misunderstood anything or you've any problems/questions then let us know!

Nick Korna

Hi @Justin Bray,

You can do this with a nested IF formula:

=IF(TODAY() - [Created Date]@row <= 39, "Five", IF(TODAY() - [Created Date]@row <= 49, "Four", IF(TODAY() - [Created Date]@row <= 59, "Three", IF(TODAY() - [Created Date]@row <= 69, "Two", IF(TODAY() - [Created Date]@row <= 73, "One", "Empty")))))

Sample data & Output:

I would guess your Created Date would normally be system generated, but this serves to illustrate how the formula works.

Hope this helps, but if I've misunderstood anything or you've any problems/questions then let us know!