Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.
Here’s a handy Excel worksheet for visitors to Europe. 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 travelers, staying inside the Schengen visa limits can get complicated. You might be in Germany, France or Italy (Schengen) then time in the UK (non Schengen) back to Greece (Schengen) then Turkey (non Schengen).
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.
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.
For many citizens (including USA, Canada, Australia, New Zealand) 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.
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.
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!
For Schenghen ‘Short Stay’ calculations, partial days count as a full day. In other words they INclude the arrival/start day as a day.
We’ve updated the Schenghen Visa Calculation worksheet to reflect that 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 2016 for Windows and Excel 2016 for Mac.
It’s also been tested with Excel Mobile for Windows 10, Excel app for Android, Excel app for Apple iPad/ Iphone and Excel Online, the browser based version of Excel.
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.
Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.