Nov
25
2011

Datedif – Excel’s secret function

In the post about Clippy, I mentioned that there are a few features in excel that are undocumented. Here’s one that I find pretty useful. I’m not sure of the history of it, but I know that when I was looking for a built in function to do something a few years ago, I came across this one. You’ll notice when you type it in, excel doesn’t suggest any syntax for you until you’ve put the opening parenthesis on the formula. This is unlike other formulas – for example, try typing in vlookup and you’ll see that excel will suggest the ending of the formula for you:

Play around with it. Oh, since excel doesn’t come up with any tips on using this function, you can use this guide below:

=DATEDIF(Date1, Date2, Interval)

Where:
Date1 is the first date,
Date2 is the second date,
Interval is just whether you want your return in whole years, whole months, or whole days. Depending if you want days, weeks, or months, use the following:
“d”
“m”
“y”

Not exactly the most useful function there is, but it can save you the step of diving by number of days in a month, months in a year, etc. More of something that gives you bragging rights – you know know a top-secret excel feature!

One Response to “Datedif – Excel’s secret function”

  1. I won’t be able to thank you fully for the articles on your web-site. I know you’d put a lot of time and energy into all of them and hope you know how much I appreciate it. I hope I could do the same for someone else sometime.

Leave a Reply