Date/time Difference

[#1]

Hi,

I need to find the difference between 2 date/time fields (e.g 01/02/2012 00:00:00) to show the difference in minutes.

So for example: schedule start time: 01/01/2012 11:00:00

                         Actual start time: 01/01/2012 11:15:00

I want it to show 15mins. I need it to work for negative numbers too, so:

schedule start time: 01/01/2012 11:00:00 

Actual start time: 01/01/2012 10:45:00

so it needs to show -15.

Please help.

Thanks in advance

[vishnus85]

Actually I too was about to post the same question.

QlikView exprets out there, please help.

How to find the diiference (in minutes) between two timestamp/date fields ??

[swuehl]

try

=interval( TimeStamp2 - TimeStamp1, 'mm')

[#4]

Thanks, can I use this in an IF statement now? Like IF (interval( TimeStamp2 - TimeStamp1, 'mm') < -10, 'Early Finish', IF (interval( TimeStamp2 - TimeStamp1, 'mm') >10, 'Late Finish' ... etc?

Or is there another way to do it? I want to find out the time differewnce thenhave another box saying how many minutes the session started late/finished early by.

Thanks,

[JoaquinLazaro]

Try this:

a) = 'Diference: '  & time(Now() - ReloadTime(), 'hh:mm:ss')

or

b) = 'Diference: '  & time(ReloadTime() - Now(), '-hh:mm:ss')

You could improve this with an IF Now > Reload use a) else use b)

[vishnus85]

hi mohit and swuehl,

I still face the problem with formating the days.

Say for example, one of the dates, CASE_START_DATE

This comes to qlikview as a number

But further formating using the date functions as u mentioned is not helping either.

please see evolution of one of the date field and let me know what is it that I am missing.

[kumarnatarajan]

Hi,

I think your time is string format. So you have problam. Try this one it is very helpful for you

Ex:

=Interval(Timestamp(Timestamp#('01/01/2012 11:15:00','DD/MM/YYYY hh:mm:ss'))-Timestamp(Timestamp#('01/01/2012 11:00:00','DD/MM/YYYY hh:mm:ss')),'mm')&' mins'

[swuehl]

It seems to me that you already have a QV timestamp in field CASE_STOP_TIME.

Don't use Date#() interpretation function then.

Use something like

interval(CASE_STOP_TIME - CASE_START_TIME,'mm')

if you want to compare against a certain time in minutes, use something like

if (CASE_STOP_TIME - CASE_START_TIME  < maketime(0,15), 'smaller 15 minutes','>= 15 min')

[vishnus85]

The expression

Interval(Date(Date#(StopDateTime,'DD-MM-YYYY hh:mm'),'mm') - Date(Date#(StartDateTime,'DD-MM-YYYY hh:mm'),'mm') ,'mm')   worked for me.

I was not getting it correct when the (seconds) 'DD-MM-YYYY hh:mm:ss' portion was included.

removing the :ss part, giving only   'DD-MM-YYYY hh:mm' resulted in correct answer for me.

[felixobes]

The expressions work well, thanks for the info, but what about date and time from 2 fields? I am having issues getting the time differences not computing as negatives when they are past midnight, so I would need to use the date field to add 24 hours to the minutes past midnight, not sure how to pull it off.

I am using the interval function on the minutes cause I got time data for instance that says, Scheduled time, 2200 actual time, 2205 so what I did was, =sum(interval(Scheduled - Actual, 'hhmm') and it works, but when the clock goes over midnight the result is a big negative number. How do you suggest I solve this. Example that does not work, Scheduled time 2350, Actual time 0050. This gives me -23hours instead.