Forum Discussion
Conditional Formatting for Dates
Hello All:
I have a table with permits, and need to find a way to apply conditional format to two columns with dates: Original Submittal Date and Approval Date, based on today's date, with two conditions.
IF Original Submittal Date is before TODAY and the Original Submittal Status is F, then the cell should color Red (past due),
ELSE IF Original Submittal Date is TODAY or Within 7 days of TODAY and the Original Submittal Status is F, then the cell should color Yellow (coming due), else no color.
Same rules for Approval Date, the cells should have the same colors if they are past due, or coming due.
Please help, I did read other posts on this topic but wasn't able to use the solutions offered.
amitchandak I could not create a measure as you typed using the IF function, the measure could not reference a column in a table, however, I did use the function you typed to create a calculated column, instead of "Red" I used "1" and instead of "Yellow" I used "2". Prior to adding the table column I created a measure someone else suggested in a post, Anonymous , the measure is Today = TODAY(). Then the formula I used was this:
Past Due/Within 7 Days Submittal = IF('Table'[Original Submittal Date] < [Today] && 'Table'[Original Submittal Date Status]="F",1,IF('Table[Original Submittal Date] >= [Today] && 'Table[Original Submittal Date] <= [Today]+7 &&'Table[Original Submittal Date Status]="F",2,0))The last step is to select the conditional formatting for the Original Submittal Date, selected Format by Rules, based on field Past Due/Within 7 Days Submittal, and if the number was 1 I chose a red format, if the value was 2 I chose a yellow format. And that did it!Thank you both, amitchandak and Anonymous
2 Replies
Oana , Create a color measure like this and use that in conditional formatting using field value option
color =
IF( [Original Submittal Date] < TODAY() && [Original Submittal Status] = "F","Red", IF([Original Submittal Date]>= TODAY() && [Original Submittal Date] <= Today()+7 && [Original Submittal Status] = "F", "Yellow" ), "White")
How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4
amitchandak I could not create a measure as you typed using the IF function, the measure could not reference a column in a table, however, I did use the function you typed to create a calculated column, instead of "Red" I used "1" and instead of "Yellow" I used "2". Prior to adding the table column I created a measure someone else suggested in a post, Anonymous , the measure is Today = TODAY(). Then the formula I used was this:
Past Due/Within 7 Days Submittal = IF('Table'[Original Submittal Date] < [Today] && 'Table'[Original Submittal Date Status]="F",1,IF('Table[Original Submittal Date] >= [Today] && 'Table[Original Submittal Date] <= [Today]+7 &&'Table[Original Submittal Date Status]="F",2,0))The last step is to select the conditional formatting for the Original Submittal Date, selected Format by Rules, based on field Past Due/Within 7 Days Submittal, and if the number was 1 I chose a red format, if the value was 2 I chose a yellow format. And that did it!Thank you both, amitchandak and Anonymous
