# Eligibility - need to build a physical presence calculator

#### crofty82

##### New Member
Hello, my wife and I are in the process of emigrating from the UK to Canada and I'm trying to build a spreadsheet that will calculate in real-time when we're eligible for Canadian Citizenship.

To qualify, you need to have spent 1095 days (3 years) out of the previous 5 physically present in Canada. I have column B which is the date and column C which is the country we spent that day in, which I will populate every couple of weeks

So far I've assumed for the sake of argument that we will complete a 'landing trip' to activate our permanent residence on May 6th and then just filled sample data in column C. I'm trying to calculate the earliest date we would be eligible for citizenship and have that date automatically update depending on how many days we spend in the country.

Does anyone have a suggestion for a formula that could work please?

https://www.dropbox.com/s/7k6i6l4h9rm2f8f/Capture.PNG?dl=0

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hi & welcome to MrExcel.

Excel 2013/2016
BCD
1DateLocation
509/05/2019UK
812/05/2019UK
913/05/2019UK
1014/05/2019UK
Cover
Cell Formulas
RangeFormula

That's wonderful, thank you!

You're welcome & thanks for the feedback

If you want to show just dates when residence changes, review the following.

Excel 2010
BCDE
39-May-19UK
512-May-19UK
206-May-24
1ddd
Cell Formulas
RangeFormula
E1=B2+1095-1+SUMPRODUCT(--(C2:C19<>D1),B3:B20-B2:B19)
B20=EDATE(B2,60)

Excel 2010
BCDE
39-May-19UK
512-May-19UK
71-Jul-19UK
206-May-24
1ddd
Cell Formulas
RangeFormula
E1=B2+1095-1+SUMPRODUCT(--(C2:C19<>D1),B3:B20-B2:B19)
B20=EDATE(B2,60)

Last edited:

Replies
8
Views
397
Replies
4
Views
513
Replies
6
Views
621
Replies
13
Views
1K
Replies
2
Views
796

1,203,675
Messages
6,056,683
Members
444,883
Latest member
garyarubin

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back