Count days

[#1]

Hello.

I need some help.

In my excel I have 2 dates, one is DateInitial and the other si DateFinal, DateFinal >= DateInitial

Example (dd/mm/yyyy): 28/01/2010, 03/03/2010

I need count the days between this dates and get the days in the diferent months between this two dates.

for example in january we have 3 days (31 - 28), february 28 days, march 03 days.

how can i do that?

[Miguel_Angel_Baeyens]

Hi,

You can use (untested)

=Interval(Date('03/03/2010') - Date('28/01/2010'), 'DD')

Hope that helps

[#3]

thanks but I need to save the days in the differents months

[johnw]

So... are you loading the Excel file into QlikView? Are you loading DateInitial and DateFinal as fields? Setting variables to them?

I might generate all the dates in the range, add a month field to that table, and then count dates by month. But I'm very unclear what your question really is.

[#5]

yes i'm loading the excel:

Example:

ID InitialDate(dd/mm/yy) FinalDate(dd/mm/yy)

1 01/01/10 05/05/10

Month Days

1 31

2 28

3 31

4 30

5 31

I need save the days for month... in this case, we have 30 days in january (31 - 01), 28 days in febraury, 31 days in march, 30 days in april and 05 days in may...

I need save all the months and the quantity of days in this months

[johnw · Accepted solution]

Here's one approach:

Days:
LOAD *
,rangemin(FinalDate,daystart(monthend(Month)))
-rangemax(InitialDate,Month) + 1 as Days
;
LOAD *
,date(monthstart(InitialDate,iterno()-1),'M YYYY') as Month
RESIDENT Ranges
WHILE monthstart(InitialDate,iterno()-1) <= FinalDate
;
DROP FIELDS InitialDate, FinalDate from Days;

[#7]

thanks a lot, this help's me soo much