Share 10 Excel function formulas: extract years from dates, randomly generate integers, convert dates to text format.
Hello, everyone. I'm Xiaolan who knows a little bit about Excel.
There are many functions in Excel, today we will share 10 frequently used function formulas, suggest collection!
❶ extract year from date = YEAR (A1)
The A1 cell contains the date "2023-10-09", and the year "2023" will be extracted using this formula.
❷ randomly generates an integer = RANDBETWEEN (1100)
Using this formula will generate a random integer between 1 and 100.
❸ calculates the number of working days = NETWORKDAYS (A2PowerB2)
A2 and B2 contain start and end dates, and this formula calculates the number of working days between the two dates (excluding Saturdays and Sundays).
If there are holidays between two days, the third parameter needs to be used; if there are holidays, other formulas need to be used.
Date at the beginning of the year and the end of the year calculated by ❹ = DATE (YEAR (A2), 1d1)
= DATE (YEAR (A2), 121.31)
❺ converts the date to text format = TEXT (A2, "yyyy mm month dd date")
This formula formats the date into the text format of "year, month, day".
❻ converts the date to week = TEXT (A2, "AAAA")
This formula formats the date into the text format of "week X".
❼ Lookup data = VLOOKUP (E2MagneB2GRO C10Cooper 2MagneFALSE)
E2 contains the lookup value, which will find and return the value of the corresponding F column in the B2:C10 range.
❽ calculated age = YEAR (TODAY ())-YEAR (A2)
The A2 cell is the date of birth, and this formula can calculate the age from birthday to today.
❾ extracts the specified character = MID from the text string.
A2 contains the text "learn Excel with Akiba". This formula will extract the 2 characters starting from the second character, resulting in "Akiba".
❿ concatenates multiple text strings = CONCAT (A2, ", B2)
CONCAT can combine multiple texts into one.
All right, this is the end of the introduction of 10 small Excel formulas!
This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Zhu Lan
