Date formula query

Options

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!

Answers

  • Will Jeffords
    Will Jeffords ✭✭✭✭✭✭
    Options

    @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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!