Schengen 90/180 Spreadsheet: A Formula That Counts Days, Not Visits

A spreadsheet is a fine way to track Schengen days. Plenty of long-stay travellers keep one for both Schengen and tax days. The hard part is the formula, because the 90/180 rule counts days in a window that moves every day. A formula that looks right can quietly count something else.

Below are two formulas that work in Excel and Google Sheets, a check you can run on your own sheet, and the mistake to avoid. Both formulas were tested against a day-by-day count on 20,000 random sets of dates on 22 October 2026.

Disclosure: we make NomadPoint, an iPhone travel planner that counts Schengen days from your itinerary. We also built a free Schengen calculator that needs no app or account. You do not need either to use the formulas here.

The rule your sheet has to follow

• On any day, look back over that day and the 179 days before it (180 days in all).
• Count every day you were inside the Schengen area in that window. All Schengen countries add up together.
• The day you arrive and the day you leave both count as full days.
• The total must never be more than 90, on any day of your stay.

Option 1: one row per visit

Put each visit on its own row: entry date in column A, exit date in column B, starting at row 2. If you are inside Schengen right now, put today's date as the exit. Put the date you want to check in E1.

=SUMPRODUCT(((B2:B50<$E$1)*B2:B50+(B2:B50>=$E$1)*$E$1-((A2:A50>$E$1-179)*A2:A50+(A2:A50<=$E$1-179)*($E$1-179))+1>0)*((B2:B50<$E$1)*B2:B50+(B2:B50>=$E$1)*$E$1-((A2:A50>$E$1-179)*A2:A50+(A2:A50<=$E$1-179)*($E$1-179))+1))

It is long because it clips each visit to the 180-day window ending on E1, counts the days left over, and adds them up. Empty rows count as zero. It needs no array entry in Excel or Google Sheets.

The result is the number of Schengen days in the window ending on E1. Ninety minus that is what you have left on that day.

Option 2: one row per day

This one is easier to read and easier to trust. Column A holds every date (type the first, drag down). Column B holds 1 if you were in Schengen that day and 0 if not. In C2, then filled down:

=COUNTIFS(A:A,">="&A2-179,A:A,"<="&A2,B:B,1)

Every row now shows the days used in the window ending on that date. Any row above 90 is an overstay. Conditional formatting on column C (greater than 90, red) makes it obvious.

The formula to avoid

One formula that circulates online, on an immigration guide among others, looks like this:

=SUMPRODUCT(--(B$2:B$100>=B2-179),--(A$2:A$100<=B2))

It counts how many visits touch the window, not how many days you spent in it. Take 60 days in Schengen, 20 days out, then 31 days back in. The formula returns 2. The real count is 91, which is an overstay.

Check your sheet before you trust it

Run these through your own formula. If any answer differs, the formula is wrong.

The last two rows matter most. Days drop off the back of the window one at a time, so you get them back slowly, not all at once after 90 days away.

Your stays in the Schengen Area

0 trips counted

• Entry 1
  Exit 1
  Remove

Add a tripClear

Assess as of

Defaults to today. Set a future date to check what a border officer would count on the day you arrive.

Everything is worked out in your browser. No dates are uploaded, stored on a server, or sent anywhere.

Days remaining on 2 Nov 2026

90of 90

0 days counted in the window 7 May 2026 to 2 Nov 2026.

If you stay from this date

Until 30 Jan 2027

90 consecutive days, allowing for days rolling out of the window behind you

NomadPoint keeps this count for you from your itinerary, so you never type these dates again. Free on iPhone.

Get NomadPoint

Checking a planned visit

To check a planned visit, add it as a row (or mark its days with 1) and look at its last day. If the count there is above 90, shorten the visit or move it later.

Spreadsheet or app?

A spreadsheet costs nothing and keeps your dates on your own machine. It is only as good as your typing, though: a missed exit or a wrong year inflates the count and nothing warns you.

If you would rather not maintain one, our Schengen calculator takes the same dates and does the counting. NomadPoint counts it from your itinerary on iPhone, so moving a flight moves the count. It is free on the App Store. We compared other options in the best Schengen calculator apps.

FAQ

Does Google Sheets handle the same formulas?

Yes. Both work as written in Google Sheets and in Excel 2010 or later. Dates must be real dates, not text.

Does a day spent in transit count?

If you passed through Schengen border control, yes. An airside transfer without entering does not count.

Do days before the Entry/Exit System started count?

Under the rule, yes, if they fall within the last 180 days. Since 6 October 2026 no day before 10 April 2026 is inside anyone's window. See why EES can show the wrong number of days.

Is my spreadsheet proof at the border?

No. The border reads its own record. Keep boarding passes or receipts for any exit you think was not recorded.