Forum Discussion

sdg33139's avatar
sdg33139
Copper Contributor
Jul 16, 2024
Solved

Rolling 365 Calculation Glitch

Hi   I am trying to track days within certain countries for tax purposes using a rolling 365 calculation.  I've followed tips from other posts but cannot get the formulas to work. Here are the ...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
MVP
Jul 16, 2024

sdg33139 

You shall use TODAY(), not TODAY

 

=SUMIF(date,">="&TODAY()-365,Col)

 

With TODAY condition returns error, thus not met, thus result is zero.

  • sdg33139's avatar
    sdg33139
    Copper Contributor
    Jul 18, 2024

    SergeiBaklan 

    Thank you for your help.  Unfortunately, the SUMIF formula is not returning the correct values.  For example, using the suggested formula =SUMIF(Date,">="&TODAY()-365,CoL) that I placed in the row for January 1, 2017 (to have 365 days of data above this row), the SUMIF formula said that I was in Colombia 88 days.  However, using a simple SUM formula returned the correct day count of 95 days.  Similarly, for my days in USA, the =SUMIF(Date,">="&TODAY()-365,USA) resulted in 315 days vs. 271 using a simple SUM.

     

    Any thoughts?

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Jul 19, 2024

      sdg33139 

      I misunderstood. Sum within 365 days back from the date in current row, in D2

      =SUMIFS($B$2:$B$1000, $A$2:$A$1000, "<=" & $A2, $A$2:$A$10000, ">=" & ($A2-365) )

      and drag it down.

      Same for the next column.

      • sdg33139's avatar
        sdg33139
        Copper Contributor
        Jul 22, 2024
        Thank you Sergei for your help. I greatly appreciate it.

Resources