Here’s a quick look at some of the modest Excel tricks we used to make the Schengen Visa Calculation worksheet.
Table
Making the itinerary list an Excel Table lets us make use of some features.
Formatting
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
