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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
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