Using Date in Face or Shape Chart Type

giannzi

Hi, I want to create a chart where, if a report is unfinished and the due date is more than 3 weeks away from the current date, it appears green. If the due date is exactly 3 weeks from the current date, it appears orange. If the due date has already passed, it appears red. However, I can only input numbers within a range, not actual dates. Do you know how I can accomplish this? Thanks!

ggenovese

You can use DATEDIFF to count the number of days and then use the range options in the card configuration something like this:

MIN(CASE WHEN `Report Status` = 'unfinished' THEN DATEDIFF(CURDATE(),`Due Date`) END)

giannzi

Hello @ggenovese, thank you. When you said 'card configuration,' were you referring to this chart property? Because it only accepts numbers.

ggenovese

Hi @giannzi - DATEDIFF() returns the number of days between two dates.

Example: MIN(DATEDIFF(`Due Date`, CURRENT_DATE()))

If today is 03/13/2025 and the due date is 04/04/2025 it will return 21

You can then use your ranges to provide the color rules. For example, the face card only allows 3 ranges and you don't have to provide a MAX for Green :

hope that helps!