W

#### wal

For the number of days between March 30, 2009, and April 1, 2009 (for

example), the following formulas give the following results:

=B6-A6 >>>> 2 days

=DATEDIF(A6,B6,"d") >>>> 2 days

=DATEDIF(A6,B6,"md") >>>> 1 day

I didn't realize this until I happened to fill in two dates that

crossed two months but were less than one month apart. My actual

formula is the following, which I thought would safely cover date

differences of any length:

DATEDIF(A2,B2,"y") & " year(s), " & DATEDIF(A2,B2,"ym") & " month(s),

" & DATEDIF(A2,B2,"md") & " day(s)")

The problem is the "md" argument in the day component. Chip Pearson

at http://www.cpearson.com/excel/datedif.aspx provides the definition

of "md" as "Complete calendar days between the dates AS IF THEY WERE

of the same month and same year" (apparently from the Excel 2000 Help

files). What does that mean --- "as if they were"?

If you substitute in my long formula above "d" in place of "md", you

get a poor result when the dates are farther apart: e.g., March 12

2008 to April 6 2009 gives the result 1 year, 1 months, 419 days.

In short, you can't use my long formula above as a foolproof general

way to calculate date differences, because if your result is less than

a month but happens to encompass two calendar months, you'll get an

incorrect figure.

I guess the safest solution is to have an embedded "IF" function: If

year difference is 0 and month difference is 0, simply subtract the

dates; otherwise, use the long formula above -- ??

Thanks for any comments.