Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Skydiver Driver
 
Posts: n/a
Default How do I sum within a set time frame from the current date?

I am trying to figure out a way to add one column based on the date entered.
I need to write a formula that will calculate the past 6,12, and 24 months
from the current date.

Travis
  #2   Report Post  
Don
 
Posts: n/a
Default

SD
=SUMPRODUCT(--(A1:A18<=E1),--(A1:A18(EDATE(E1,-6))),B1:B18)

Change the -6 to -12 and -24 for the other months


=SUMPRODUCT(--(A1:A18<=TODAY()),--(A1:A18(EDATE(TODAY(),-6))),B1:B18)

Same formula, except the current date is today.


"Skydiver Driver" <Skydiver wrote in
message ...
I am trying to figure out a way to add one column based on the date
entered.
I need to write a formula that will calculate the past 6,12, and 24 months
from the current date.

Travis



  #3   Report Post  
Gary''s Student
 
Posts: n/a
Default

You need to :
1. calculate the times in the past
2. look then up in your table
3. sum between the end points

For the first part, is =TODAY() is in A1
then =DATE(YEAR(A1),MONTH(A1)-6,DAY(A1)) will be 6 months prior
then =DATE(YEAR(A1),MONTH(A1)-12,DAY(A1)) will be 12 months prior
then =DATE(YEAR(A1),MONTH(A1)-24,DAY(A1)) will be 24 months prior

you would then use MATCH() to find the entries or SUMIF() or SUMPRODUCT()
to get the correct sums
--
Gary''s Student


"Skydiver Driver" wrote:

I am trying to figure out a way to add one column based on the date entered.
I need to write a formula that will calculate the past 6,12, and 24 months
from the current date.

Travis

  #4   Report Post  
Skydiver Driver
 
Posts: n/a
Default How do I sum within a set time frame from the current date?

Thank you Don, you have been a tremendous help; I have been pulling my hair
out over this for some time.

Travis

"Don" wrote:

SD
=SUMPRODUCT(--(A1:A18<=E1),--(A1:A18(EDATE(E1,-6))),B1:B18)

Change the -6 to -12 and -24 for the other months


=SUMPRODUCT(--(A1:A18<=TODAY()),--(A1:A18(EDATE(TODAY(),-6))),B1:B18)

Same formula, except the current date is today.


"Skydiver Driver" <Skydiver wrote in
message ...
I am trying to figure out a way to add one column based on the date
entered.
I need to write a formula that will calculate the past 6,12, and 24 months
from the current date.

Travis




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
Excel Time Manipulation BFiedler Excel Discussion (Misc queries) 0 September 15th 05 01:15 AM
conditional formatting with time values Access Idiot Excel Discussion (Misc queries) 2 September 13th 05 03:29 PM
How do I calculate if a date is in a certain time frame? Pe66les Excel Worksheet Functions 19 August 27th 05 11:07 PM
3 Time frame 11050204 Excel Worksheet Functions 1 June 27th 05 03:05 AM
unmet challenge boris Excel Worksheet Functions 2 March 16th 05 03:13 PM


All times are GMT +1. The time now is 11:39 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"