Excel formulas how many years

Further on in this tutorial, you will learn a couple of special functions that calculate age faultlessly regardless of the year.

The first two arguments are obvious and hardly require any additional explanation. Basis is an optional argument that defines the day count basis to use. This function can return the difference between two dates in various time units such as years, months and days, depending on the value you supply in the unit argument:. Since we aim to calculate age in years , we are using the "y" unit:. No additional rounding function is needed in this case because a DATEDIF formula with the "y" unit calculates the number of full years:.

As you have just seen, calculating age as the number of full years that the person has lived is easy, but it is not always sufficient. If you want to know the exact age, i. The above formula returns 3 numbers years, months, and days concatenated in a single text string, as shown in the screenshot below:. Does not make much sense, uh? To make the results more meaningful, separate the numbers with commas and define what each value means:. The result looks much better now:.

The formula works great, but you can improve it even further by hiding zero values. The following screenshot demonstrates the final Excel age formula in action - it returns age in years, months, and days, displaying only non-zero values:.

The generic age calculation formulas discussed above work great in most cases. In some situations, however, you may need something very specific. Of course, it's not possible to cover every and each scenario, but the following examples will give you some ideas on how you can tweak an age formula depending on your particular task.

To make your age formula more flexible, you can input the date in some cell and reference that cell in your formula:. This formula comes in handy in situations when the complete date to calculate at is not defined, and you know only the year.

About Us Our team. How to calculate elapsed years, months, or days from a certain date in Excel? Calculate elapsed years Select a blank cell to output the elapsed years, enter the below formula into it and press the Enter key. Click OK. See screenshot: 3. Now you are getting the elapsed years, months or days from a certain date. Terms and Conditions of use. Home About Advertise With Us.

Newsletter Get latest updates from exceltip in your mail. Powered by GDPR plugin. Privacy Overview This website uses cookies so that we can provide you with the best user experience possible.

You can adjust all of your cookie settings by navigating the tabs on the left hand side. Strictly Necessary Cookies Strictly Necessary Cookie should be enabled at all times so that we can save your preferences for cookie settings. Enable All Save Changes. Type a formula like the above example. The 1 in the formula establishes Saturdays and Sundays as weekend days, and excludes them from the total. INTL function.

If Saturday and Sunday are not your weekend days, then change the 1 to another number from the IntelliSense list. For example, 2 establishes Sundays and Mondays as weekend days. If you are using Excel , skip this step.

If you don't have holidays, you can leave the comma and MyHolidays out. Or, you could type each holiday inside the formula. You can calculate elapsed time by subtracting one time from another. First put a start time in a cell, and an end time in another. Make sure to type a full time, including the hour, minutes, and a space before the AM or PM.

In this example, the start time is in cell D80 and the end time is in E Make sure to type the hour, minute, and a space before the AM or PM. To calculate the time between two dates and times, you can simply subtract one from the other.

However, you must apply formatting to each cell to ensure that Excel returns the result you want. Each cell should have a month, day, year, hour, minute, and a space before the AM or PM. This isn't the date you'll set, it's just a sample of how the format will look. The result will probably look like a number and decimal. You'll fix that in the next step. Select Custom. In the Type box, type [h]:mm. Table of contents.

Common uses of functions.