Date calculation

  • Thread starter Thread starter tonyalt3
  • Start date Start date
T

tonyalt3

I would like to calculate how many months it has been from one cell to
another. Example: cell a1 has 1/5/2009 and cell b1 has 5/17/2009. Is
there a function for this?
 
tonyalt3 said:
I would like to calculate how many months it has been
from one cell to another. Example: cell a1 has 1/5/2009
and cell b1 has 5/17/2009.
Click to expand...

The correct solution for you depends on how you want to count the month
difference.

One solution:

=datedif(A1,A2,"m")

where A1 has 1/5/2009 and A2 has 5/17/2009.

But note that A1 were 1/18/2009, DATEDIF would result in 3. If you would
prefer 4 still, another solution is:

=(year(A2)-year(A1))*12 + month(A2)-month(A1)

On the other hand, note that the latter solution would return 1 if A1 were
1/31/2009 and A2 is 2/1/2009; that is, "one month" for a single day's
difference. Is that acceptable?
 
Hi,

The undocumented DATEDIF function has the following form:

DATEDIF(StartDate,EndDate,Unit)

Where Units are on the left in the table below and their results on the
right. Note that the Unit must be quoted - "y" for example.

y Whole years between two dates
m Whole months between two dates
d Whole days between two dates
md Number of days between two dates ignoring months
ym Number of months between two dates ignoring years
yd Number of days between two dates ignoring years

=DATEDIF(A1,A2,"Y") returns the number of whole years between the dates in
cell A1 and A2
 
Back
Top