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
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!