There’s a little trap in Excel when figuring out the number of days between two dates. Excel has one way, but the real world has two others.
It’s easy to forget the differences and it can matter when the date calculation is close to some limit, for example in a Schenghen Visa Calculation.
How Excel calculates days between two dates
Here’s a standard ‘days between two dates’ calculation. It’s the solution you’ll find in many Excel guides.
Cell C2 has the formula =[Date Out]-[Date In] or =B2-A2
That seems OK and probably is in many situations. For example, the number of nights spent in a hotel or the way UK tax authorities count days in the country for tax residency purposes (they talk about number of ‘midnights’ spent in the UK).
It works because Excel treats all dates as numbers see The Truth behind Excel Dates.
How do you need to calculate days between two dates?
Not all situations calculate the number of days the same way.
You need to check the exact law, regulation, contract or company rules to make sure your worksheet copies that method.
Part days (start and end) count as a full day
Some INclude the arrival/start day as a day. In other words, partial days count as a full day.
That was the situation in our Schenghen Visa Calculation worksheet. When counting the number of days in a country/area, the immigration law includes part days as a whole day (in other words, the arrival and departure days count as a full day). Most immigration rules seem to work this way.
Just add 1 to the formula
Now Cell C2 has the formula =[Date Out]-[Date In]+1 or =B2-A2+1
Whole Days only
A more unusual but possible situation is where only WHOLE days are counted. That’s easily calculated with a minus 1 in the formula.
Now Cell C2 has the formula =[Date Out]-[Date In]-1 or =B2-A2-1
Entering Time Durations in Excel
