Expression/ Difference Between Dates

[#1]

Hi!

I'm trying to create 3 graphics with the average of the number of days between two dates within the space of:

- Graphic 1/Expression 1: from 01.01.2013 to 31.12.2013;

- Graphic 2/Expression 2: From 01.06.2013 to 30.09.2013;

- Graphic 3/Expression 3: From 16.10.2012 to 10.02.2013;

I make an example of what I'm trying to calculate

Expression 1 - I calcutate only days between this interval from 01.01.2013 to 31.12.2013:

- When my start date is 28.10.2012 and my end date is 31.03.2013, the difference is 89 days (01.03.13 - 31.03.13);

- When my start date is 08.09.2013 and my end date is 16.01.2014 the difference is 114 days (08.09.13 - 31.12.13);

- When my start date is 22.09.2002 and my end date is blank, the difference is 365 days (01.01.13 - 31.12.13);

Expression2 - I calcutate only days between this interval from 01.10.2012 to 28.02.2013:

- When my start date is 28.10.2012 and my end date is 31.03.2013, the difference is 123 days (28.10.12 - 28.02.13);

- When my start date is 16.02.2013 and my end date is 16.03.2014 the difference is 12 days (16.02.13 - 28.02.13);

- When my start date is 22.09.2002 and my end date is blank, the difference is 150 days (01.10.12 - 28.02.13);

You can find an example attached.

Do you think it's possible to do?

Any suggestions greatly appreciated.

Andrea

[swuehl · Accepted solution]

You can start with something like

=rangemax(rangemin(makedate(2013,12,31),[End Date])-rangemax(makedate(2013,1,1),[Start Date]) ,0)

where the makedate() functions are defining your reference date intervals (Jan 1st,  2013 to Dec 31, 2013 in this sample). You may need to add 1 (if you want to include the start date in your calculation).

You can use aggregation functions like avg() around (otherwise this expression will only work correctly with unambiguous start and end dates, i.e. selections been made).

Hope this helps,

Stefan

[#3]

thank you so much for your help!

Andrea