Using a formula in "color" property to change color according to conditions

(0) Share

Report

Posted on by PO-06031949-0

Hello all,

Could i please get some help troubleshooting why this formula isn't producing the desired result?

With(
    {
        localDeadline:
            If(
                IsBlankOrError(ThisItem.'Deadline'),
                Blank(),
                DateAdd(
                    DateTimeValue(ThisItem.'Deadline'),
                    TimeZoneOffset(DateTimeValue(ThisItem.'Deadline')),
                    TimeUnit.Minutes
                )
            ),
        now: Now(),
        // Excel table column is text; make comparison robust to case/spaces
        reqStatus: Lower(Trim(Coalesce(ThisItem.'Request Status', "")))
    },
    If(
        IsBlank(localDeadline),
        CustomTheme.PrimaryColor,                           // no deadline → theme color
        If(
            localDeadline > DateAdd(now, 2, TimeUnit.Days), // > 2 days out
            RGBA(59, 151, 110, 1),                          // green
            If(
                localDeadline >= now And reqStatus = "completed",
                RGBA(59, 151, 110, 1),                      // due within 2 days AND Completed → green
                If(
                    localDeadline >= now,
                    RGBA(251, 188, 5, 1),                   // due within 2 days → amber
                    RGBA(234, 67, 53, 1)                    // overdue → red
                )
            )
        )
    )
)
 
 
I put this formula in the "Color" property of a text label. It is supposed to 
change the color of the text to green if Deadline is more than two days from today, 
leave the color as CustomTheme.PrimaryColor if Deadline is blank or has an error, 
change text to green when Deadline is today or overdue but if Request Status is "Completed", 
change text to amber if Deadline is within two days from today, and 
change text to red if Deadline is today or overdue. 
 
The formula is only changing the text to CustomTheme.PrimaryColor & then red. Why is it not doing the other conditions? 

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Verified answer

WarrenBelz 155,838 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @PO-06031949-0,

A couple of things that may improve it are below, however I am not sure they are the answer, rather than what you have tested this on
With( { localDeadline: If( IsBlankOrError(ThisItem.'Deadline'), Blank(), DateAdd( DateTimeValue(ThisItem.'Deadline'), TimeZoneOffset(DateTimeValue(ThisItem.'Deadline')), TimeUnit.Minutes ) ), now: Now(), reqStatus: Lower(Trim(Coalesce(ThisItem.'Request Status', ""))), days2: DateAdd(now, 2, TimeUnit.Days) }, If( IsBlank(localDeadline), CustomTheme.PrimaryColor, localDeadline > days2, RGBA(59, 151, 110, 1), localDeadline >= now And reqStatus = "completed", RGBA(59, 151, 110, 1), localDeadline >= now, RGBA(251, 188, 5, 1), RGBA(234, 67, 53, 1) ) )

You mentioned you were using Excel - Excel Dates and Power Apps are "not the best of friends" - are you storing the dates as Text in Excel and do you see the correct data when you put this on a Label in a Gallery or Form.
DateTimeValue(ThisItem.'Deadline')

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider answering Yes to Was this reply helpful? or give it a Like ♥
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
Suggested answer

11manish 3,333 on at

Like
a
(0)

Report
Copy link

Link copied!
Your formula logic is mostly correct, but the behavior you’re seeing in Microsoft Power Apps usually happens because of two common issues:

Date/time comparison problems (UTC vs local time)

Condition order causing branches to never execute

Fix the TimeZone Calculation
The TimeZoneOffset should usually be subtracted to move from UTC to Local, or you need to wrap it in a negative if your intention is to adjust for the user's offset. However, a simpler and more robust way to handle "Today vs 2 Days" is to use the DateDiff function, which is less sensitive to minute-level math errors.

Condition Ordering
Your logic order:

2 days → Green

= now AND completed → Green

= now → Amber

else → Red

But "Completed" condition is nested inside the 2-day condition, which can cause conflicts.

try below :

With(
{
localDeadline: ThisItem.'Deadline',
now: Now(),
reqStatus: Lower(Trim(Coalesce(ThisItem.'Request Status', "")))
},
If(
IsBlank(localDeadline),
CustomTheme.PrimaryColor,

localDeadline > DateAdd(now, 2, TimeUnit.Days),
RGBA(59,151,110,1),

localDeadline <= now && reqStatus = "completed",
RGBA(59,151,110,1),

localDeadline > now && localDeadline <= DateAdd(now,2,TimeUnit.Days),
RGBA(251,188,5,1),

localDeadline <= now,
RGBA(234,67,53,1)
)
)

Was this reply helpful? Yes No
Suggested answer

DP_Prabh 381 on at

Like
a
(2)

Report
Copy link

Link copied!
Hi @PO-06031949-0,

Your logic looks fine. The issue is most likely coming from the date conversion and timezone adjustment. Since you’re using:

DateAdd( DateTimeValue(ThisItem.'Deadline'), TimeZoneOffset(DateTimeValue(ThisItem.'Deadline')), TimeUnit.Minutes )

the deadline may be getting shifted backward, making it appear already overdue, which causes the formula to fall mostly into the red condition.

Also note that Now() includes the current time, so if the Excel deadline is stored as midnight, items due today may already be treated as overdue.

You could try using the deadline directly (without the timezone adjustment) to see if the other conditions start working.
try this formula:

With(
{
localDeadline: ThisItem.'Deadline',
now: Now(),
reqStatus: Lower(Trim(Coalesce(ThisItem.'Request Status', "")))
},
If(
IsBlank(localDeadline),
CustomTheme.PrimaryColor,
localDeadline > DateAdd(now, 2, TimeUnit.Days),
RGBA(59,151,110,1),
localDeadline >= now && reqStatus = "completed",
RGBA(59,151,110,1),
localDeadline >= now,
RGBA(251,188,5,1),
RGBA(234,67,53,1)
)
)

I hope this works for you.

Was this reply helpful? Yes No
PO-06031949-0 12 on at

Like
a
(1)

Report
Copy link

Link copied!

Thank you so much @WarrenBelz, @11manish and @DP_Prabh.

I ended up using this formula with insights from all your responses.

With(
{
localDeadline: ThisItem.'Deadline',
now: Now(),
reqStatus: Lower(Trim(Coalesce(ThisItem.'Request Status', "")))
},
If(
IsBlankOrError(localDeadline),
CustomTheme.PrimaryColor,
localDeadline > DateAdd(now, 2, TimeUnit.Days),
RGBA(59,151,110,1),
localDeadline <= now && reqStatus = "completed",
RGBA(59,151,110,1),
localDeadline > now && localDeadline <= DateAdd(now,2,TimeUnit.Days),
RGBA(251,188,5,1),
localDeadline <= now,
RGBA(234,67,53,1)
)
)

I had to change the IsBlank function to IsBlankOrError to get my desired results from my Excel table.

When I tried to include the TimeZoneOffset function in the formula, Power Apps would not give the desired result. What was stated by @DP_Prabh could be a good reason. Now that I am using the formula without offsetting the time, I can't be too sure the correct date is being read into the code to determine the color. :(

This is because when I use ThisItem.'Deadline' without the zone offset in the "text" property of the label, Power Apps kept returning the date a day earlier, so March 12 in the Excel table would be returned as March 11.

Was this reply helpful? Yes No