Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I specify these date-range formulas?
SaleDate ShipDate $Due $Paid
7/2/08 7/18/08 52.00 7/12/08 7/25/08 35.00 35.00 7/30/08 8/4/08 76.00 8/3/08 8/25/08 47.00 Based on the above, what formulas do I use to get the following?: 1. Paid year-to-date thru ShipDate 7/31/08 (result 35.00) 2. Past due year-to-date thru ShipDate 7/31/08 (result 52.00) 3. Future due for orders placed prior to 7/31/08 (result 76.00) Thanks! k |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I specify these date-range formulas?
Set it up like this:
A B C D E F G 1 SaleDate ShipDate $Due $Paid 2 7/2/08 7/18/08 52.00 3 7/12/08 7/25/08 35.00 35.00 4 7/30/08 8/4/08 76.00 5 8/3/08 8/25/08 47.00 6 7 Thru Shipdate: 7/31/08 8 Paid: 9 Past Due: 10 Future Due: In cell E2: =IF($C$7B2,D2,0) In cell F2: =IF($C$7B2,(C2-D2),0) In cell G2: =IF(AND($C$7A2,$C$7<B2),(C2-D2),0) Drag these formulas down as far as you need. In Cell D8: =SUM(E2:E5) (adjust for length of spreadsheet entries) In cell D9: =SUM(F2:F5) In cell d10: =SUM(G2:G5) This is assuming the ship date is in cell C7. "KMC" wrote: SaleDate ShipDate $Due $Paid 7/2/08 7/18/08 52.00 7/12/08 7/25/08 35.00 35.00 7/30/08 8/4/08 76.00 8/3/08 8/25/08 47.00 Based on the above, what formulas do I use to get the following?: 1. Paid year-to-date thru ShipDate 7/31/08 (result 35.00) 2. Past due year-to-date thru ShipDate 7/31/08 (result 52.00) 3. Future due for orders placed prior to 7/31/08 (result 76.00) Thanks! k |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using weekday formulas to automatically update a date range | Excel Discussion (Misc queries) | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
Using formulas to determine date in one cell based on date in anot | Excel Worksheet Functions |