Beware the date calculation trap in Excel

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  

The Truth behind Excel dates

Entering Time Durations in Excel

Join Office for Mere Mortals today

Office for Mere Mortals is where thousands pick up useful tips and tricks for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  We've never spammed or sold addresses since we started over twenty years ago.
Invalid email address