Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
wal wal is offline
external usenet poster
 
Posts: 8
Default DATEDIF vs. simple difference

Excel 2007 (also 2003)

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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Difference function and argument for two simple amounts lawrencae Excel Discussion (Misc queries) 8 January 5th 08 12:52 PM
DateDif Average? Damn DateDif UTCHELP Excel Worksheet Functions 14 November 17th 05 10:30 AM
DATEDIF Simple request TAS-LGS Excel Worksheet Functions 1 October 26th 05 01:30 PM
charting a difference of 2 columns' w/o adding a difference column Wab Charts and Charting in Excel 4 July 27th 05 02:37 AM
Simple question: Difference between two dates? dstock Excel Discussion (Misc queries) 2 June 23rd 05 01:40 AM


All times are GMT +1. The time now is 12:47 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"