Display number of days in a set range

**ChrisDubau** · 10-15-2009, 01:49 PM

Hi, I am trying to create a query option that allows a user to set 2 specific dates. (M/D) and then display the # of days in that range. I can successfully do this with only one field of data. How do I have the code calculate the number of days for more than just one specific data field. And how

Example

(A1)Query Start Date :
(A2)Query End Date:
(A3)Total # of Days :

(A5) Start | (B5) End Project info over here...
(A6) 1/21 | (B6) 1/23
(A7) 1/24 | (B7) 1/30
(A8) 2/21 | (B8) 3/2

I would like it to display the number of days from A6 through b8 (that data field)

Any help would be greatly appreciated.

**Ron Coderre** · 10-15-2009, 01:55 PM

It's more helpful if your examples also indicate the end result you want calculated.

Meanwhile, try this:

Please Login or Register  to view this content.

Does that help?...or do you need something else?

**ChrisDubau** · 10-15-2009, 02:05 PM

That would work to give me overall days of actual projects, but I'd like to have the option for a person to input a start date into A1, and and end date into A2, and have A3 display the total number of project days from the list below. I'd like to use this as a search type page, to be able someone to just type in the dates they need data from and have it retrieve it without them having to go and figure it out themselves. I hope this is a little better explanation, thank you for your help. Please let me know if theres anything I can explain better.

Ex Again:

(A1)Query Start Date :
(A2)Query End Date:
(A3)Total # of Days :

(A5) Start | (B5) End Project info over here...
(A6) 1/21 | (B6) 1/23
(A7) 1/24 | (B7) 1/30
(A8) 2/21 | (B8) 3/2

**daddylonglegs** · 10-15-2009, 02:45 PM

I assume that if the date ranges overlap you count them twice....

Try this array formula in A3

=SUM(IF((B6:B8>=A1)+(A6:A8<=A2),IF(B6:B8>A2,A2,B6:B8)-IF(A6:A8<A1,A1,A6:A8)))

confirmed with CTRL+SHIFT+ENTER

**Ron Coderre** · 10-15-2009, 02:50 PM

This ARRAY FORMULA (committed with CTRL+SHIFT+ENTER, instead of just ENTER)
returns the number of project days included in the date range
specified by A1 (start date) and A2 (end date), INCLUSIVE of the first date.

Please Login or Register  to view this content.

Example:
With
A1: 21-Jan-2009
A2: 21-Jan-2009

A6: 21-Jan-2009
B6: 30-Jan-2009
The formula returns: 1...because 21-Jan-2009 is in the date range.

Is that something you can work with?