Shulou(Shulou.com)11/24 Report--

The original title: "Oh, no, your Excel can't find this function." "

Hi, I am the uncle Mr Zhao who prefers function formulas and likes to use Excel charts to manage warehouses.

Today we are going to talk about a date function, a hidden but very useful function: DATEDIF.

It's a hidden function, and if you want to find it in the formula list, that's impossible.

But direct input can be used.

In our usual work, we can use it to calculate age, length of service and so on.

For instance!

As shown in the figure below, someone's age is calculated based on the date of birth and the due date.

Enter the formula in the D3 cell:

The age of getting is five years old. Ah, at this time, some friends want to ask, 2021-2015 is obviously equal to 6, how can it be 5? Is there something wrong with the formula?

Want to know how the DATEDIF function is calculated? What does the third argument "Y" of this function mean?

Next, let's talk about this function in detail.

The DATEDIF function calculates the interval between two dates.

Its syntax is:

= DATEDIF (start date, end date, type) ❶ calculates the number of years between two dates when the third parameter is "Y". Please note that it is the whole year!

= DATEDIF (B3, C3, "Y")

So, you should also see that in the previous case, from 2015-9-5 to 2021-8-31, it is not yet a full 6 years, and only the deadline is 2021-9-5.

This shows a rule: using the DATEDIF function to calculate age, there must be a full year between two dates to count as a year, not even a day short.

❷ calculates the number of full months between two dates when parameter 3 is "M".

= DATEDIF (B3, C3, "M")

The number of months shown in the picture is 71, and the reason is very simple. It is a full 72 months from 2015-9-5 to 2021-9-5, but the deadline in the picture is 2021-8-31, so it is 71 months.

❸ calculates the number of days between two dates when parameter 3 is "D".

= DATEDIF (B3, C3, "D")

There is no need for me to say more about this, if the two dates are directly subtracted, we will get the same result.

= C3-B3

❹ calculates the difference between the two dates by how many days when the third parameter is "YD", ignoring the number of years in the date.

There is an interval of 5 years + 361 days between the two days. Remove the whole year and get the number of days. The result is 361.

❺ calculates the number of months between two dates when the third parameter is "YM", ignoring the difference in the number of whole years.

= DATEDIF (B3 ~ C3, "YM")

During the two-day period, the interval of 5 years + 11 months + 26 days, remove the number of the whole year, get the number of the whole month, the result is 11.

❻ calculates the number of days between two dates when the third parameter is "MD", ignoring the number of years and months.

During the two-day period, there is an interval of 5 years + 11 months + 26 days, excluding the whole year and the whole month, get the number of days, the result is 26.

As a result, we can sum up, the interval between the two dates can be accurate to the year, month and day.

Enter the following formula in cell D3:

= DATEDIF (B3 cync C3, "Y") & "year" & DATEDIF (B3 cync C3, "YM") & "month" & DATEDIF (B3 cync C3, "MD") & "days" get the result, as shown in the following figure D2 cell:

We can also write an array formula, and the principle is the same.

The formula is as follows:

= TEXT (SUM (DATEDIF (B3PowerC3, {"y", "ym", "md"}) * 10 ^ {4Power2), "00 years, 00 months and 00 days") is shown below:

PS. When the array formula is finished, press [Ctrl+Shift+Enter] to finish.

Dear friends, DATEDIF function, do you know it?

In the future, when you need to calculate the interval between two dates, don't forget it.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), by Zhao Jiaoyang and edited by Yali Zi and Zhu Lan.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.