Comparing two dates using If / Then construct

Use Case

You have a table with two columns: Target date and Completion date. You need to compare and rate these dates:

• If the completion date is within 5 days, the status is ON TIME.
• If the completion date is more than 5 days late, but less than 10 days, the status is LATE.
• If the completion date is more than 10 days, the status is VERY LATE.

Solution

1. Switch the page to the edit mode.
2. Insert the Table Transformer macro and paste the tables or the macros outputting tables within the macro body.
3. Select the macro and click Edit.
4. In the Presets tab select Custom transformation and click Next.

5. Enter the following SQL query:

   SELECT *,
      CASE
         WHEN
            (('Completion date' - 'Target date') / "24h") < 5 
         THEN "ON TIME" 
         WHEN
            (('Completion date' - 'Target date') / "24h") > 5 
            AND 
            (('Completion date' - 'Target date') / "24h") <= 10 
         THEN "LATE" 
         ELSE "VERY LATE" 
      END
      AS 'Rating' 
   FROM T1

   SQL
6. Click Next.
7. Define the date format .
8. Save the macro and the page.

You can use FORMATWIKI function to insert statuses into a table.