How to play with excel date function? The following article will help you understand 90% of date operations. Today there will be more functions involved. It is recommended that friends save it first and then read it~
1. Calculate the same day of last month
Xiaoli: "Teacher Miao, I have a question. I want to calculate the month-on-month situation. I hope I can directly compare it with To compare the same day last month, I originally wanted to directly subtract 30 days, but after calculation, I found that some months have 30 or 31 days, and some months have 28 or 29 days. How can I solve this problem? ?"
Teacher Miao: "This is easy to handle. I will teach you a function, EDATE. This function can achieve your needs. I will give you a demonstration." As shown in Figure 1.
Figure 1
EDATE function is used to return the date of the specified month before or after the calculated date. It has two parameters, the basic format is EDATE (start date, interval months). The interval months can be positive, negative, or zero, which represent the month after the calculation date, the month before the calculation date, and this month respectively. as shown in picture 2.
Figure 2
If you want to directly calculate the date of the previous month on the current day, just put TODAY() directly in the formula, as shown in Figure 3.
Figure 3
Xiaoli: "Great, so I can make a deal."
EDATE function is our job A very commonly used function, it can be used not only to calculate employee regularization dates, contract expiration dates, but also product validity expiration dates, etc.
2. About other date functions
Teacher Miao: "Now that I've talked about it, let me teach you a few more Functions about dates."
1. EOMONTH function
It is mainly used to return the end of the specified month before or after the calculated date. The structure is similar to the EDATE function. "As shown in Figure 4.
Figure 4
In addition, we can also use the EOMONTH function to get the corresponding The date of the beginning of the month. As shown in Figure 5.
Figure 5
Use the EOMONTH function to calculate the date of the end of the last month and then add 1, it becomes The 1st of the following month. Moreover, the EOMONTH function can also be used to calculate the number of days in this month. With the DAY function, we can write like this, as shown in Figure 6.
Figure 6
In addition, we can also use the EOMONTH function to determine how many days are left in this month, as shown in Figure 7.
Figure 7
2. DATEDIF function
After talking about the EOMONTH function, let’s talk about a very important date function, the DATEDIF function. This function is used to calculate the difference between two dates Difference, returns the number of years, months, and days between two dates. We can use this function to calculate someone's age, length of service, length of service, etc. As shown in Figure 8.
Figure 8
The third parameters Y, M, and D of the DATEDIF function in the above figure represent the return of the number of whole years, the number of whole months, and the number of days between the two dates respectively. However, this function There is a taboo, that is, the first date in the function must be smaller than the second date.
3. WEEKDAY function
This function is about the week. Used to return the day of the week for a date. It has two parameters. The basic format is WEEKDAY (calculated date, specifying the day of the week as the first day of the week). If the second parameter is omitted, Sunday will be used as the first day of the week. Due to different customs about the week in different places, some countries use Sunday as the first day of the week, and some countries use Monday as the first day of the week. At this time, we can adjust the WEEKDAY function The second parameter is calculated, as shown in Figure 9.
Figure 9
But some people will definitely say that our company uses Tuesday as the first day of the week. What should we do? Don’t worry, this function really takes these issues into consideration, as shown in Figure 10.
Figure 10
4. WEEKNUM function
Finally, I will introduce to you a function WEEKNUM about the week. , this function can return the week of the year for the specified date. The structure is similar to the EDATE function. The second parameter is used to specify the day of the week as the first day of the week. When the second parameter is omitted, Sunday is also used as the first day of the week. As shown in Figure 11.
Figure 11
Summary: These are the functions we talked about today. They can help us better manage dates in our daily work. calculate. The table below lists all the function formulas explained today, and attaches several other methods of date calculation to facilitate your induction and summary.
Serial number |
Description |
Function |
1 |
Returns the date of the specified month after the calculated date |
=EDATE(TODAY (),1) |
| ##2 | Returns the end of the specified month after the calculated date | =EOMONTH(TODAY(),1) |
| Calculate two dates The number of whole years difference | =DATEDIF(A2,B2,"Y") | |
| Returns the day of the week when the calculated date is | =WEEKDAY(TODAY(),2) | |
5 |
Returns the week number of the year in which the calculated date is located |
=WEEKNUM(TODAY(),2) |
6 |
Calculate the day of the quarter today is |
=COUPDAYBS(TODAY(),"9999-1",4,1) 1 |
7 |
Calculate how many days there are in the current quarter |
=COUPDAYS(TODAY(),"9999-1",4,1) |
8 |
Calculate which quarter today belongs to |
=MONTH(MONTH (TODAY())*10) |
Related learning recommendations:excel tutorial
The above is the detailed content of Practical Excel skills sharing: Let you play with date functions and master 90% of date operations!. For more information, please follow other related articles on the PHP Chinese website!