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!

Find more posts tagged with

Multi-year projects

Accepted answers

Will Jeffords

@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

All comments

Will Jeffords

Let me know if you can share a little more detail about your scenario to help me help you!

Cheers,

Will

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.