How do I set up a formula for calculating time duration in an Excel worksheet?

[GenesFISHer]

I am trying to use an Excel sheet containing two dates, and need to
prepare a formula that will yield the amount of time in months or days
between those two dates. The date format in the cells is currently
mm/dd/yy.

Thanks.
GenesFISHer

 

[JE McGimpsey]

I am trying to use an Excel sheet containing two dates, and need to
prepare a formula that will yield the amount of time in months or days
between those two dates. The date format in the cells is currently
mm/dd/yy.

XL stores dates as integer offsets from a base date (either 12/31/1899
or 1/1/1904, depending on the date system). So to get number of days,
just subtract one from the other:

A1: 1/1/2006
A2: 5/20/2006
A3: =A2-A1

which returns 139 in A3, when formatted as General.

For months, you can use DATEDIF:

=DATEDIF(A1,A2,"m")

See http://cpearson.com/excel/datedif.htm for an explanation.

Note that your date format *only* affects how the date is displayed. The
underlying stored value is a number, and is unaffected by the format.