icon formula for date

  • 0
  • 1
  • Question
  • Updated 1 month ago
  • Answered
Need a formula to generate an icon in the following instances

Green circle icon displayed when [Due date] has not yet been met (as of today)
Yellow circle icon displayed when [Due Date] is today or is within 1 day of being met (as of today)
Red circle displayed when [due date] has passed (as of today)

This all needs to be based on a 5 day work week if that makes any difference. So if the due date lands on a Monday, i would need the icon to show yellow on that previous Friday.

I can find the icons just fine, just need help developing the formula.

Thanks!
Photo of mkosek

mkosek

  • 776 Points 500 badge 2x thumb

Posted 1 month ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
The formula would be something like this.  But I have not tested it.  I suggest getting it working to calculate the result in words first to be sure it's working.

IF(
Today() > [Due Date], "red",
WeekdaySub([Due Date],Today())<=1, "yellow", "green")

Photo of mkosek

mkosek

  • 776 Points 500 badge 2x thumb
thanks, its working well.

If i wanted to change the first line from "if past today" 
to
"due date is 3 days from today" display red, what would that look like?

yellow would be 5 days from today

green would be anything beyond those 5 days

*all based on 5 day work week*
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Well, it's just that same formula, WeekDaySub.

IF(
WeekdaySub([Due Date],Today())<=3, "red",
WeekdaySub([Due Date],Today())<=5, "yellow", "green")