[Dba-office] Help figuring out the Best Approach To Take

[Tina Norris Fields]

Bryan

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.

Regards,
TNF

Tina Norris Fields
tinanfields-at-torchlake-dot-com
231-322-2787

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.
>