Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Difference function and argument for two simple amounts | Excel Discussion (Misc queries) | |||
DateDif Average? Damn DateDif | Excel Worksheet Functions | |||
DATEDIF Simple request | Excel Worksheet Functions | |||
charting a difference of 2 columns' w/o adding a difference column | Charts and Charting in Excel | |||
Simple question: Difference between two dates? | Excel Discussion (Misc queries) |