Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I add a range by date over 90 days older than today
I am setting up a work book that needs a cell to show an over 120 days old
total with out having a column above it. I have tried sumif but can't find a criteria that will let me refferance a TODAY date or a cell with today's date in it to subtract from. my columns a Invoice #, Invoice Date, and Invoice Amount I would Like a cell on the bottom that shows a total amount over 120 days old |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I add a range by date over 90 days older than today
Try this. =SUMPRODUCT(--(DATEDIF(B2:B5,TODAY(),"d")+1=120)) B2:B5 are your dates. DATEDIF counts the days between the date in your dates and TODAY. Add the 1 to include the start date. Does that help? Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=513244 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I add a range by date over 90 days older than today
I'm comfused??? how to apply this
here is an example of what I'm trying to do TODAY 2/16/2006 INV 1 10/01/05 $100 INV 2 10/18/05 $100 INV 3 11/01/05 $100 INV 4 12/30/05 $100 INV 5 01/30/06 $100 total $500 total over 30 days $400 total over 60 days $300 total over 90 days $200 total over 120 days $100 I don't know how to apply the formular to the cells Thanks so much "SteveG" wrote: Try this. =SUMPRODUCT(--(DATEDIF(B2:B5,TODAY(),"d")+1=120)) B2:B5 are your dates. DATEDIF counts the days between the date in your dates and TODAY. Add the 1 to include the start date. Does that help? Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=513244 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I add a range by date over 90 days older than today
Sorry, thought you wanted to count the number of instances that the invoice was over 120 days. Try this. You need to alter this formula for each # of days you have. 30,60,90,120. Using the dates you have then. 30 days =SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),"d")+130),($C$2:$C$6)) 60 days =SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),"d")+160),($C$2:$C$6)) 90 days =SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),"d")+190),($C$2:$C$6)) 120 days =SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),"d")+1120),($C$2:$C$6) ) Your results should actually be 30 - $400 60 - $300 90 - $300 120 - $200 120 days from today would be 10/20/2005. If you want to include the Text phrase in your example then use this and modify as needed. ="Total over 30 days"&" $"&SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),"d")+130),($C$2:$C$6)) Does that help? Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=513244 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I add a range by date over 90 days older than today
Thank you so much it worked
"SteveG" wrote: Sorry, thought you wanted to count the number of instances that the invoice was over 120 days. Try this. You need to alter this formula for each # of days you have. 30,60,90,120. Using the dates you have then. 30 days =SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),"d")+130),($C$2:$C$6)) 60 days =SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),"d")+160),($C$2:$C$6)) 90 days =SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),"d")+190),($C$2:$C$6)) 120 days =SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),"d")+1120),($C$2:$C$6) ) Your results should actually be 30 - $400 60 - $300 90 - $300 120 - $200 120 days from today would be 10/20/2005. If you want to include the Text phrase in your example then use this and modify as needed. ="Total over 30 days"&" $"&SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),"d")+130),($C$2:$C$6)) Does that help? Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=513244 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Formula Needed-Business Days | Excel Discussion (Misc queries) | |||
How do I sum a range if the date is is greater than today's date? | Excel Worksheet Functions | |||
Date Range and calculation | Excel Worksheet Functions | |||
Formula to count number of days in range which are less than today | Excel Worksheet Functions | |||
How would I change a date cell to decrease it by business days? | Excel Discussion (Misc queries) |