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