Split time-stamp into date and time

[#1]

Hi Guys,

I have a column in the format (DD MMM hh:mm for example 23 Aug 10:30). How could I calculate the number of days between today and say this date (i.e 31 Aug - 23 Aug = 8). I have tried format the time-stamp with no success.

Please advise.

Thanks

[sunny_talwar]

You can try this:

LOAD Today() - SetDateYear(DateField, Year(DateField)) as DayDiff

or

LOAD Today() - SetDateYear(TimeStamp(DateField, 'DD MMM hh:mm'), Year(DateField)) as DayDiff

[Anonymous]

Hi Pravir,

Try with this:

Interval(Today()-Floor(SetDateYear(DateField,Year(DateField)),'DD') as DayDiff

It will return your difference on days, if you want to change it to months, change 'DD' with 'MM'.

Regards!

[sunny_talwar]

I don't think interval can return MM, for calculating month difference you will need something more complicated like this:

How Do I Get the Number of Months Between Two Dates?

[#5]

Hi Gents,

I have resolved the issue. The time-stamp didn't have a year so I added that in using another function.

While we are here, I am also struggling to highlight certain rows of data in my table. Say for example I wanted QlikView to automatically highlight specific rows in red where the difference between today's date and the time-stamp data points exceeded a certain value (i.e 31 Aug - 23 Aug = 8 etc). What could a possible solution for this be?

Thanks for all your help guys.

Kind Regards,

Pravir

[Anonymous]

Hi Pravir, you can use two ways, visual cues on table options, or edit the Background colour with a personal expression.

Regards!

[#7]

Hi Manuel,

Thanks for your reply.

Visual Cues doesn't seem to be working. Tried using it earlier, it only highlights the 1 cell of the expression I am conditioning on. Is there a way to highlight the entire row based on a single value in that row. How would I edit the background colour?

Kind Regards,

Pravir

[#8]

Thanks guys I have (by accident) solved the problem to my own question.

I have resolved the issue.

Thanks for the help

Regards,

Pravir

[Anonymous]

Hi pravir,

On expressions and dimensions you can edit the expression of background colour. Don't use ARGB function or colour functions with alpha values, because Excel don't undertand alpha colours, use RGB function. You have to edit all background expressions for the rows.

Regards!

[vardhancse]

INTERVAL(DATE('01/05/2016','DD/MM/YYYY')-DATE([DATE],'DD/MM/YYYY'),'D') as AGEING,