Skip to Content
0
Former Member
Jul 18, 2008 at 06:47 PM

How do I create this range report?

25 Views

Greetings,

I have a report requirement which is like this - I have three columns Start Date, End Date, Amount.

An example of data set can be:

Start Date              End Date              Amount
------------------------------------------------------
07/18/2008             07/21/2008           $100.00
07/21/2008             07/22/2008           $200.00
07/21/2008             07/24/2008           $300.00

Now I want to run a report for date range 07/18/2008 to 07/22/2008 such that for each date, if it falls between Start Date and End Date in the table, it will add the Amount and show the result. So the output should be:

Date                 Total
-------------------------------
07/18/2008           $100         //Only first row
07/21/2008           $600         //All three rows as 07/21 is in all three date ranges. Also skipping weekends
07/22/2008           $500         //Row 2+Row 3

How do I do this without having to create a formula for each date and then calculating the total?

Thank you very much!

N K.

Edited by: Nikhil Kulkarni on Jul 18, 2008 10:10 PM