DateDiff vs DateDif (Access vs Excel)

3
Found 11th Dec 2015
Hi,

A bit of a weird one here I ma afraid. I want to measure the elapsed time in months between two sets of dates (ie a start date and an end date). The data is held within Microsoft Access 2010, and in a query I have used the following:
Months Open: DateDiff("m",[Elapsed Time Start Date],[Elapsed Time End Date])

I have since copied the results from Access and pasted into Microsoft Excel 2010 to reconcile. In Excel I have used the following:
=DATEDIF(K2,L2,"m")

I really thought that the number of months returned would be the same in both the different programmes. However the number of months actually differs, even though the same source data is being used.

For example, with using the 2 dates; 17/12/2014 and 11/12/2015. In Access it returns 12 months, and in Excel it returns 11.

My first thought was that Access was returning complete and incomplete months, ie 10 complete months and 1 partial month. And that Excel was returning just complete months. But I don't think this is the case either.

Please can someone help?

  1. Ask
Groups
  1. Ask
3 Comments

would this work
date 1 = A1
date 2 = A2
=(A2-A1)/365.25*12

Excel does it correctly in my opinion..

There's some discussion on the topic here:

mrexcel.com/for…tml

Is one of them automatically using American date format
ie 12 June 2015
Uk 12/06/15
US 06/12/15
Post a comment
Avatar
@
    Text

    Top Discussions

    Top Merchants