cancel
Showing results for
Did you mean:
Helper V

Calculate if a number falls within a range, and conditional format based on that calculation

This is probably very simple but I've not been able to work it out and the closes forum thread I could find doesn't fit either.

My app calculates how many days a ticket has been open, and returns that number to the reader. I want this to highlight red/amber/green depending on what the number is eg. 0-7 days = green, 8-14 = amber, 15+=red.

As you can see, at the moment, my formula only works it out on specific numbers, and my attempt to use < just broke it. I assume this is because it's reading it as Text, but when I tried to change the DaysOldValue.Text to .Value it didn't work (as you can see I have used Value in the 2nd & 3rd screenshot below). The Error this gives is 'Name isn't valid'.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

Hi @misc ,

@CNT is correct, Value(DaysOldValue.Text) should work.

------------------------------------------------------------------------------------------

Besides, I see some issues from your formula, we should not use formula like "Value(DaysOldValue.Text) < 8 >14" to judge the number, instead, we should use "Value(DaysOldValue.Text)> 8 && Value(DaysOldValue.Text)< 14".

So based on your requirement, the complete formula should be:

``````If(
Value(DaysOldValue.Text)> 0 && Value(DaysOldValue.Text) < 7,RGBA(196, 237, 165, 1),
Value(DaysOldValue.Text) > 8 && Value(DaysOldValue.Text) < 14,RGBA(249, 202, 128, 1),
Value(DaysOldValue.Text) >= 15,RGBA(249, 142, 128, 1))``````

Best regards,

Allen

3 REPLIES 3
Super User

@misc try replacing DaysOldValue.Value to Value(DaysOldValue.Text) everywhere in your formula.

Community Support

Hi @misc ,

@CNT is correct, Value(DaysOldValue.Text) should work.

------------------------------------------------------------------------------------------

Besides, I see some issues from your formula, we should not use formula like "Value(DaysOldValue.Text) < 8 >14" to judge the number, instead, we should use "Value(DaysOldValue.Text)> 8 && Value(DaysOldValue.Text)< 14".

So based on your requirement, the complete formula should be:

``````If(
Value(DaysOldValue.Text)> 0 && Value(DaysOldValue.Text) < 7,RGBA(196, 237, 165, 1),
Value(DaysOldValue.Text) > 8 && Value(DaysOldValue.Text) < 14,RGBA(249, 202, 128, 1),
Value(DaysOldValue.Text) >= 15,RGBA(249, 142, 128, 1))``````

Best regards,

Allen

Helper V

Thank you! I had to make a tiny change of = alongside the > and < in your solution above as otherwise it wasn't counting 0 or 8 for example but otherwise that was a perfect solution. Thank you both!

Announcements