LinkBack URL
About LinkBacks
I have the below formula in RESULTS G10 of the attached WB. How do I limit the formula to only count the days within the past 1 rolling calendar year?
Basically, count & sum days in the past 365 days.
Please Login or Register to view this content.
I have the below formula in RESULTS G10 of the attached WB. How do I limit the formula to only count the days within the past 1 rolling calendar year?
Basically, count & sum days in the past 365 days.
Please Login or Register to view this content.
Last edited by cableghost; 05-12-2022 at 10:14 AM.
What should happen in the case of a Leap Year?
Try this:
=SUMPRODUCT(((Table1[DATE]<>"")*(Table1[RISK]<>"")*(Table1[DATE]>=TODAY()-365))/COUNTIFS(Table1[DATE],Table1[DATE]&""))
or this:
=SUMPRODUCT(((Table1[DATE]<>"")*(Table1[RISK]<>"")*(Table1[DATE]>=EDATE(TODAY(),-12)))/COUNTIFS(Table1[DATE],Table1[DATE]&""))
Last edited by AliGW; 05-12-2022 at 01:29 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thanks Ali for the input.
Leap Year... I wouldn't be really concerned with it... for these calculations, 1 day will be nominal, as the results for me are simply a ballpark anyhow.
If I understand the EDATE formula correctly, the -12 is simply calculating EDATE over the past 12 months. I notice both give slightly different results, with the basic -365 a fractional number.
The first calculates today minus 365 days.
EDATE with -12 finds the equivalent date last year (so if today is 12/05/2022, it will return 12/05/2021).
Use whichever you prefer - that's why I gave yo two options.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks