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

[David McAfee]

I'd put them all in one column.

I can send you a quick little Excel file I made, which may be easier to
understand by viewing it.

In Column A, starting with Cell A2, I entered 2/28/15 and auto filled dates
down to A749 (which is 3/16/2017).
In Column B, I randomly entered a "U" every so often, this would be your Us
& Cs
We'll Skip Column C for a bit.

For simplicity, I made the following:
Cell E2 = Now() (formatted as ShortDate)
Cell D2 = E2-365
In Cell C2 I entered the following Formula:
=IF(AND(A2>=$D$2,A2<=$E$2),TRUE, FALSE)
I dragged/filled that formula down to C749

Cell F1 = "US Days"
Cell F2 = =COUNTIFS(C2:C749,"=TRUE",B2:B749,"=U")

You can do the same in G1 & 2 for Canada.

It should be live, I can add or remove a U from any cell in Column B that
has a True in Column C and the number updates.

Hope this helps.
David McAfee

On Tue, Mar 29, 2016 at 11:47 AM, Bryan Carbonnell <carbonnb at gmail.com>
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.
>
> --
> Bryan Carbonnell - carbonnb at gmail.com
> Life's journey is not to arrive at the grave safely in a
> well-preserved body, but rather to skid in sideways, totally worn out,
> shouting "What a great ride!"
> _______________________________________________
> Dba-office mailing list
> Dba-office at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-office
>