cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Conditional Formatting date field to display date in red if greater than today on a label in a gallery

Could someone help me with the code to get a label in a gallery to show the date value as red if the labels date value (SharePoint date and time column) is greater than today

 

The following code doesnt work?

 

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

 

Any help would be appreciated

4 REPLIES 4
PowerAddict
Super User
Super User

If you want to set the color to red if the due date is greater than today, then the color property of the date label should be:

If(Value(ThisItem.'DUEDATE') >Today(), Red, Black)

However, I assume you wanted to say if the due date is lesser than today. If this isnt working, you can try 2 things:

1. Use the Now() function instead. The Now function returns the current date and time as a date/time value.

The Today function returns the current date as a date/time value. The time portion is midnight. Today has the same value throughout a day, from midnight today to midnight tomorrow.

Since your due date is date/time, this might help for a better comparison.

2. If you are only concerned about the date portion, then you can convert your due date to a date only value and then compare it with today. So something like this:

Date(Year(ThisItem.DueDate), Month(ThisItem.DueDate), Day(ThisItem.DueDate))

Also, if your ThisItem.DueDate is text, you shouldnt be using Value function. You probably need to use DateTimeValue function to convert that text or string into a date time value.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit(Haman)

v-xida-msft
Community Support
Community Support

Hi @Anonymous ,

Could you please share a bit more about your scenario?

Do you want to compare the 'DUEDATE' field value with Today?

 

Based on the formula you provided, I think there is somthing wrong with it. I have made a test on my side, please consider take a try with the following workaround:

Set the Color property of the Label in your Gallery to following:

If(ThisItem.'DUEDATE' > Today(), Red, Black)

Note: I assume that the 'DUEDATE' field a Date type column in your data source.

Or

If(Value(ThisItem.'DUEDATE') > Value(Today()), Red, Black)

or

If(
   Value(Text(ThisItem.'DUEDATE', "yyyymmdd")) > Value(Text(Today(), "yyyymmdd")), 
   Red, 
   Black
)

 

Please take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable


@v-xida-msft wrote:

Hi @Anonymous ,

Could you please share a bit more about your scenario?

Do you want to compare the 'DUEDATE' field value with Today?

 

Based on the formula you provided, I think there is somthing wrong with it. I have made a test on my side, please consider take a try with the following workaround:

Set the Color property of the Label in your Gallery to following:

 

If(ThisItem.'DUEDATE' > Today(), Red, Black)

 

Note: I assume that the 'DUEDATE' field a Date type column in your data source.

Or

 

If(Value(ThisItem.'DUEDATE') > Value(Today()), Red, Black)

 

or

 

If(
   Value(Text(ThisItem.'DUEDATE', "yyyymmdd")) > Value(Text(Today(), "yyyymmdd")), 
   Red, 
   Black
)

 

 

Please take a try with above solution, check if the issue is solved.

 

Best regards,


This is the code that works for me

 

If(Value(ThisItem.'DUEDATE') > Value(Today()), Red, Black)

 

Would you be able to tell me the code that would make it red if greater than today and orange if the date is between 30 days and today? and black if greater than 30 days? 

 

Im trying several pieces of code however i cant get it to work? does it matter that my date format is shortdatetime? as if i use "dd-mm-yyyy" the Au timezone code appears and i cant use that?  

@Anonymous Did you get a solution to the orange date between 30 days and today?  I'm looking to do the same thing.  Also, I can't seem to make my date colour go back to black if the date is Today or greater.  I've tried both code options above and both work to change to red, but can't get to black if it's greater.

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,826)