date format

[Krish2459_58]

Hi,

I need to show the output highlidhted below in red().

If the difference review date-today() >0 I need to show the difference in the format as highlighted in red().

And one addon like if review date-today() < 365 I need to show like 2 months 11 days instead of 0 years 2 months 11 days.

ref	review date	=today()	review date-today()	difference	Required output
000AL2	01/07/2024	21/04/2024	71	<365	2 months 11 days
000AL2	01/07/2026	21/04/2024	801	>365	2 Years 2 months 11 days
000AL2	01/07/2029	21/04/2024	1897	>365	5 Years 2 months 12 days
000AL2	01/07/2032	21/04/2024	2993	>365	8 Years 2 months 13 days
000AL2	01/07/2035	21/04/2024	4088	>365	11 Years 2 months 13 days

[Krish2459_58]

Any Suggestions please 🙂

[Chanty4u]

Try this 

LOAD
ref,
[review date],
Date(today()) as today,
Date([review date]) as review_date,
Date([review date]) - Date(today()) as review_date_diff
FROM [source];

LOAD
ref,
[review date],
today,
review_date,
review_date_diff,
If(review_date_diff > 0,
If(review_date_diff < 365,
Floor(Months(review_date_diff)) & ' months ' & Mod(review_date_diff, 30) & ' days',
Floor(review_date_diff / 365) & ' years ' & Floor(Months(Mod(review_date_diff, 365))) & ' months ' & Mod(review_date_diff, 30) & ' days'),
'') as Required_output
RESIDENT YourData;

[Krish2459_58]

@Chanty4u ,

Thanks for the reply.

It seems the logic adds 30 days. In the below 70 days from today is 2 months 10 days but the logic give 3 months 10 days.

Thanks..

[Krish2459_58]

any additional suggestions please.