Looking for a formula : IF[Date Field 1] In BETWEEN [Date Field 2] AND [Date Field 3], Display this icon.

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

Looking for a formula : IF[Date Field 1] In BETWEEN [Date Field 2] AND [Date Field 3], Display this icon.

Photo of JW

JW

  • 140 Points 100 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of Xavier Fan

Xavier Fan, Champion

  • 340 Points 250 badge 2x thumb
Try this formula - in a Formula - Text field (with "Allow HTML" checked):

If ( [Date Field 1] > [Date Field 2] and [Date Field 1] < [Date Field 3], "<img src='[URL to icon image]' />" )

Edit - updated to fix quote error in <img> - Thanks Dan!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,664 Points 20k badge 2x thumb
Minor difference in X's formula (1) reordering [Date Field 1] and (2) correcting quote error:

If ( [Date Field 2] < [Date Field 1] and [Date Field 1] < [Date Field 3], "<img src='[URL to icon image]' />" )
Photo of JW

JW

  • 140 Points 100 badge 2x thumb
Let me elaborate my question i need to display red, green icons based on expiration. Like i have a request that has an expiry of 5 days, So If the date crosses the 5 day barrier a red icon should be displayed else Green. So i m not sure how do i accomplish this in QB.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,664 Points 20k badge 2x thumb
See this formula:

http://pastie.org/10669410
Photo of JW

JW

  • 140 Points 100 badge 2x thumb
Thanks Dan for the solution, But this does meet what i was looking for. Let me put it again : I am working on a ticketing system, The ticket needs to be resolved in 5 days. So when a ticket is submitted and saved i can use date created. I also created a new field SLA which is date created + 5 days. So now i have two fields, So is there a way in QB where i can display a red icon if date created is 5 days after date created, And if its within 5 days of date created then icon is green.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,664 Points 20k badge 2x thumb
1> ... But this does meet what i was looking for.
2> ... display a red icon if date created is 5 days after date created

I think you have some typos there.

You are changing the problem in moving from having three date fields to now introducing a literal five day duration and the [SLA] field. But all you have to do is change the If() statement criteria. Try this:

 http://pastie.org/10669442