Date formating & Duration calculation

[Moniteur1000]

Hello everyone,

I need help from Qlik experts 🙂

‌‌Context

In order to clean up our archives, I try to display the age (in months) of all the documents in order to delete the oldest ones and free up space.

Each document has a creation date. Therefore all you have to do is calculate a duration (today's date - the creation date) to obtain the age of the document.

However, I have two issues:

Issue 1: Creation date has a particular format: Wed Oct 27 14:26:52 2021

I'm not sure Qlik recognizes this format as a date and therefore if it's possible to use them as an expression.

How to change this format to a more classic format: MM/DD/YYYY   (don't need Hours, Minutes and Seconds) 

Issue 2:  Which expression to use to calculate the duration between two date (today's date - the creation date) ?

Thanks in advance for your help 🙂

[Or]

If Qlik doesn't recognize that as a date, you can always break it down using strings, concatenate those, and rebuild it into a date.

Right(Creation_Date,4) is year

Mid(Creation_Date,5,3) is month name

Trim(Mid(Creation_Date,9,2)) would be the day (Trim is necessary to remove the excess space in case of a single-digit day)

You could then use e.g. date#(DayPart & '/' & MonthPart & '/' & YearPart,'d/mmm/yyyy') - note that this may not be the precise format but I'm sure you can get it to work with a little fiddling around.

[Moniteur1000]

It worked. thx a lot 🙂