addition for last 90 days

Green1972

Board Regular
Joined
Dec 8, 2008
Messages
161
Hi All. i have a new question. I have to log my flight time so ive created a excell sheet to do this. as part of the competency i have to calulate totel flight time and flight time for the last 90days. So in column A i have the date (formatted 04/06/18) and in Colum G I have the flight duration (in minutes) for that date. What I want to do in column K is have the cell show the total flight time for the last 90 days from the last date in added in column A. How can I do it?
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
try
Code:
=SUMPRODUCT((A:A<=MAX(A:A))*(A:A>=(MAX(A:A)-90)),G:G)
 
Upvote 0
Thanks for that. I have tried it and it keep returning a value of 00.33 in each cell I put it in. Also I look back at my original post and realised I forgot to add that the cells in colume G and K a formatted with custom formatting hh;mm as each flight would be minutes long but would add upto hours and minutes and the row 1 are the colume header; Date/Time, Flight duration and total flight duration last 90days. Sorry. etc
 
Upvote 0
here are some dummy data, K1 is formatted as numbers (i.e. in days). need to x24 to hours or x1440 to mins


Book1
AGK
108/02/201909:071.19
209/09/201810:04
313/12/201810:48
424/08/201806:28
506/08/201811:45
621/11/201808:38
705/07/201811:02
808/08/201811:45
926/05/201806:28
1030/10/201807:12
Sheet1
Cell Formulas
RangeFormula
K1=SUMPRODUCT(($A:$A<=MAX($A:$A))*($A:$A>=(MAX($A:$A)-90)),G:G)
 
Upvote 0
Sorry it took me a while to reply. Iam a little confused in your example G1 is 9 min 7
seconds
(assuming that is what 09:07 means) but in K1 you have 1.19. Multiply 1.19 by 24 you get 28.56, not 9.07? Not sure if iam missing something or not understanding your explanation properly, but the answer still make on sense. also it needs work on a rolling basis so as new date are added any that are over 90 days old need to be ignored
 
Upvote 0
Sorry it took me a while to reply. Iam a little confused in your example G1 is 9 min 7
seconds
(assuming that is what 09:07 means) but in K1 you have 1.19. Multiply 1.19 by 24 you get 28.56, not 9.07? Not sure if iam missing something or not understanding your explanation properly, but the answer still make on sense. also it needs work on a rolling basis so as new date are added any that are over 90 days old need to be ignored

Col B is formatted as hh:mm,

so, the 1.19 day in K1 is the summation of cells less than 90 days from 2/8/2019 equal to 28hrs 33mins (1.19days)
 
Upvote 0
Ok thanks for clearing that up for me. I got it working except it delivers the same figure in each cell I put it in. It does change as I add new date but it changes it in all the cells prior to that date.
 
Upvote 0
could you post the data/formula you are having problem here so that we can have a look
 
Upvote 0
it delivers the same figure in each cell I put it in.

Hi, do you want the formula to calculate the sum based on the date column in A on the row the formula is entered - 90 days?

If so, and assuming your data starts on row 2 - you could try this in K2 copied down.

=SUMIFS(G:G,A:A,">="&A2-90)

Code:
=SUMPRODUCT(([COLOR=#ff0000]A:A<=MAX(A:A))[/COLOR]*(A:A>=(MAX(A:A)-90)),G:G)

Hi Alan, isn't the red part redundant? i.e. it has to always be true.
 
Upvote 0
Hi Tried the new formula and getting similar problem. How do I put a screen capture on her to show you, or how can I get the data to you.
 
Upvote 0

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top