Inside the Schengen Visa Calculation worksheet

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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.

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address