Excel trap in date calculations


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, in fact we forgot it ourselves in the Schenghen Visa Calculation.  This article is part explanation, part warning and part mea-culpa.

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

1
=[Date Out]-[Date In]

   or 

1
=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.

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 day counts).

All that’s necessary is to add 1 to the formula

Now Cell C2 has the formula

1
=[Date Out]-[Date In]<strong>+1</strong>

   or 

1
 =B2-A2<strong>+1</strong>

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

1
=[Date Out]-[Date In]<strong>-1</strong>

   or 

1
=B2-A2<strong>-1</strong>  

See also:

The Truth behind Excel dates

Entering Time Durations in Excel