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 specifics:
Column A is DATE going back to January 2016 Column B is COL for Colombia and has a value of 1.0 if I was in the country and 0 if I was outside the countryColumn C is USA and has a value of 1.0 if I was in the country and 0 if I was outside the countryColumn D is titled Total COL/365 and has a rolling 365 calculation formula that failsColumn E is Total USA/365 and has a rolling 365 calculation formula that fails
I am placing the first rolling 365 calculation formulas in Columns D & E on January 1, 2017 so as to allow 365 days to have elapsed. I know from using COUNT, that I was in Colombia 95 days in 2016 and 271 days in the USA.
The formulas I am using are:
=SUMIF(Date,”>=”&TODAY-365,CoL)=SUMIF(Date,”>=”&TODAY-365,USA)
In an ideal world, I want to copy the formula down and calculate the rolling 365 total for both countries through 2023 as I have the data.
Any thoughts will be greatly appreciated!
Thank you
Seth
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 specifics: Column A is DATE going back to January 2016 Column B is COL for Colombia and has a value of 1.0 if I was in the country and 0 if I was outside the countryColumn C is USA and has a value of 1.0 if I was in the country and 0 if I was outside the countryColumn D is titled Total COL/365 and has a rolling 365 calculation formula that failsColumn E is Total USA/365 and has a rolling 365 calculation formula that failsI am placing the first rolling 365 calculation formulas in Columns D & E on January 1, 2017 so as to allow 365 days to have elapsed. I know from using COUNT, that I was in Colombia 95 days in 2016 and 271 days in the USA. The formulas I am using are:=SUMIF(Date,”>=”&TODAY-365,CoL)=SUMIF(Date,”>=”&TODAY-365,USA)In an ideal world, I want to copy the formula down and calculate the rolling 365 total for both countries through 2023 as I have the data. Any thoughts will be greatly appreciated!Thank youSeth Read More