Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Contributor II

Find hours between dates

Hello,

I'm having trouble trying to find the amount of hours,positive or negative, between my date field and today's date.  I've looked on other discussions and can't seem to find the solution.

Here's the formula in my script.  It is returning null values.

Interval(timestamp(now(),'M/D/YYYY hh:mm')-timestamp(Date1,'M/D/YYYY hh:mm')) as Interval,

Is there another function that I should be using?

Thank you in advance.

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Find hours between dates

Have you checked that Date1 shows a numeric representation (can be formatted as number)?

Then this should work

Interval( now()-timestamp#(Date1,'M/D/YYYY hh:mm') ) as Interval,


or if your input value shows seconds as well:

Interval( now()-timestamp#(Date1,'M/D/YYYY hh:mm:ss') ) as Interval,

View solution in original post

5 Replies
Highlighted
MVP
MVP

Re: Find hours between dates

You don't need to format your timestamps using Timestamp(), but that should not be the issue here.

Ensure that Date1 is read in correctly, interpreted as timestamp by QlikView:

Why don’t my dates work?

Get the Dates Right

QlikView Date fields

Highlighted
Valued Contributor

Re: Find hours between dates

Hi David,

Once I used somenthing like this and worked:

=(Timestamp(Now(), 'DD/MM/YYYY hh:mm:ss') - Timestamp([Creation Date], 'DD/MM/YYYY hh:mm:ss')) * 24

Regards,
H

Highlighted
Contributor II

Re: Find hours between dates

Hello Stefan,

Thank you for your reply.

I have the date being formatted in the script to M/D/YYYY hh:mm and it's being read as such when I pull the field into a listbox


Timestamp#(Date1,'M/D/YYYY hh:mm:ss') as Date1

Highlighted
MVP
MVP

Re: Find hours between dates

Have you checked that Date1 shows a numeric representation (can be formatted as number)?

Then this should work

Interval( now()-timestamp#(Date1,'M/D/YYYY hh:mm') ) as Interval,


or if your input value shows seconds as well:

Interval( now()-timestamp#(Date1,'M/D/YYYY hh:mm:ss') ) as Interval,

View solution in original post

Highlighted
Contributor II

Re: Find hours between dates

This works, thank you so much!