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.

visa calculation for european schengen visitors an excel worksheet microsoft excel 22601 - Inside 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.

inside the schengen visa calculation worksheet microsoft excel 22617 - Inside the Schengen Visa Calculation worksheet

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:

inside the schengen visa calculation worksheet microsoft excel 22618 - Inside the Schengen Visa Calculation worksheet

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

img 5b55954a8604d - Inside the Schengen Visa Calculation worksheet

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.

img 5b55955bb04ac - Inside the Schengen Visa Calculation worksheet

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

img 5b55956dc958d - Inside the Schengen Visa Calculation worksheet

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.

inside the schengen visa calculation worksheet microsoft excel 22627 - Inside the Schengen Visa Calculation worksheet

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.

inside the schengen visa calculation worksheet microsoft excel 22629 - Inside the Schengen Visa Calculation worksheet

subs profile e1563205311409 - Inside the Schengen Visa Calculation worksheet
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