Bug in DATEDIF in Excel 2007?

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Feb 6, 2008

• #2

Yup, looks like a bug. Excel 2003 gives the correct results.

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Feb 6, 2008

• #3

Thanks Andrew.

Any help with an alternative formula

 

Upvote 0

Richard Schollar

MrExcel MVP

Joined

Apr 19, 2005

Messages

23,707

• Feb 6, 2008

• #4

Barry hasn't been around much today - I just know he'll have a slick formula for it though Peter...

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Feb 6, 2008

• #5

RichardSchollar said:

Barry hasn't been around much today - I just know he'll have a slick formula for it though Peter...

Click to expand...

That's exactly what I was hoping for Richard

 

Upvote 0

D

Dave Patton

Well-known Member

Joined

Feb 15, 2002

Messages

6,345

Office Version

1. 365
2. 2010

Platform

1. Windows

• Feb 6, 2008

• #6

Hello VoG II

Excel 2003 gives the result of 26 days.

You could try the Morefunc add-in. I have not used this add-in with Excel 2007 and I do not have Excel 2007 on my notebook.

=DATE.DIFF(A1,B1,3)

Dave

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Feb 6, 2008

• #7

Thanks Dave - I may try that but I was hoping for a solution using native Excel functions.

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Feb 8, 2008

• #8

Bump

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Feb 8, 2008

• #9

I found a UDF that works correctly http://www.vbaexpress.com/kb/getarticle.php?kb_id=866

I would still prefer a formula solution though

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Feb 8, 2008

• #10

Hello VoG II

I'd be reluctant to suggest an alternative to DATEDIF(A1,B1,"md") which exactly replicates DATEDIF because that sometimes gives odd results, even in versions of Excel before 2007, e.g. if A1 is 31st Jan 2008 and B1 is 1st March 2008 it gives a result of -1

This formula should replicate the results, except for negative ones like that....

=MOD(DAY(B1)-DAY(A1),IF(DAY(B1)< DAY(A1),DAY(B1-DAY(B1)),31))

Not sure if DATEDIF(A1,B1,"y") and others work correctly in Excel 2007......

 

Upvote 0