It’s a common date task to have to figure out the nth weekday in a given month. For example, you might need to schedule a budget meeting for the first Monday in each month, or you might want to plan the annual company picnic for the third Sunday in June. These are tricky calculations, to be sure, but Excel’s date functions are up to the task.
Phải tính một ngày trong tuần thứ n của một tháng đã biết là một công việc thường gặp. Ví dụ, bạn cần lập thời khóa biểu cho một cuộc họp vào mỗi ngày thứ Hai đầu tháng, hoặc bạn muốn lên kế hoạch cho chuyến dã ngoại hằng năm của công ty vào ngày Chủ Nhật thứ ba trong tháng Sáu... Chắc chắn, đây là những bài toán khó, nhưng những hàm ngày tháng của Excel sẽ giúp bạn thực hiện được.
As with many complex formulas, the best place to start is with what you know for sure. In this case, we always know for sure the date of the first day of whatever month we’re dealing with. For example, Labor Day always occurs on the first Monday in September, so you would begin with September 1 and know that the date you seek is some number of days after that. The formula begins like this:
Như với nhiều công thức phức tạp, cách tốt nhất là bắt đầu với những gì bạn đã biết chắc chắn. Trong trường hợp này, chúng ta luôn biết chắc chắn được ngày đầu tiên của bất kỳ tháng nào mà chúng ta muốn xử lý. Ví dụ, ngày Lễ Lao Động (của Mỹ) luôn luôn là ngày thứ Hai tuần thứ nhất của tháng Chín, do đó, bạn sẽ bắt đầu với ngày 01/9, và biết rằng ngày cần tìm là một ngày nào đó sau ngày này. Công thức bắt đầu như sau:
=DATE(Year, Month, 1) + days
Here,
Year is the year in which you want the date to fall, and
Month is the number of the month you want to work with. The
days value is what you need to calculate.
Ở đây, Year là năm mà bạn muốn ngày tháng rơi vào trong đó, và Month là con số của tháng mà bạn muốn làm việc. Giá trị days là những gì bạn cần tính toán.
To simplify things for now, let’s assume that you’re trying to find a date that is the first occurrence of a particular weekday in a month (such as Labor Day, the first Monday in September).
Để đơn giản hóa vấn đề, hãy giả sử rằng bạn đang cố gắng tìm ngày xuất hiện đầu tiên của một ngày trong tuần cụ thể nào đó của một tháng (chẳng hạn như ngày Lễ Lao Động, ngày thứ Hai đầu tiên của tháng Chín).
Using the first of the month as your starting point, you need to ask whether the weekday you’re working with is less than the weekday of the first of the month. (By “less than,” I mean that the WEEKDAY() value of the day of the week you’re working with is numerically smaller than the WEEKDAY() value the first of the month.) In the Labor Day example, September 1, 2007, falls on a Wednesday (WEEKDAY() equals 4), which is greater than Monday (WEEKDAY() equals 2). The result of this comparison determines how many days you add to the 1st to get the date you seek:
Sử dụng ngày đầu tháng như là điểm bắt đầu, bạn cần biết liệu rằng ngày trong tuần (thứ) mà bạn muốn làm việc với nó có nhỏ hơn ngày trong tuần (thứ) của ngày đầu tháng không. ("Nhỏ hơn", ý tôi muốn nói là liệu giá trị WEEKDAY() của ngày trong cái tuần lễ mà bạn muốn làm việc với nó nhỏ hơn giá trị WEEKDAY() của ngày đầu tháng không). Trong ví dụ ngày Lễ Lao Động (thứ Hai đầu tiên của tháng Chín), ngày 01/9/2007 rơi vào ngày thứ Tư (hàm WEEKDAY() trả về 4), lớn hơn ngày thứ Hai (hàm WEEKDAY() trả về 2). Kết quả của việc so sánh này sẽ quyết định số ngày bạn phải cộng thêm vào ngày 1 để có được ngày bạn muốn tìm:
- If the day of the week you’re working with is less than the first of the month, the date you seek is the first plus the result of the following expression:
Nếu ngày trong tuần (thứ) của ngày bạn đang làm việc với nó nhỏ hơn ngày trong tuần (thứ) của ngày đầu tháng, thì ngày mà bạn tìm là ngày đầu tháng cộng thêm kết quả của biểu thức sau:
7 - WEEKDAY(DATE(Year, Month, 1)) + Weekday
Here, Weekday is WEEKDAY() value of the day of the week you’re working with. Here’s the expression for the Labor Day example:
Ở đây, Weekday là giá trị của WEEKDAY() cho ngày mà bạn đang xử lý. Đây là biểu thức cho ví dụ ngày Lễ Lao Động:
7 - WEEKDAY(DATE(2007, 9, 1)) + 2
- If the day of the week you’re working with is greater than or equal to the first of the month, the date you seek is the first plus the result of the following expression:
Nếu ngày trong tuần (thứ) của ngày bạn đang làm việc với nó lớn hơn hoặc bằng ngày trong tuần (thứ) của ngày đầu tháng, thì ngày mà bạn tìm là ngày đầu tháng cộng thêm kết quả của biểu thức sau:
Weekday - WEEKDAY(DATE(Year, Month, 1))
Again, Weekday is WEEKDAY() value of the day of the week you’re working with. Here’s the expression for the Labor Day example:
Nói lại, Weekday là giá trị của WEEKDAY() cho ngày mà bạn đang xử lý. Đây là biểu thức cho ví dụ ngày Lễ Lao Động:
2 - WEEKDAY(DATE(2007, 9, 1))
These conditions can be handled by a basic IF() function. Here, then, is the generic formula for calculating the first occurrence of a Weekday in a given Year and Month:
Những điều kiện vừa nói có thể xử lý bằng một hàm IF() cơ bạn. Và đây là công thức chung để tính lần xuất hiện đầu tiên của một Ngày Trong Tuần (thứ) trong một năm, một tháng đã biết:
=DATE(Year, Month, 1) + IF(Weekday < WEEKDAY(DATE(Year, Month, 1)), 7 - WEEKDAY(DATE(Year, Month, 1)) + Weekday, Weekday - WEEKDAY(DATE(Year, Month, 1)))
Here’s the formula for calculating the date of Labor Day in 2007:
Đây là công thức để tính ngày Lễ Lao Động năm 2007:
=DATE(2007, 9, 1) + IF(2 < WEEKDAY(DATE(2007, 9, 1)), 7 - WEEKDAY(DATE(2007, 9, 1)) + 2, 2 - WEEKDAY(DATE(2007, 9, 1)))
Generalizing this formula for the nth occurrence of a weekday is straightforward: The second occurrence comes one week after the first, the third occurrence comes two weeks after the first, and so on. Here’s a generic expression to calculate the extra number of days to add (where n is an integer that represents the nth occurrence):
Khái quát lại công thức trên cho lần xuất hiện thứ n của một ngày trong tuần (thứ) thì đơn giản thôi: Lần xuất hiện thứ hai sẽ đến sau lần xuất hiện thứ nhất một tuần lễ, lần xuất hiện thứ ba sẽ đến sau lần xuất hiện thứ nhất hai tuần lễ, v.v... Và đây là biểu thức chung để tính thêm số ngày cần cộng thêm: (với n là một số nguyên, tượng trưng cho lần xuất hiện thứ n)
(n - 1) * 7
Here, then, in generic form, is the final formula for calculating the nth occurrence of a Weekday in a given Year and Month:
Và đây là dạng chung của công thức tính lần xuất hiện thứ n của một Ngày Trong Tuần (thứ) với năm, tháng đã biết:
=DATE(Year, Month, 1) + IF(Weekday < WEEKDAY(DATE(Year, Month, 1)), 7 - WEEKDAY(DATE(Year, Month, 1)) + Weekday, Weekday - WEEKDAY(DATE(Year, Month, 1))) + (n - 1) * 7
For example, the following formula calculates the date of the third Sunday (WEEKDAY() equals 1) in June for 2008:
Ví dụ, công thức sau đây tính ngày Chủ Nhật (WEEKDAY() = 1) thứ ba trong tháng Sáu năm 2008:
=DATE(2008, 6, 1) + IF(1 < WEEKDAY(DATE(2008, 6, 1)), 7 - WEEKDAY(DATE(2008, 6, 1)) + 1, 1 - WEEKDAY(DATE(2008, 6, 1))) + (3 - 1) * 7
Figure 10.2 shows a worksheet used for calculating the nth occurrence of a weekday:
Hình 10.2 minh họa một bảng tính sử dụng để tính lần xuất hiện thứ n của một ngày trong tuần (thứ):
The input cells are as follows:
Chú thích các ô nhập liệu:
- B1 — The number of the occurrence
Con số cho biết lần xuất hiện thứ mấy trong tháng
- B2 — The number of the weekday (the formula in C2 shows the name of the entered weekday)
Con số đại diện cho ngày trong tuần (công thức ở C2 hiển thị tên của ngày trong tuần này)
- B3 — The number of the month (the formula in C3 shows the name of the entered month)
Số chỉ tháng (công thức trong C3 hiển thị tên của tháng này)
- B4 — The year
Năm (cần tính)
The date calculation appears in cell B6. Here’s the formula:
Ngày cần tính toán nằm ở ô B6. Đây là công thức trong ô đó:
=DATE(B4, B3, 1) + IF(B2 < WEEKDAY(DATE(B4, B3, 1)), 7 - WEEKDAY(DATE(B4, B3, 1)) + B2, B2 - WEEKDAY(DATE(B4, B3, 1))) + (B1 - 1) * 7
) as a time separator.
