cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
misc
Helper V
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'.

 

disabledfill.png

 

defaultdaysold.png

defaultdaysold value.png

 

Thank you in advance

1 ACCEPTED SOLUTION

Accepted Solutions
v-albai-msft
Community Support
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))

v-albai-msft_0-1621996641024.png

Best regards,

Allen

View solution in original post

3 REPLIES 3
CNT
Super User
Super User

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

v-albai-msft
Community Support
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))

v-albai-msft_0-1621996641024.png

Best regards,

Allen

View solution in original post

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!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,681)