Here’s a handy Excel worksheet for visitors to Europe, including UK citizens. It’ll help you stay within the legal limits for ‘short stays’ by visitors to the EU Schengen zone.
Most visitors to Europe are allowed to stay in the Schengen group of countries for 90 days out of the previous 180 days. It’s a rolling calculation with no ‘reset’ like some other countries. That’s simple if you’re just doing a standard trip ‘there and back’ from home.
But for ‘digital nomads’ and other extended-time travellers, staying inside the Schengen visa limits can get complicated. That also applies to UK citizens from 1 January 2021 who may be hopping across the Channel regularly.
- Download SchengenVisitorsCalculation.xlsx
- How to use
- Your Itinerary
- Date Out
- Allowed days
- The result
- Part days count as full days
- Comments / Feedback
Keeping track of your legal limits can be difficult because it’s a rolling calculation for the previous 180 days … a perfect job for Excel!
The worksheet lets you enter your itinerary plus an exit date from the Schengen zone. It’ll work out whether you’re inside the legal limit or not.
You might be in Germany, France or Italy (Schengen) then time in the UK (non Schengen) to Greece (Schengen) then Turkey (non Schengen). This worksheet shows that after time in Turkey you could return to the Schengen zone for seven days.
It’s an Excel ‘macro free’ xlsx file with no external data connections. Naturally, we’ve checked it for viruses etc. (but you should NEVER take anyone’s word for that <sigh>.)
How to use
Open the worksheet in the Excel of your choice. There’s an instructions tab and a list of European countries with their Schengen status for easy reference.
All the main stuff is on the Schengen calculation tab.
The important cells to change all have a yellow background.
Enter your movements over time.
Location – just a name for the country or area you’ll be in
Schengen or not – a simple pull-down selection for Yes/No.
Date In / Out – enter the date into and out of that location. You can link the Date In to the previous Date Out to ensure you don’t miss any dates.
Add more rows to the Excel table, if necessary. The itinerary can be as long as you like. The worksheet will figure out your status based on the starting ‘Date Out’ value.
Finally, enter the date from which you want to calculate back from. Usually the last day you leave the Schenghen zone.
‘Date In’ is calculated automatically from the Date Out minus ‘Out of the last’ days.
Obviously, the Date Out to Date In range has to be covered by what you’ve entered in the itinerary rows. Anything outside the date range is ignored. If your itinerary does NOT cover the Date In/Out search range, the results will be wrong, usually negative values.
For many citizens (including USA, Canada, Australia, New Zealand plus UK from 1 January 2021) the ‘short stay’ limit is 90 days out of the last 180 days. That’s the default setting but can be changed to suit you.
Allowed days: how many days you’re allowed to be in the Schengen zone.
Out of the last: the total days the ‘allowed’ days are counted over.
Our globe-trotting boss, Peter Deegan, urges not pushing visa limits to the very last day. If a flight is delayed or you calculated wrong, it’s too easy to overstay accidentally. It’s safer to give yourself a day or two leeway by calculating to 89 or 88 days allowed.
Look below the table to see if you’re ‘safe’ or not.
The columns ‘Schengen Days’ , ‘Non-Schengen Days’ and ‘Schengen days in current search range’ will help you figure out where the problem is or where you can stay longer!
Part days count as full days
For Schenghen ‘Short Stay’ calculations, partial days count as a full day. In other words they INclude the arrival/start day as a full day.
The Schenghen Visa Calculation worksheet includes part-days as full days, see days between two dates method.
Most immigration law/regulation across the world seem to work this way. It’s safer to assume that partial days count as full days to not run the risk of accidentally overstaying.
In short, any modern Excel should open the worksheet. We’ve deliberately used fairly simple Excel features for maximum compatibility and easy understanding (if you’d like to tinker with the worksheet).
The worksheet was made over time in Excel 365, 2021, 2019, 2016 for Windows and Excel 365, 2021, 2019 and 2016, for Mac.
It’s also been tested with Excel app for Android, Excel app for Apple iPad/ Iphone and Excel Online, the browser based version of Excel.
The worksheet is deliberately simple to make it easy to understand and not so complicated that it can be easily/accidently broken.
The Yes/No column could be automated to show Schengen status from the separate country list — however that creates problems about name matching (e.g people typing “Holland” or “Deutschland”) and possible changes to the Schengen list. Safer to let people type their own country labels and choose Schengen status.
Comments / Feedback
As always, we welcome any feedback or comments.
If there’s enough interest, we may expand the worksheet. Let us know what else you’d like included.