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

I'm Laxiaden, an Excel teacher who can design forms.

As an Excel teacher, I am not afraid to ask questions, really!

I even enjoy the process of answering questions.

But what scares me is that the same questions are asked over and over again.

For example, the question of date:

Why did ❶ enter date 1.10 to become 1.1?

How does ❷ merge date and time together?

How does ❸ use the formula to tell the hours from the minutes?

❹ how to write the formula if you want to calculate the length of service up to 2019-12-31?

Today, Mr. bin Laden summarized three types of date calculation questions and six frequently asked questions.

Don't make excuses, take it back and recite it 10 times, you won't learn it quickly, I don't deserve to be your teacher!

(little E voiceover: there is no need to turn off the emmm, T T)

1. Date format problem ▋ how to correctly enter 1.10 date Q: please tell me that the date 1.1-1.9 is all OK, but if you enter 1.10, it automatically becomes 1.1. how to modify it in batches?

I get angry when I see this problem. I'll say it again, take out a small notebook and take good notes.

1 and 10 are the same number by default in Excel.

A decimal, the zero after the decimal point, is dropped by default, because it doesn't make any sense.

The correct way to 👉:

Just format the cell as text, and then type the date.

However, I cannot help but want to say a few more words.

Do not use dots in the date format, here is the standard format

In this way, the date can be folded and the date-related functions can be used when filtering.

How did the ▋ date change to 2019.05.06 format? Q: teacher, how can I change the date format to 2019.05.06?

Now it is a manual modification, is there a faster way?

Students with such an axis are sure to become the head of the family after marriage.

Mr. bin Laden taught you a trick, remember.

👉 text version steps:

❶ select the cell, press [Ctrl+1] to open the cell format

❷ sets the custom cell format to: "yyyy.mm.dd"

❸ press [OK] to OK.

In this way, the date format satisfies the style and can be filtered when grouping without affecting the formula calculation.

2. Date merging extraction problem how does ▋ merge date and time together? Q: when the date and time are merged with the & symbol, they all become numbers. How can the merged column show the date?

Did I mention it during the self-study and extra meal last week?

Date and time are essentially numbers. What symbols do you use with two numbers? they are used to deal with text.

Just add it up!

How does ▋ extract months and give birthday noodles to employees in May and June? Q: how to extract the month from the birthday information, so as to give a bowl of birthday noodles to employees who have birthdays in May and June?

Could you pick a little more? A bowl of noodles? I don't want to waste my brain cells on you.

Remember, extract the month with the MONTH function, a formula.

In addition, there are YEAR extraction year, DAY extraction days, analogy, next time do not ask ah.

3. Date-time difference calculation how does ▋ calculate the length of service up to 2019-12-31? Q: teacher, there are entry and departure time in Excel, the number of days this month, then the number of days of attendance, how to use the function to calculate?

They have all left their jobs, so what if they count for an extra day or two? I also made a contribution to the company!

What? The money saved is on me?

All right!

As I said earlier, the date is essentially a number, and the number of days is calculated directly by the departure date, minus the entry date.

The money saved is on me. Are you sure you can take it?

To be sure, I'll teach you another function, the DATEDIF function, which can calculate not only the days of difference, but also the number of months and years of difference.

❶ parameter 1, the start date to be calculated.

❷ parameter 2, the end date to be calculated.

❸ parameter 3, the date difference format to be calculated. "D" represents the number of days, "M" represents the number of months, and "Y" represents the number of years.

How does ▋ calculate whether to be late for work or leave early? Q: teacher, there are attendance records and attendance time, how to calculate whether you are late or not?

If I tell you how to do it, can you scratch my record of being late this morning?

Take it!

Date time is essentially a number, the integer part is the number of days, and the decimal part is time.

If you are late, you have to extract the time, that is, get the decimal out.

❶ has an INT function to extract integers, and then subtract integers from the original number, which is a decimal.

❷ uses this decimal to directly subtract the work time at 08:00, the result > 0 is late, am I good?

Note that time cannot be subtracted directly in this formula. Only when it is written in time format in double quotation marks can it be calculated correctly.

❸ finally put an IF function on the outside to change the number of red 0 into "late".

What a beautiful job!

4. After summing up, that's all for this lesson. Let's make a brief summary.

❶ date standard format, is 2020-3-20 or 2020-3-20.

❷ dates and times are essentially numbers, integers are dates and decimals are times.

❸ extraction date was extracted by YEAR, MONTH and DAY.

Finally, leave a homework assignment:

How do you calculate the number of days since you joined the job today?

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: bin Laden Dony

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.