Using Date in Face or Shape Chart Type

giannzi · 2025-03-13T01:50:48+00:00

There was an error rendering this rich post.

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!

Find more posts tagged with

Analyzer

Accepted answers

All comments

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!