11 Replies Latest reply: Jul 17, 2012 9:20 AM by Naveed Ul Haq

# Difference between two dates

I would like to know if it is possible with a formula to calculate the difference between two dates.

For example, I have these two dates: 2012-07-05 and 2012-07-11

Now in my chart there should be the number 6 to get to know that for example one order is in progress for 6 days.

Can anyone explain how to do it?

Thanks a lot!

• ###### Re: Difference between two dates

If these are dates and not timestamps, you should be able to use Field1 - Field2.

If they are timestamps, then somthing like floor(Field1)-floor(Field2)

• ###### Re: Difference between two dates

Thank you for your answer but I don't have separate fields. It is just one field with dates from April to today. Is it just possible with two fields?

• ###### Re: Difference between two dates

How will you know which two dates you want to subtract?

• ###### Re: Difference between two dates

Put the date into two fields date1 and date2

and use mwoolf's formula to calcualte the difference

floor(date1) - floor(date2)

• ###### Re: Difference between two dates

Are you trying to do something like my attachment?

• ###### Re: Difference between two dates

Sorry, but I can't open your attachment. Could you write your example here?

• ###### Re: Difference between two dates

Here is a screenshot:

• ###### Re: Difference between two dates

As said above you should split the data in your load script, and format the values accordingly to Date values.

For example if your source data is according to the DateFormat system varaible then the principle could be as below;

T1:

*,

EndDate-StartDate AS IntervalLength

;

DateInterval,

SubField(DateInterval, ' - ', 1) AS StartDate,

SubField(DateInterval, ' - ', 2) AS EndDate

Inline [

DateInterval

2012-07-05 - 2012-07-11

];

If your source needs to be reformatted to match the DateFormat variable then the principle below should apply;

T2:

*,

EndDate-StartDate AS IntervalLength

;

DateInterval,

Date(Date#(StartDate, 'YYYY/MM/DD')) AS StartDate,

Date(Date#(EndDate, 'YYYY/MM/DD')) AS EndDate

;

DateInterval,

SubField(DateInterval, ' - ', 1) AS StartDate,

SubField(DateInterval, ' - ', 2) AS EndDate

Inline [

DateInterval

2012/05/05 - 2012/06/05

];

The DateFormat system varibale is the format included at the beginning of your script;

SET DateFormat='YYYY-MM-DD';

• ###### Re: Difference between two dates

Thank you for your answers but now we found another solution:

=(  subfield('0;31;59;90;120;151;181;212;243;273;304;334;365',';',num(month(date#(max(field1),'YYYYMMDD'))) )

+num(day(date#(max(field1),'YYYYMMDD'))) )

- (subfield('0;31;59;90;120;151;181;212;243;273;304;334;365',';',num(month(date#(min(field1),'YYYYMMDD'))) )

+num(day(date#(min(field1),'YYYYMMDD'))) )

Now we have just little problems when the months overlap.

• ###### Re: Difference between two dates

It is really recommend that you format your Date field properly in the load script. This way you can utilize the QlikView date and time functions and make a more efficient and easy to overview implementation.

Keep in mind that a Date value has a numerical part that corresponds to the number of days since 1899-12-30, which means that a larger Date minus a smaller Date will result in the number of days between the Dates.

I do not really see the purpose of the subfield, the following should work to get the difference in number of days;

=num( date#(floor(max(field1)),'YYYYMMDD') - date#(floor(min(field1)),'YYYYMMDD') )

• ###### Re: Difference between two dates

(dayStart(timestamp(EndDate)) - dayStart(timestamp(StartDate))) + If(Day(StartDate) = 1, 1, 0)

see the attached file