Date calc limitations in Excel

[Eamon]

Hi There,

I've encountered a problem in the way that Excel calculates the perio
between two dates. In particular if the first date is the start of th
month and the second date is the last day of the month, Excel assume
that this is an incomplete month. Therefore, if the period is rounde
to complete months Excel will calculate the period as a month short.

For example:

01-Apr-2003 to 31-Mar-2004 = 12 months

However, Excel will calculate this as one day short of 12 months.

Does anyone have a work-around for this?

Thanks,

Eamo

 

[Andy B]

Hi

To Excel, dates are just numbers. If you take 1 away from 3, you get 30 -
just as Excel does. The usual workaround is just to add 1 to each
calculation.

Hope this helps.

 

[Norman Harker]

Hi Eamon!

Excel is counting fence panels. You're counting fence posts.

Just add a day to your calculations.

Example:
A1: 1-Apr-2003
B1: 31-Mar-2004
=DATEDIF(A1,B1+1,"y")
Returns 1

 

[Frank Kabel]

Hi
with what formula are you calculating this?

 

[Eamon]

Hi Frank,

I'm using the formula

=TRUNC((B1-B2)/365.25*12,0)/12

This allows me to return the period in complete years and months. Thi
works great for all dates except where the dates fall on the first an
last day of the month.

I've already added 1 day (or month) where necessary, but I was reall
searching for an all-inclusive remedy! Maybe I'm being too hopeful?

Thanks,

Eamo

 

[Frank Kabel]

Hi
use DATEDIF for this. See:
http://www.cpearson.com/excel/datedif.htm