In excel how do i subtract two dates to get time in position

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using Excel 2000, I'am putting a spread sheet together for training. In
one column I put the "start date in position" in the next column I put the
"Time in position" This sheet is undated every week. What I'm trying to
accomplish is to be able to insert the current date at the top of the sheet
to automatically update the time in position column and have it displayed in
"years" and "months"
 
There are various ways and one is:
A1 is today's date
B1 is "start date in position"
Put the following formula for "Time in position"
=INT(YEARFRAC(B1,A1)) & " years and "
ROUND((YEARFRAC(B1,A1)-INT(YEARFRAC(B1,A1)))*12,2) & " months"


- Manges
 
If you were doing this as a worksheet formula, you could use =datedif()

Chip Pearson's web page is the best source of info for this:
http://www.cpearson.com/excel/datedif.htm

And if you want to do it VBA, there's a datediff (note the spelling
difference--double FF at the end) function.

VBA has a nice example of this.

But if you put:

=today() in A1, you could use:

this in cell I2 (and drag down)

=DATEDIF(I2,$A$1,"y")&" Years "&DATEDIF(I2,$A$1,"ym")&" Months"

In fact, you could avoid A1 completely and just use a formula like this:
=DATEDIF(I2,today(),"y")&" Years "&DATEDIF(I2,today(),"ym")&" Months"

(Chip's site has lots of info on it.)
 
Back
Top