Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
John DeLosa
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
SteveG
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
John DeLosa
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
SteveG
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
John DeLosa
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date Formula Needed-Business Days MauiTim Excel Discussion (Misc queries) 2 November 25th 05 09:31 PM
How do I sum a range if the date is is greater than today's date? S2 Excel Worksheet Functions 4 October 8th 05 08:11 PM
Date Range and calculation vgreen Excel Worksheet Functions 2 August 23rd 05 11:08 AM
Formula to count number of days in range which are less than today zooming Excel Worksheet Functions 2 June 21st 05 04:01 PM
How would I change a date cell to decrease it by business days? CNGracin Excel Discussion (Misc queries) 3 December 15th 04 06:20 PM


All times are GMT +1. The time now is 07:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"