[Dba-office] Help figuring out the Best Approach To Take
Tina Norris Fields
tinanfields at torchlake.com
Tue Mar 29 16:05:22 CDT 2016
I think a moving running countif() formula should do it. So, in one
column you have the dates, day by day. In the next column you enter a C
or a U for which country you were in on that day. Since this is a limit
of 182 days in the past 365, you need to track how many days have gone
by as well as how many of them you were where. For the first 365 days
you will count from the beginning cell. After that, your days range
will always report 365 days, and your C and U columns will count the
days they are checked in the moving 365 day range.
I'm sending you a workbook off-list. I've put in dummy data so you can
see that it works.
This is just one way to do it, but It will work.
Tina Norris Fields
On 03/29/16 2:47 PM, Bryan Carbonnell wrote:
> I hope I can explain what I'm trying to do properly.
> Let me give you some background so you understand what I'm up against.
> As a Canadian I can only be in the US for 182 days in the past 365
> days. This 365 days is a rolling 365 days and not a calendar year. So
> for today it is 182 days from March 30, 2015 to Mar 29, 2016. Tomorrow
> it would be 182 days from Mar 31, 2015 to Mar 30, 2016 etc.
> I was thinking of using Excel, one column for each year, each row for
> a different day of the year and putting either a C or U to denote
> which country I'm in.
> Then having a cell at the top that counts all the U or C in the
> previous 365 days.
> However I'm not sure how to go about this.
> Oh, and I have to keep 4 years of records to stay out of trouble with the IRS.
> I'm hoping the brain power here can help me figure this out.
> Thanks for any suggestions or pointers. Oh, Access is NOT an option.
> I'm on a Mac.
More information about the Dba-office