cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KatLane
Frequent Visitor

Conditional Formatting Colour of Date - red, orange, black

Hi,

 

I'm customising a form based on a SharePoint List.

 

I have added the DatePicker control for an expiry date of a document (e.g. driver licence, industry certificate etc).

 

I want to show the date as red if the entered date is less than today, black if it's today or greater, and orange if it's within 30 days before today.

 

I can change the colour to red, but it wont change back to black if the date is greater than today.

 

I've used both of these codes with success to change to red, but neither change back to black.

 

If(ThisItem.'Expiry Date'<Today(),Red,Black)

If(Value(ThisItem.'Expiry Date') < Value(Today()), Red, Black)

 

And I can't work out how to make it orange for that 30 day period in the lead up to the expiry date.

 

Please help!

1 ACCEPTED SOLUTION

Accepted Solutions
BCLS776
Super User
Super User

Here's the code I was hoping you would try, but please make sure the reference to your datepicker is correct:

If(
    DateDiff(DatePicker1.SelectedDate,Today(),Days) > 0, Red,
    DateDiff(DatePicker1.SelectedDate,Today(),Days) >= -30, Orange,
    Black
)
_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

View solution in original post

8 REPLIES 8
BCLS776
Super User
Super User

In this case, an If() statement with more than one condition and careful attention to the order of conditions should help this work:

If(
    DateDiff(ThisItem.'Expiry Date',Today(),Days) > 0, Red,
    DateDiff(ThisItem.'Expiry Date',Today(),Days) >= -30, Orange,
    Black
)

Hope that helps,

Bryan

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.
KatLane
Frequent Visitor

Thanks Bryan,

The formula is accepted, but it's like it just takes the last colour.

It will now only make it orange, regardless of what date I pick.

BCLS776
Super User
Super User

OK, I think I know what's going on, but can you run a little experiment to confirm? I believe you are doing this in a gallery, so add a temporary button outside the gallery with this in the OnSelect property: Reset(GalleryName)

 

Let us know if the color starts behaving properly after you select a new date AND use the button to reset the gallery.

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.
KatLane
Frequent Visitor

No gallery, just form. Here's a screen shot

KatLane_0-1632267087208.png

 

OK, I know what's going on here. The statement: 

ThisItem.'Expiry Date'

does not refer to the value in your date picker - it refers to the value stored in the current record. The current record does not change until the form is submitted. You should refer to the date coming from your date picker in the If() statement, something like DatePicker1.SelectedDate

 

Try changing that reference and let us know how it works out.

 

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.
KatLane
Frequent Visitor

Im sorry, I'm super new to this and I can't get the formula right 😩

BCLS776
Super User
Super User

Here's the code I was hoping you would try, but please make sure the reference to your datepicker is correct:

If(
    DateDiff(DatePicker1.SelectedDate,Today(),Days) > 0, Red,
    DateDiff(DatePicker1.SelectedDate,Today(),Days) >= -30, Orange,
    Black
)
_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

View solution in original post

KatLane
Frequent Visitor

I swear I tried that but it was flagging issues with using DateDiff, I must have had just one thing wrong...

 

This is exactly what I wanted, thank you so much for your help.  Much appreciated.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

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