Inside the Schengen Visa Calculation workbook

Here’s a quick look at some of the modest Excel tricks we used to make the Schengen Visa Calculation worksheet.


Making the itinerary list an Excel Table lets us make use of some features.


It’s a lot easier to format as a table with banded rows.

We’ve turned off the Table | Filter Button (defaults on) because the sorting is important in this table and the filtering is unlikely to be needed.

Tables are really useful because formulas use the column heading names automatically.  No need to define names for each cell or column.  A table formula is easily readable with names not cell references:

Of course, other cells outside the table have been named separately.

The slightly complex formula allows for situations where the dates you’re in a certain country overlap with the relevant Schengen date range.  (eg  you’re in Germany from 1 Dec to 1 Feb and you’re checking for Schengen compliance from 1 Jan onwards).

Named Cells Reference

Near the bottom of the Instructions tab, we added a reference table of all the named cells.  This is always a good idea for a complex or shared worksheet.

It’s easy to create in Excel for Windows. Formulas | Defined Names | Use in Formula then Paste List.

That adds a list of names plus formulas to access that name.  We’ve copied to make a third column to show the result as well as the reference formula.

Date Math

Because all Excel dates are saved as serial numbers, it’s easy to do some date comparisons.

To work out if a date is before or after another just compare the two:

To see if one date is before another use the test [@[Date Out]] < DepartureDate  .

Or subtract one from another [@[Date Out]] - DepartureDate  and compare the result (negative = Date Out is earlier, Zero = same date, positive = Departure Date is earlier.

The worksheet uses MIN() and MAX() which return the lowest or highest value.  For dates that means the earlier or latest date.

MIN( [@[Date Out]],DepartureDate) returns the earliest date of either Date Out or DepartureDate

MAX([@[Date In]],StartDate) returns the oldest date of Date In or Start Date.

IF() with AND()

Normally the Excel IF() takes a single test eg IF( Start Date < DepartureDate, "Yes", "No") but we need to do three tests and they all need to be true.

That’s where AND() comes in. It lets us combine the three tests into one function that returns either True or False.

For the worksheet we need to check:

  • Is the country Schengen?
  • The Date Out before the Departure Date
  • The Date In after the Start Date

Only then are the days included in the count.

There’s a corresponding OR() function as well.

Conditional Formatting

There’s a spot of Conditional Formatting to make it obvious if you’re OK or not.

Easy Visa calculator for European visitors – with Excel

Join Office for Mere Mortals today

Office for Mere Mortals is where thousands pick up useful tips and tricks for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  We've never spammed or sold addresses since we started over twenty years ago.
Invalid email address