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

The title of the original text: "after using Excel for more than ten years, I unexpectedly know these three functions. Don't be too easy to use!" "

Hello, everyone. I have been studying the function recently.

Someone asked me:

Are there any hidden features in Excel that most people don't know about?

Me: hide the function?

When I have studied functions, the first thing I think of is the hidden functions in Excel.

Search for a hidden function (NUMBERSTRING function) on Microsoft's official website, and you can't find any relevant information!

But it does exist in Excel!

Using this function, we can quickly convert Arabic numerals into Chinese characters:

Other useful hidden functions are: DATEDIF, DATESTRING functions.

Let's introduce them one by one.

These three functions in PS:WPS do not belong to hidden functions and can be found directly in the insertion formula, but in Excel, these three functions belong to hidden functions and need to be entered manually.

NUMBERSTRING function NUMBERSTRING function is a function that converts numbers into Chinese characters.

Grammar rules:

= Numberstring (Value,type)

= Numberstring (numeric, type)

There are three "types" (1 for lowercase, 2 for uppercase, 3 for reading):

In the past, we would use the Text function, but the formula would be much more complicated:

[DBNum1], [DBNum2], [DBNum3] are so hard to remember!

If you can't remember, you have to look for it in the digital format. It's really troublesome.

At this point, it is much more convenient to use the Numberstring function directly.

Of course, for the financial staff, the actual data amount is not just an integer, using this function alone can not meet our needs.

DATEDIF function Datedif function is the most practical hidden function, we can find its related resolution in the date function.

The Datedif function can be used to calculate the number of days, months, or years between two dates.

Grammar rules:

= DATEDIF (start date, end date, information type)

The common "information types" are: y, M, D.

❶ Y:year, year.

You can calculate the number of years of difference between two dates.

Formula:

= DATEDIF (B2 now (), "Y") Now () represents the current system time, and this formula represents the year in which the date of birth differs from the current system time, that is, the first year.

❷ M:month, month.

You can calculate the number of months that differ between two dates.

For example, calculating a project has been going on for several whole months.

The formula is as follows:

= DATEDIF (B3, C3, "M")

❸ D:day, God.

You can calculate the number of days that differ between two dates.

Such as the countdown to the college entrance examination.

The formula is as follows:

= DATEDIF (TODAY (), "2020-6-7", "D")

There are also the following three types of information that are rarely used.

❶ "MD"-the difference in days between the start date and the end date. (ignore the month and year in the date)

❷ "YM"-the month difference between the start date and the end date. (ignore days and years in dates)

❸ "YD"-the difference between the date portion of the start date and the end date. (ignore the year in the date)

In known problems with Excel, the "MD" parameter can also cause negative, zero, or inaccurate results.

The DATESTRING function DATESTRING function converts the date to a uniform text format (yy year mm month dd day).

The frequency of its use is relatively low, we can use it as an understanding.

Grammar rules:

= Datestring (Value)

All right, that's all for the three hidden functions.

To sum up, this article introduces three Excel hiding functions:

❶ Numberstring function, which can convert numbers into Chinese characters.

The ❷ Dateif function, which calculates the number of days, months, or years between two dates.

❸ Datestring function to convert the date to a uniform text format (yy year mm month dd day).

Having learned them, I will leave you with a little thought:

If you are asked to write a function, which function do you think can achieve the same effect as the DATESTRING function? By comparison, which of the two function formulas is more practical?

Friends who know the answer, or know about other hidden functions in Excel, please leave me a message backstage.

Everyone who has been in the workplace knows how important Excel is. If you can only rely on the naked eye and do manual work, you can foresee that there is only one way ahead: overtime.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiao Shuang, Editor: 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.