Date formula query
What formula to use, to calculate records from today till 6 months down the line? And this rule should be valid at all times.
In other words, how to calculate sum of values eg: count of total number of projects starting from today (26/April/24) till 12 months down the line i.e. 26/October/24?
For sum of values starting from today till 6 months from now, I am using this formula,
=SUMIFS({Range1}, {Range2}, Criteria2, {Range Year}, YEAR(TODAY()), {Range Month}, <(MONTH(TODAY()) + 6), {Project_Status}, NOT(OR(@cell = "a", @cell = "b", @cell = "c", @cell = "d", @cell = "e", @cell = "f")), {Range4}, "0")
Range1, Range 2 and so on are cell references taken from another sheet. I can't share the sheet. Sorry.
this formula is fine for now. But when the year changes from 2024 to 2025, this formula doesn't work. Similarly, I want to calculate values for 12 months period.
How to resolve? Please help!
Best Answer
-
@neetuchopra05, what is the date field you are using to measure this? Start Date? End Date? Other? Is there some reason you cannot just add 180 days (365 days for the 12-month) to TODAY() to help with this calculation?
Let me know if you can share a little more detail about your scenario to help me help you!
Cheers,
Will
Answers
-
@neetuchopra05, what is the date field you are using to measure this? Start Date? End Date? Other? Is there some reason you cannot just add 180 days (365 days for the 12-month) to TODAY() to help with this calculation?
Let me know if you can share a little more detail about your scenario to help me help you!
Cheers,
Will
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 437 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!