Determining how many days between dates fall within a specified duration and how many

Hi everyone,

I am currently trying to obtain permanent residence in Australia. While this might seem by-the-by, what is interesting is that there are various timing requirements in order to keep certain visas valid. I imagine this is common to most visa systems around the world.

I have tried for hours how I can work out from the attached whether this condition is satisfied:

The candidate for permanent residence must have lived in Australia for 2 years (730 days) in the last 5 years


I suspect the answer will involve a logic formula to see if either arrival or departure date are more recent than 5 years old, and also a sumif(s) to ensure only dates against Australia are analysed.

How, using formula, would it be possible to have a yes/no for the above by interrogating the data? I'm aware the answer is no based on the above, but it will become yes as time goes on. Also, how can I predict the first date that the answer will become yes?

Please let me know if you have any ideas how to solve this one?

New title much better. Thanks

My mistake - sorted

I'm aware the answer is no based on the above, but it will become yes as time goes on.

There is no point providing sample data that will not provide the result you are looking for, as we can't test any proposals.

Please provide sample data that we can use to solve the issue. Thanks.

Also, how can I predict the first date that the answer will become yes?

How would you predict this yourself? What would your criteria be?

This will work out length of stay for each row (D2 copied down):

=DATEDIF(B2,C2,"d")

Change C2 to:

=TODAY()

Then have a play with this:

=SUMIFS($D$2:$D$10,$A$2:$A$10,"Australia",$C$2:$C$10,"<="&TODAY()-730)

Hopefully this will get you started.

If the period of residency straddles today-5 years it gets a bit messier. One approach might be this:

=MAX(IF(B2< EDATE(TODAY(),-60),C2-EDATE(TODAY(),-60),IF(A2< > "Australia",0,IF(ISNUMBER(C2),C2-B2,TODAY()-B2))),0)

to calculate eligible days and then just add them up. see sheet.

I cannot post the formula.

Please see the attachment.

Please try

=SUM((A2:A10="Australia")*(EDATE(TODAY(),-60)<B2:B10)*(IFERROR(--C2:C10,TODAY())-B2:B10))
and Press Ctrl+Shift+Enter

My Mistake.

Then try this with Ctrl+Shift+Enter

=SUM((A2:A10="Australia")*(EDATE(TODAY(),-60)<B2:B10)*(IFERROR(--C2:C10,TODAY())-B2:B10+1))

Better use this with Ctrl+Shift+Enter
if Arrival date is before 5 years, above formula count it as 0 as in row 9.

=SUM(--TEXT((A2:A10="Australia")*(IFERROR(--C2:C10,TODAY())-IF(EDATE(TODAY(),-60)<B2:B10,B2:B10,EDATE(TODAY(),-60))+1),"0;\0;0"))