# How to use DATEDIF in Excel

DATEDIF is very useful for counting the number of whole calendar years, months and days between two dates. It is a hidden worksheet function and you will not find it in the list of functions but you can use it.

## DATEDIF syntax

=DATEDIF(startDate,endDate, “period”)

You have to write this syntax right into the cell.

## Period values

 y Number of whole calendar years (including leap year) m Number of whole calendar months d Number of days ym Number of whole months in an incomplete year yd Number of days in an incomplete year md Number of days in an incomplete month

## DATEDIF errors

#NUM! – startDate is later than endDate
#VALUE – startDate or endDate is not a valid date

## DATEDIF examples

Note: Dates in this example are in m/d/y format.

A1 cell: 09/01/2012
B1 cell: 11/29/2013

=DATEDIF(A1,B1, “y”) – return  1 (complete calendar year: 09/01/2012–09/01/2013)

=DATEDIF(A1,B1, “m”) – return  14 (complete calendar months: 09/01/2012–09/01/2013)

=DATEDIF(A1,B1, “d”) – return  454 (complete calendar days)

=DATEDIF(A1,B1, “ym”) – return  2 (complete months in incomplete year: 09/01/2013–11/01/2013)

=DATEDIF(A1,B1, “yd”) – return  89 (days in incomplete year: 09/01/2013–11/29/2013)

=DATEDIF(A1,B1, “md”) – return  28 (days in incomplete month: 11/01/2013–11/29/2013)

### Year

A1 cell: 11/01/2012
B1 cell: 11/01/2013

=DATEDIF(A1,B1, “y”) – return  1

A1 cell: 11/01/2012
B1 cell: 10/31/2013

=DATEDIF(A1,B1, “y”) – return  0

### Month

A1 cell: 07/01/2013
B1 cell: 11/01/2013

=DATEDIF(A1,B1, “m”) – return  4

A1 cell: 07/01/2013
B1 cell: 10/31/2013

=DATEDIF(A1,B1, “m”) – return  3

### Day

A1 cell: 07/01/2013
B1 cell: 07/03/2013

=DATEDIF(A1,B1, “d”) – return  2

### Number of years, months and days

A1 cell: 09/01/2012
B1 cell: 11/29/2013

=DATEDIF(A1,B1,”y”) … years (1)
=DATEDIF(A1,B1,”ym”) … months (2)
=DATEDIF(A1,B1,”md”) …days (28)

Want more? Try very nice article by Chip Pearson.

### Add a comment »2 comments to this article

1. Number of days between 2 dates is ok. But!

I have 2 date with time. I need calculate the number of whole days between them. The time is very important here.

And another question:
Can it count with the leap year?

2. So this Excel function makes difference between two columns with dates. Is it right?

Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress