If I used m instead of ym for the third argument, I would have got 24 instead of 0. The result will add the number of months in the Month column where you apply it if there is a 'hire date' older than one year for that person.Ĭell G2 has the formula =DATEDIF($E2,$K$1,'ym'). As a cool trick, if you want to see the number of months after the years, the third argument will be ' ym'. Freezing column E allows you to autofill to the right to cell H2 and still reference E2 and then autofill down to reference column E still, but the rows change accordingly.įor the month's situation, you use the notation ' m'. The dollar sign in front of the letter E froze column E.

Here's an example of how your formula looks like inside the 'year' cell for that specific person.Ĭell F2 has the formula =DATEDIF($E2,$K$1,'y'). Knowing the three arguments and the time notations that make the syntax for this operation (start_date, end_date and unit). So you have a spreadsheet with your employees, and you want to calculate the time (years) that one person spent in the company. At the end of the post the download link is available with the file I used. In this article, I will use some examples and situations to show you how the DateDif function works. We could track how long customers have been in the system.ĭATEDIF has three arguments - start date, end date, and date unit (year/month/ or date).

The HR department could use this to see how long employees have worked. Get days, months, or years between two datesĢ.

Excel calculate time between two dates