Excel For Mac Calculate The Number Of Months Between Two Dates

-->

How do I calculate an employee’s tenure given their start date?

Calculate the number of days, months, or years between two dates using Excel functions. For example, you can calculate age in years, months and days. You can also calculate the number of days between another date and today. You can also calculate elapsed time.

How can I determine a person’s age given their birth date?

In a customer conversation this week, I was asked to help solve this question in Power BI. Interestingly, I was intrigued by this topic almost 5 years ago when I wrote a blog entry to solve this in TSQL, but now it is time to solve it in DAX for use in Excel or Power BI. The challenge with this question is that it sounds so simple, yet turns out to be a bit tricky. There are likely several other creative ways to solve this. If you have found another way, please share it in the comments below.

Let’s start with a list of employees and their start dates:

Now create a measure to represent today’s date (or whatever date you want to use as the end date for the calculation).

TodaysDate = FORMAT(TODAY(),'mm/dd/yyyy')

I am using YEARFRAC as the basis of the year/month/day calculations. YEARFRAC returns the number of years in a fractional format between two dates. From this value, we can use additional math functions, such as FLOOR() and MOD() to break out the individual year/month/day components.

Create the following columns in Power BI (or formulas in Excel):

Years = FLOOR(YEARFRAC(Source[StartDate],[TodaysDate]),1)

Months = FLOOR(MOD(YEARFRAC(Source[StartDate],[TodaysDate]),1) * 12.0,1)

Days = SWITCH(DAY(Source[StartDate]) > DAY([TodaysDate]),
TRUE(), (DAY(EOMONTH([TodaysDate],-1)) - DAY(Source[StartDate])) + (DAY([TodaysDate])),
FALSE(), (DAY([TodaysDate])-DAY(Source[StartDate])))

Using these calculations, we can display the date difference in years, months, and days:

The sample .pbix file can be downloaded here to explore on your own.

Thanks,
Sam Lester (MSFT)

A reader contacted me recently with a deceptively simple Microsoft Excel question: “How do I calculate the difference between two dates?”

I say “deceptively simple” because the answer depends upon the context, namely, whether the two dates being compared are actually embedded in cells within the Microsoft Excel spreadsheet.

If they are, then the formula needed to perform the calculation is actually very simple (assuming you already know how to do simple formulas in Microsoft Excel):

Komponen sketchup download for mac free. If, however, the dates are not anywhere else in the spreadsheet, you’ll have to embed them in the formula itself. This is done using the DATEVALUE function:

What a lot of people don’t realize is that Microsoft Excel doesn’t actually store dates per se. It stores each date as a number calculated as the number of days from 1/1/1900, so that January 1, 1900 is “1” and all subsequent dates are successively higher numbers. The DATEVALUE function instructs Excel to convert the date back to its original number form in order to perform the arithmetic operation, in this case calculating the number of days between the two dates.

So the next time you find yourself needing to calculate the difference between the two dates, use a formula like one of those illustrated above.