Formulas & Functions Excel 2007 - 10. Working with Date and Time Functions

BNTT

Bùi Nguyễn Triệu Tường
Thành viên danh dự
Tham gia ngày
3 Tháng bảy 2007
Bài viết
4,947
Được thích
23,120
Điểm
1,860
Tuổi
50
Nơi ở
Dalat
Phỏng dịch từ cuốn Formulas and Functions with Microsoft Office Excel 2007 của Paul McFedries

Part II: HARNESSING THE POWER OF FUNCTIONS - Tận dụng sức mạnh của các hàm
---------------------------------------------------------------------------------------



PART II - HARNESSING THE POWER OF FUNCTIONS
Phần II - Tận dụng sức mạnh của các hàm



Chapter 10 - WORKING WITH DATE AND TIME FUNCTIONS

Chương 10 - Làm việc với các hàm ngày tháng và thời gian
The date and time functions enable you to convert dates and times to serial numbers and perform operations on those numbers. This capability is useful for such things as accounts receivable aging, project scheduling, time-management applications, and much more. This chapter introduces you to Excel’s date and time functions and puts them through their paces with many practical examples.
Các hàm ngày tháng và thời gian cho phép bạn chuyển đổi ngày tháng và thời gian thành những chuỗi số liên tục và thực hiện những thao tác trên những con số đó. Khả năng này hữu dụng cho những vấn đề như các khoản phải thu quá hạn, lập thời biểu cho một dự án, những ứng dụng có quản lý thời gian, và nhiều điều khác nữa. Chương này giới thiệu với các bạn những hàm ngày tháng và thời gian của Excel và áp dụng chúng trong nhiều ví dụ thực tiễn.


You can download the workbook that contains this chapter’s examples here:
Bạn có thể tải về bảng tính với những ví dụ trong chương này tại đây:
Trong chương này, do hầu hết các hàm tôi đã trình bày chi tiết ở topic: Các hàm ngày tháng và thời gian, nên tôi sẽ không trình bày lại cú pháp và chú giải các đối số của hàm nữa (không theo như nguyên bản cuốn sách này). Trong các bài dịch sau đây, khi nói đến một hàm nào, tôi sẽ tạo liên kết (link) đến bài viết về hàm đó. Nếu muốn tìm hiểu kỹ hơn về cú pháp và cách sử dụng các đối số (argument), các bạn theo những liên kết này để xem.
 
Lần chỉnh sửa cuối:

BNTT

Bùi Nguyễn Triệu Tường
Thành viên danh dự
Tham gia ngày
3 Tháng bảy 2007
Bài viết
4,947
Được thích
23,120
Điểm
1,860
Tuổi
50
Nơi ở
Dalat
Chapter 10 - WORKING WITH DATE AND TIME FUNCTIONS


10.1. How Excel Deals with Dates and Times
Excel xử lý các ngày tháng và thời gian như thế nào

Excel uses serial numbers to represent specific dates and times. To get a date serial number, Excel uses December 31, 1899, as an arbitrary starting point and then counts the number of days that have passed since then. For example, the date serial number for January 1, 1900, is 1; for January 2, 1900, is 2; and so on. Table 10.1 displays some example date serial numbers.
Excel dùng những chuỗi số liên tục để tượng trưng cho các ngày tháng và thời gian cụ thể. Để có được một chuỗi số liên tục cho ngày tháng, Excel dùng ngày 31 tháng 12 năm 1899 như là điểm khởi đầu và rồi đếm số ngày đã trôi qua từ đó. Ví dụ, con số theo thứ tự ngày tháng của ngày 1 tháng 1 năm 1900 là 1, của ngày 2 tháng 1 năm 1900 là 2, và cứ thế... Bảng 10.1 trình bày một số ví dụ về các chuỗi số liên tục cho ngày tháng.

To get a time serial number, Excel expresses time as a decimal fraction of the 24-hour day to get a number between 0 and 1. The starting point, midnight, is given the value 0, so noon — halfway through the day — has a serial number of 0.5. Table 10.2 displays some example time serial numbers.
Để có chuỗi số liên tục cho thời gian, Excel biểu diễn thời gian như dưới dạng một phân số thập phân của 24 giờ một ngày để có một con số giữa 0 và 1. Điểm bắt đầu là nửa đêm, được gán giá trị là 0, do dó lúc giữa trưa — nửa ngày — có một con số (theo chuỗi liên tục) là 0.5. Bảng 10.2 trình bày một số ví dụ về các chuỗi số liên tục cho thời gian.

You can combine the two types of serial numbers. For example, 39447.5 represents noon on December 31, 2007.
Bạn có thể kết hợp hai loại số này. Ví dụ, 39447.5 tượng trưng cho 12 giờ trưa ngày Chủ Nhật 31/12/2007.

The advantage of using serial numbers in this way is that it makes calculations involving dates and times very easy. A date or time is really just a number, so any mathematical operation you can perform on a number can also be performed on a date. This is invaluable for worksheets that track delivery times, monitor accounts receivable or accounts payable aging, calculate invoice discount dates, and so on.
Ưu điểm của việc sử dụng các chuỗi số liên tục theo cách này là nó giúp cho các phép tính đòi hỏi các ngày tháng và thời gian được dễ dàng hơn. Một ngày tháng hoặc thời gian thật sự chỉ là một con số, cho nên bấy kỳ phép toán nào mà bạn có thể thực hiện được trên một con số thì bạn cũng có thể thực hiện được với một ngày tháng. Điều này hữu dụng cho các bảng tính dùng để theo dõi các thời gian gửi hàng, giám sát các khoản phải thu hoặc các khoản phải trả quá hạn, tính các ngày chiết khấu hóa đơn, v.v...
 
Lần chỉnh sửa cuối:

BNTT

Bùi Nguyễn Triệu Tường
Thành viên danh dự
Tham gia ngày
3 Tháng bảy 2007
Bài viết
4,947
Được thích
23,120
Điểm
1,860
Tuổi
50
Nơi ở
Dalat
10.1. How Excel Deals with Dates and Times


10.1.1. Entering Dates and Times
Nhập các ngày tháng và thời gian

Although it’s true that the serial numbers make it easier for the computer to manipulate dates and times, it’s not the best format for humans to comprehend. For example, the number 25,404.95555 is meaningless, but the moment it represents (July 20, 1969, at 10:56 p.m.) is one of the great moments in history (the Apollo 11 moon landing). Fortunately, Excel takes care of the conversion between these formats so that you never have to worry about it. To enter a date or time, use any of the formats outlined in Table 10.3.
Cho dù sự thật là các chuỗi số liên tục giúp cho máy tính dễ xử lý các ngày tháng và thời gian hơn, nhưng nó không phải là loại định dạng tốt nhất mà mọi người có thể hiểu được. Ví dụ, con số 25,404.95555 thì vô nghĩa, nhưng thời điểm mà nó biểu diễn (10:56 p.m. ngày 20/7/1969) lại là một trong những thời điểm vĩ đại nhất của lịch sử (tàu Apllo đổ bộ xuống mặt trăng). May thay, Excel tự xử lý sự chuyển đổi giữa những định dạng này để bạn không phải bận tâm về nó. Để nhập một ngày tháng hoặc thời gian, bạn có thể sử dụng bất kỳ loại định dạng nào được trình bày trong bảng 10.3.

Here are a couple of shortcuts that will let you enter dates and times quickly. To enter the current date in a cell, press Ctrl+; (semicolon). To enter the current time, press Ctrl+: (colon).
Có hai bộ phím tắt giúp bạn nhập nhanh các ngày tháng và thời gian. Để nhập ngày tháng năm hiện hành vào trong một ô, nhấn
Ctrl+; (dấu chấm phẩy). Để nhập thời gian hiện hành, nhấn Ctrl+: (dấu hai chấm).

Table 10.3 represents Excel’s built-in formats, but these are not set in stone. You’re free to mix and match these formats, as long as you observe the following rules:
Bảng 10.3 trình bày các kiểu định dạng có sẵn của Excel, nhưng chúng không phải là những thiết lập cố định. Bạn tự do trộn và phối hợp những kiểu định dạng này, miễn là bạn tuân theo những quy tắc sau đây:
  • You can use either the forward slash (/) or the hyphen (-) as a date separator. Always use a colon :)) as a time separator.
    Bạn có thể sử dụng dấu gạch chéo (/) hoặc dấu nối (-) làm dấu phân cách ngày tháng. Nhưng luôn luôn sử dụng dấu hai chấm :)) để phân cách thời gian.

  • You can combine any date and time format, as long as you separate them with a space.
    Bạn có thể kết hợp bất kỳ kiểu định dạng ngày tháng và thời gian nào lại với nhau, miễn là bạn tách biệt chúng bằng một dấu cách (một khoảng trắng).

  • You can enter date and time values using either uppercase or lowercase letters. Excel automatically adjusts the capitalization to its standard format.
    Bạn có thể nhập các giá trị ngày tháng và thời gian bẳng chữ hoa hoặc chữ thường. Excel sẽ tự động điều chỉnh việc viết hoa theo định dạng chuẩn của nó.

  • To display times using the 12-hour clock, include either am (or just a) or pm (or just p). If you leave these off, Excel uses the 24-hour clock.
    Để hiển thị thời gian theo kiểu 12 giờ, bạn phải bao gồm chữ am (hay là a) hoặc pm (hay là p). Nếu bạn bỏ qua những ký hiệu này, Excel sẽ sử dụng loại thời gian 24 giờ.
For more information on formatting dates and times, see “Formatting Numbers, Dates, and Times”
Xem thêm các kiểu định dạng ngày tháng và thời gian ở bải: "Định dạng số, ngày tháng và thời gian"
 

BNTT

Bùi Nguyễn Triệu Tường
Thành viên danh dự
Tham gia ngày
3 Tháng bảy 2007
Bài viết
4,947
Được thích
23,120
Điểm
1,860
Tuổi
50
Nơi ở
Dalat
10.1. How Excel Deals with Dates and Times


10.1.2. Excel and Two-Digit Years
Excel và các năm với 2 chữ số

Entering two-digit years (such as 07 for 2007 and 99 for 1999) is problematic in Excel because various versions of the program treat them differently. In versions since Excel 97, the two-digit years 00 through 29 are interpreted as the years 2000 through 2029, whereas 30 through 99 are interpreted as the years 1930 through 1999. Earlier versions treated the two-digit years 00 through 19 as 2000 through 2019, and 20 through 99 as 1920 through 1999.
Việc nhập các năm với hai chữ số (chẳng hạn như 07 cho 2007 và 99 cho 1999) là một vấn đề trong Excel, bởi vì các phiên bản khác nhau của chương trình sẽ xử lý chúng mỗi cách khác nhau. Trong các phiên bản kể từ Excel 97, các năm với hai chữ số từ 00 đến 29 được hiểu là các năm từ 2000 đến 2029, trng khi 30 đến 99 được hiểu là các năm từ 1930 đến 1999. Còn các phiên bản trước đây, lại xem các năm có hai chữ số từ 00 đến 19 là 2000 đến 2019, và 20 đến 99 là 1920 đến 1999.

Two problems arise here: One is that using a two-digit year such as 25 will cause havoc if the worksheet will ever be loaded into Excel 95 or some earlier version. The second is that you could throw a monkey wrench into your calculations by using a date such as 8/23/30 to mean August 23, 2030, because Excel treats it as August 23, 1930!
Hai vấn đề nảy sinh ở đây: Một là sử dụng một năm với hai chữ số, chẳng hạn như 25, sẽ gây thiệt hại nếu như bảng tính sẽ được mở bằng Excel 95 trở về trước. Thứ hai là bạn có thể làm sai lệch các phép tính bằng việc sử dụng một ngày tháng như là 8/23/30 để có nghĩa là August 23, 2030, bởi vì Excel sẽ xem nó là August 23, 1930!

The easiest solution to both problems is to always use four-digit years to avoid ambiguity. Alternatively, you can put off the second problem by changing how Excel and Windows interpret two-digit years. Here are the steps to following in Windows Vista (Windows 98 and later have similar options):
Giải pháp dễ nhất cho cả hai vấn đề này là luôn luôn sử dụng các năm có bốn chữ số để tránh sự mơ hồ. Hoặc, bạn có thể tránh vấn đề thứ hai bằng việc thay đổi cách Excel và Windows hiểu các năm có hai chữ số. Đây là các bước để làm trong Windows Vista (Windows 98 trở về sau cũng có những tùy chọn tương tự):
  1. Choose Start, Control Panel, and then click the Clock, Language, and Region link.
    Chọn Start, Control Panel, và nhấp vào Clock, Language, và liên kết Region.

  2. Click the Change the Date, Time, or Number Format link. The Regional and Language Options dialog box appears.
    Nhấn chọn liên kết Change the Date, Time, or Number Format. Hộp thoại Regional and Language Options xuất hiện.

  3. In the Formats tab, click Customize This Format. The Customize Regional Options dialog box appears.
    Trong tab Formats, nhấn Customize This Format. Hộp thoại Customize Regional Options xuất hiện.

  4. Select the Date tab.
    Chọn tab Date.

  5. Use the When a Two-Digit Year Is Entered, Interpret It As a Year Between spinner to adjust the maximum year in which a two-digit year is interpreted as a 21st-century date. For example, if you never use dates prior to 1960, you could change the spin box value to 2059, which means Excel interprets two-digits years as dates between 1960 and 2059 (see Figure 10.1).
    Dùng cái spinner When a Two-Digit Year Is Entered, Interpret It As a Year Between để điều chỉnh số năm tối đa mà một năm với hai chữ số được hiểu là một năm của thế kỷ 21. Ví dụ, nếu bạn không bao giờ dùng những ngày tháng trước 1960, bạn có thể thay đổi giá trị trong hộp spin thành 2059, có nghĩa là Excel hiểu các năm với hai chữ số là những ngày giữa 1960 vào 2059 (xem hình 10.1)

    Figure 10.1

  6. Click OK to return to the Regional and Language Options dialog box.
    Nhấn OK để quay trở về hộp thoại Regional and Language Options.

  7. Click OK to put the new setting into effect.
    Nhấn OK để cho những xác lập mới này có hiệu lực.
 

BNTT

Bùi Nguyễn Triệu Tường
Thành viên danh dự
Tham gia ngày
3 Tháng bảy 2007
Bài viết
4,947
Được thích
23,120
Điểm
1,860
Tuổi
50
Nơi ở
Dalat
Chapter 10 - WORKING WITH DATE AND TIME FUNCTIONS


10.2. Using Excel’s Date Functions
Sử dụng các hàm ngày tháng của Ecel

Excel’s date functions work with or return date serial numbers. All of Excel’s date-related functions are listed in Table 10.4. (For the serial_number arguments, you can use any valid Excel date.)
Các hàm ngày tháng của Excel làm việc với hoặc trả về các số thứ tự của ngày. Tất cả các hàm có liên quan đến ngày tháng của Excel được liệt kê ở bảng 10.4. (Bạn có thể sử dụng bất kỳ một ngày tháng hợp lệ nào trong đối số serial_number)


Table 10.4 - Excel’s Date Functions
Bảng 10.4 - Các hàm ngày tháng trong Excel

DATE (year, month, day) : Returns the serial number of a date, in which year is a number from 1900 to 2078, month is a number representing the month of the year, and day is a number representing the day of the month
Trả về số thứ tự của một ngày, trong đó year là một con số từ 1900 đến 2078, month là một con số đại diện cho tháng trong năm, và day là một con số đại diện cho ngày trong tháng.

DATEDIF (start_date, end_date [, unit]) : Returns the difference between start_date and end_date, based on the specified unit
Trả về sự khác nhau (sự chênh lệch) giữa start_dateend_date, dựa theo sự chỉ định ở unit

DATEVALUEhttp://www.giaiphapexcel.com/forum/showpost.php?p=50280&postcount=5 (date_text) : Converts a date from date_text to a serial number
Chuyển đổi một ngày từ dạng date_text ra một số thứ tự

DAYhttp://www.giaiphapexcel.com/forum/showpost.php?p=50280&postcount=5 (serial_number) : Extracts the day component from the date given by serial_number
Trích ra thành phần ngày từ một ngày được cho ở serial_number

DAYS360http://www.giaiphapexcel.com/forum/showpost.php?p=51138&postcount=15 (start_date, end_date [, method]) : Returns the number of days between start_date and end_date, based on a 360-day year
Trả về số ngày giữa start_dateend_date, dựa trên cơ sở một năm có 360 ngày

EDATEhttp://www.giaiphapexcel.com/forum/showpost.php?p=50892&postcount=9 (start_date, months) : Returns the serial number of a date that is the specified number of months before or after start_date
Trả về số thứ tự của một ngày trước hoặc sau ngày start_date một số tháng đã được chỉ định ở months

EOMONTHhttp://www.giaiphapexcel.com/forum/showpost.php?p=50908&postcount=10 (start_date, months) : Returns the serial number of the last day of the month that is the specified number of months before or after start_date
Trả về số thứ tự của ngày cuối cùng của một tháng, trước hoặc sau ngày start_date một số tháng đã được chỉ định ở months

MONTHhttp://www.giaiphapexcel.com/forum/showpost.php?p=50280&postcount=5 (serial_number) : Extracts the month component from the date given by serial_number (January = 1)
Trả về thành phần tháng từ một ngày được cho ở serial_number (January = 1)

NETWORKDAYShttp://www.giaiphapexcel.com/forum/showpost.php?p=51133&postcount=14 (start_date, end_date [, holidays]) : Returns the number of working days between start_date and end_date; doesn’t include weekends and any dates specified by holidays
Trả về số ngày làm việc trong một khoảng thời gian giữa start_dateend_date, không tính các ngày cuối tuần và các ngày được chỉ định ở holidays

TODAY () : Returns the serial number of the current date
Trả về số thứ tự của ngày tháng hiện tại

WEEKDAY (serial_number) : Converts a serial number to a day of the week (Sunday = 1)
Chuyển đổi serial_number thành một ngày trong tuần (Sunday = 1)

WEEKNUM (serial_number [, return_type]) : Returns a number that corresponds to where the week that includes serial_number falls numerically during the year
Trả về một số cho biết tuần lễ đang chứa ngày serial_number là tuần lễ thứ mấy trong năm

WORKDAY (start_day, days [, holidays]) : Returns the serial number of the day that is days working days from start_date; week ends and holidays are excluded (this is an Analysis ToolPak function)
Trả về một số thứ tự của một ngày làm việc tính từ ngày start_date; không bao gồm những ngày cuối tuần và ngày nghỉ (đây là một hàm Analysis ToolPak)

YEARhttp://www.giaiphapexcel.com/forum/showpost.php?p=50280&postcount=5 (serial_number) : Extracts the year component from the date given by serial_number
Trả về thành phần năm của ngày được cho ở serial_number

YEARFRAChttp://www.giaiphapexcel.com/forum/showpost.php?p=51145&postcount=16 (start_date, end_date, basis) : Converts the number of days between start_date and end_date into a fraction of a year
Chuyển đổi số ngày giữa start_dateend_date thành một tỷ lệ của một năm

 
Lần chỉnh sửa cuối:

BNTT

Bùi Nguyễn Triệu Tường
Thành viên danh dự
Tham gia ngày
3 Tháng bảy 2007
Bài viết
4,947
Được thích
23,120
Điểm
1,860
Tuổi
50
Nơi ở
Dalat
10.2. Using Excel’s Date Functions


10.2.1. Returning a Date
Trả về một ngày

If you need a date for an expression operand or a function argument, you can always enter it by hand if you have a specific date in mind. Much of the time, however, you need more flexibility, such as always entering the current date or building a date from day, month, and year components. Excel offers three functions that can help: TODAY(), DATE(), and DATEVALUE().
Nếu bạn cần sử dụng một ngày tháng như một toán hạng trong một biểu thức hay như một đối số của một hàm, bạn luôn luôn có thể nhập nó bằng tay nếu như bạn đã có một ngày tháng cụ thể trong đầu. Tuy nhiên, đa phần là bạn sẽ cần thêm sự linh hoạt, chẳng hạn như nhập ngày tháng hiện hành, hoặc tạo một ngày tháng từ những thành phần ngày, tháng, năm. Excel cung cấp cho bạn ba hàm hữu dụng sau đây: TODAY(), DATE(), và DATEVALUE().



  • TODAY(): Returning the Current Date
    Trả về ngày tháng hiện hành
When you need to use the current date in a formula, function, or expression, use the TODAY() function, which doesn’t take any arguments:
Khi bạn cần sử dụng ngày tháng hiện hành trong một công thức, một hàm, hay biểu thức, bạn sử dụng hàm TODAY(), là một hàm không cần đối số:

TODAY()

This function returns the serial number of the current date, with midnight as the assumed time. For example, if today’s date is December 31, 2007, the TODAY() function returns the following serial number:
Hàm này trả về số thứ tự của ngày tháng hiện hành, với thời gian được giả định là nửa đêm (0 giờ). Ví dụ, nếu hôm nay là ngày 31/12/2007, hàm TODAY() trả về số thứ tự sau:

39447.0

Note that TODAY() is a dynamic function that doesn’t always return the same value. Each time you edit the formula, enter another formula, recalculate the worksheet, or reopen the workbook, TODAY() updates its value to return the current system date.
Lưu ý rằng TODAY() là một hàm động, không phải lúc nào nó cũng cho ra một kết quả giống nhau. Mỗi lần bạn sửa lại công thức, nhập một công thức khác, tính toán lại bảng tính, hoặc mở lại bảng tính, TODAY() cập nhật giá tri của nó và trả về ngày hiện hành của hệ thống.



  • DATE(): Returning Any Date
    Trả về bất kỳ một ngày nào
A date consists of three components: the year, month, and day. It often happens that a worksheet generates one or more of these components, and you need some way of building a proper date out of them. You can do that by using Excel’s DATE() function:
Một ngày gồm có ba thành phần: năm, tháng, và ngày. Điều thường hay gặp là một bảng tính phát sinh ra một hay nhiều thành phần trong những thành phần này, và việc của bạn là tạo ra một ngày thích hợp từ những thành phần đó. Bạn có thể làm điều này bằng cách sử dụng hàm DATE() của Excel:

DATE (year, month, day)
CAUTION: Excel’s date inconsistencies rear up again with the DATE() function. That’s because, if you enter a two-digit year (or even a three-digit year), Excel converts the number into a year value by adding 1900. So, entering 5 as the year argument gives you 1905, not 2008. To avoid problems, always use a four-digit year when entering the DATE() function’s year argument.
Cẩn thận: Sự mâu thuẫn trong ngày tháng của Excel lại tăng thêm với hàm DATE(). Bởi vì, nếu bạn nhập một năm với hai chữ số (thậm chí là ba chữ số), Excel chuyển đổi con số này thành một giá trị năm bằng cách cộng thêm 1900 vào nó. Do đó, nhập con số 5 ở đối số year sẽ cho ra 1905 chứ không phải là 2005. Để tránh vấn đề này, bạn hãy luôn luôn nhập bốn chữ số trong đối số year của hàm DATE().
For example, the following expression returns the serial number of Christmas Day in 2007:
Ví dụ, biểu thức sau đây trả về số thứ tự của ngày Giáng Sinh năm 2007:

DATE(2007, 12, 25)

Note, too, that DATE() adjusts for wrong month and day values. For example, the following expression returns the serial number of January 1, 2008:
Lưu ý thêm, DATE() tự động điều chỉnh các giá trị ngày và tháng không hợp lệ. Ví dụ, biểu thức sau trả về số thứ tự của ngày 01/01/2008:

DATE(2007, 12, 32)

Here, DATE() adds the extra day (there are 31 days in December) to return the date of the next day. Similarly, the following expression returns January 25, 2008:
Ở đây, DATE() bổ sung thêm ngày (có 31 ngày trong tháng 12) để trả về ngày kế tiếp. Tương tự, biểu thức sau đây trả về ngày 25/01/2008:
DATE(2007, 13, 25)



  • DATEVALUE(): Converting a String to a Date
    Chuyển đổi một chuỗi thành một ngày tháng
If you have a date value in string form, you can convert it to a date serial number by using the DATEVALUE() function:
Nếu bạn có một giá trị ngày tháng dưới dạng một chuỗi, bạn có thể chuyển đổi nó thành một ngày có số thứ tự liên tục với hàm DATEVALUE():

DATEVALUE (date_text)

For example, the following expression returns the date serial number for the string August 23, 2007:
Ví dụ, biểu thức sau đây trả về thứ tự ngày liên tục của chuỗi August 23, 2007

DATEVALUE("August 23, 2007")

To learn how to convert nonstandard date strings to dates, see "A Date-Conversion Formula"
Để học cách chuyển đổi những chuỗi ngày tháng không theo tiêu chuẩn, xem bài: "Một công thức chuyển đổi ngày tháng"
 
Lần chỉnh sửa cuối:

BNTT

Bùi Nguyễn Triệu Tường
Thành viên danh dự
Tham gia ngày
3 Tháng bảy 2007
Bài viết
4,947
Được thích
23,120
Điểm
1,860
Tuổi
50
Nơi ở
Dalat
10.2. Using Excel’s Date Functions


10.2.2. Returning Parts of a Date
Trả về các thành phần của một ngày tháng

The three components of a date — year, month, and day — can also be extracted individually from a given date. This might not seem all that interesting at first, but actually many useful techniques arise out of working with a date’s component parts. A date’s components are extracted using Excel’s YEAR(), MONTH(), and DAY() functions.
Ba thành phần của một ngày tháng — năm, tháng và ngày — cũng có thể được trích xuất riêng lẻ từ một ngày đã cho. Thoạt nhìn vào thì những điều này dường như không có gì thú vị, nhưng thật sự là có nhiều vấn đề hữu dụng nảy sinh khi làm việc với từng thành phần của một ngày tháng. Các thành phần của một ngày tháng được trích xuất ra bởi các hàm YEAR(), MONTH() và DAY() của Excel.



The YEAR() function returns a four-digit number that corresponds to the year component of a specified date:
Hàm YEAR() trả về một con số có bốn chữ số tương ứng với thành phần năm của một ngày tháng đã xác định:

YEAR (serial_number)

For example, if today is August 23, 2007, the following expression will return 2007:
Ví dụ, nếu hôm nay là ngày 23/8/2007, biểu thức sau đây sẽ trả về 2007:

YEAR(TODAY())



The MONTH() function returns a number between 1 and 12 that corresponds to the month component of a specified date:
Hàm MONTH() trả về một con số giữa 1 và 12 tương ứng với thành phần tháng của một ngày tháng đã xác định:

MONTH (serial_number)

For example, the following expression returns 8:
Ví dụ, biểu thức sau đây sẽ trả về 8:

MONTH ("August 23, 2007")



The DAY() function returns a number between 1 and 31 that corresponds to the month component of a specified date:
Hàm DAY() trả về một con số giữa 1 và 31 tương ứng với thành phần ngày của một ngày tháng đã xác định:

DAY (serial_number)

For example, the following expression returns 23:
Ví dụ, biểu thức sau đây sẽ trả về 8:

DAY ("August 23, 2007")
 
Lần chỉnh sửa cuối:

BNTT

Bùi Nguyễn Triệu Tường
Thành viên danh dự
Tham gia ngày
3 Tháng bảy 2007
Bài viết
4,947
Được thích
23,120
Điểm
1,860
Tuổi
50
Nơi ở
Dalat
10.2.2. Returning Parts of a Date

The WEEKDAY() function returns a number that corresponds to the day of the week upon which a specified date falls.
Hàm WEEKDAY() trả về một con số, cho biết một ngày đã được xác định là ngày thứ mấy trong một tuần.

WEEKDAY (serial_number [, return_type])

For example, the following expression returns 5 because August 23, 2007, is a Thursday:
Ví dụ, biểu thức sau đây sẽ trả về 5 bởi vì ngày 23/8/2007 là một ngày thứ Năm:

WEEKDAY("8/23/2007")
To learn how to use CHOOSE() to convert the WEEKDAY() return value into a day name, see "Determining the Name of the Day of the Week"
Để học cách dùng hàm CHOOSE() chuyển đổi giá trị trả về của WEEKDAY() thành tên (thứ) của một ngày, xem bài "Xác định tên của ngày trong tuần"



The WEEKNUM() function returns a number that corresponds to where the week that includes a specified date falls numerically during the year.
Hàm WEEKNUM() trả về một con số, cho biết tuần lễ đang chứa một ngày đã được xác định là tuần lễ thứ mấy trong một năm.

WEEKNUM (serial_number [, return_type])

For example, the following expression returns 34 because August 23, 2007, falls in the 34th week of 2007::
Ví dụ, biểu thức sau đây sẽ trả về 34 bởi vì ngày 23/8/2007 rơi vào tuần lễ thứ 34 của năm 2007.

WEEKNUM ("August 23, 2007")
 
Lần chỉnh sửa cuối:

BNTT

Bùi Nguyễn Triệu Tường
Thành viên danh dự
Tham gia ngày
3 Tháng bảy 2007
Bài viết
4,947
Được thích
23,120
Điểm
1,860
Tuổi
50
Nơi ở
Dalat
10.2.2. Returning Parts of a Date

  • Returning a Date X Years, Months, or Days from Now
    Trả về một ngày nào đó tính từ bây giờ
You can take advantage of the fact that, as I mentioned earlier, DATE() automatically adjusts wrong month and day values by applying formulas to one or more of the DATE() function’s arguments. The most common use for this is returning a date that occurs X number of years, months, or days from now (or from any date).
For example, suppose that you want to know which day of the week the 4th of July falls on next year. Here’s a formula that figures it out:
Bạn có thể tận dụng việc mà tôi đã đề cập đến ở trên, là DATE() tự động điều chỉnh những giá trị tháng hoặc ngày nhập sai bằng cách áp dụng các công thức vào một hay nhiều đối số của hàm DATE(). Công dụng phổ biến nhất cho chuyện này là dùng để tính một ngày nào đó kể từ từ hôm nay (hoặc từ bất kỳ một ngày tháng nào). Ví dụ, giả sử bạn muốn biết ngày 4 tháng Bảy năm sau sẽ là ngày thứ mấy, đây là công thức để tính điều này:

=WEEKDAY(DATE(YEAR(TODAY)) + 1, 7, 4)

As another example, if you want to work with whatever date it is six months from now, you would use the following expression:
Một ví dụ khác, bạn muốn biết 6 tháng nữa tính từ hôm nay sẽ là ngày bao nhiêu, bạn dùng biểu thức sau:

DATE(YEAR(TODAY()), MONTH(TODAY()) + 6, DAY(TODAY()))

Given this technique, you’ve probably figured out that you can return a date that is X days from now (or whenever) by adding to the day component of the DATE() function. For example, here’s an expression that returns a date 30 days from now:
Với kỹ thuật trên, có lẽ bạn đã biết cách để biết được một ngày nào đó tính từ bây giờ (hoặc bất kỳ khi nào), là thêm vào thành phần ngày tháng của hàm DATE(). Ví dụ, đây là một biểu thức cho biết 30 ngày nữa tính từ hôm nay là ngày nào:

DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY() + 30))

This is overkill, however, because date addition and subtraction works at the day level in Excel. That is, if you simply add or subtract a number to or from a date, Excel adds or subtracts that number of days. For example, to return a date 30 days from now, you need only use the following expression:
Tuy nhiên, điều này là quá mức cần thiết, bởi vì việc cộng hay trừ vào một ngày tháng là việc của Excel. Nghĩa là, nếu bạn thêm vào hoặc bớt đi một con số của một ngày, Excel sẽ cộng hay trừ số ngày đó. Ví dụ, để biết 30 ngày nữa tính từ hôm nay là ngày nào, bạn chỉ cẩn dùng biểu thức sau:

TODAY() + 30
 
Lần chỉnh sửa cuối:

BNTT

Bùi Nguyễn Triệu Tường
Thành viên danh dự
Tham gia ngày
3 Tháng bảy 2007
Bài viết
4,947
Được thích
23,120
Điểm
1,860
Tuổi
50
Nơi ở
Dalat
10.2.2. Returning Parts of a Date

  • A Workday Alternative: The WORKDAY Function
    Tính ngày kết thúc một công việc — Hàm WORKDAY
Adding days to or subtracting days from a date is straightforward, but the basic calculation includes all days: workdays, weekends, and holidays. In many cases, you might need to ignore weekends and holidays and return a date that is a specified number of workdays from some original date.
Cộng hay trừ thêm ngày vào một ngày thì đơn giản, nhưng phép tính cơ bản này bao gồm tất cả các ngày: ngày làm việc, ngày cuối tuần, và ngày nghỉ. Trong nhiều trường hợp, bạn có thể cần phải bỏ qua các ngày cuối tuần và ngày nghỉ, và trả về một ngày là ngày làm việc, cộng thêm một số ngày đã xác định, tính từ một ngày nào đó.

You can do this by using the Analysis ToolPak’s WORKDAY() function, which returns a date that is a specified number of working days from some starting date.
Bạn có thể làm điều này bằng cách sử dụng hàm WORKDAY() của bộ Add-in Analysis ToolPak, là hàm trả về một ngày tháng sau khi cộng thêm một số ngày làm việc đã được xác định, tính từ một ngày khởi đầu nào đó. Nói cách khác, có thể dùng hàm này để biết ngày hoàn thành một công việc, biết ngày khởi đầu và số ngày sẽ làm.

WORKDAY (start_date, days [, holidays])

For example, the following expression returns a date that is 30 workdays from today:
Ví dụ, biểu thức sau đây trả về một ngày sau 30 ngày làm việc tính từ hôm nay:

WORKDAY(TODAY(), 30)

Here’s another expression that returns the date that is 30 workdays from December 1, 2007, excluding December 25, 2007, and January 1, 2008:
Đây là một biểu thức khác trả về một ngày sau 30 ngày làm việc, tính từ ngày 01/12/2007, không tính ngày 25/12/2007 và ngày 01/01/2008:

=WORKDAY("1/12/2007", 30, {"25/12/2007", "1/1/2008"})
 
Lần chỉnh sửa cuối:

BNTT

Bùi Nguyễn Triệu Tường
Thành viên danh dự
Tham gia ngày
3 Tháng bảy 2007
Bài viết
4,947
Được thích
23,120
Điểm
1,860
Tuổi
50
Nơi ở
Dalat
10.2.2. Returning Parts of a Date

  • Adding X Months: A Problem
    Vấn đề cộng thêm tháng vào một ngày tháng
You should be aware that simply adding X months to a specified date’s month component won’t always return the result you expect. The problem is that the months have a varying number of days. So, if you add a certain number of months to a date that falls on or near the end of a month, the future month might not have the same number of days. Excel adjusts the day component accordingly.
Bạn nên lưu ý rằng việc cộng thêm một số tháng vào thành phần tháng của một ngày tháng đã xác định sẽ không luôn trả về kết quả mà bạn mong đợi. Vấn đề là các tháng có số ngày không bằng nhau. Do đó, nếu bạn cộng một số tháng nhất định vào một ngày tháng là những ngày cuối tháng, thì tháng tương lai có thể không có cùng số ngày (với tháng trước khi cộng thêm), do Excel sẽ điều chỉnh thành phần ngày một cách phù hợp.

For example, suppose that A1 contains the date 31/01/2008, and consider the following formula:
Ví dụ, giả sử rằng ô A1 đang chứa ngày 31/01/2008, và xem xét công thức sau:

=DATE(YEAR(A1), MONTH(A1) + 3, DAY(A1))

You might expect this formula to return the last date in April as the result. Unfortunately, adding three months returns the wrong date 4/31/2008 (there are only 30 days in April), which Excel automatically converts to 5/1/2008.
Có thể bạn muốn rằng công thức này trả về ngày cuối cùng của tháng Tư ở kết quả của nó. Thật không may, việc cộng thêm ba tháng sẽ trả về một ngày không đúng: 31/4/2008 (tháng Tư chỉ có 30 ngày), và Excel tự động chuyển đổi thành ngày 01/5/2008.

You can avoid this problem by using two functions: EDATE() and EOMONTH().
Bạn có thể tránh vấn đề này bẳng cách sử dụng hai hàm: EDATE() và EOMONTH().
 
Lần chỉnh sửa cuối:

BNTT

Bùi Nguyễn Triệu Tường
Thành viên danh dự
Tham gia ngày
3 Tháng bảy 2007
Bài viết
4,947
Được thích
23,120
Điểm
1,860
Tuổi
50
Nơi ở
Dalat
10.2.2. Returning Parts of a Date

The EDATE() function returns a date that is the specified number of months before or after a starting date:
Hàm EDATE() trả về một ngày trước hoặc sau ngày đã cho một số tháng xác định

EDATE (start_date, months)

The nice thing about the EDATE() function is that it performs a “smart” calculation when working with dates at or near the end of the month: If the day component of the returned date doesn’t exist (for example, April 31), EDATE() returns the last day of the month (April 30).
Một điều thú vị về hàm EDATE() là nó thực hiện một phép tính "thông minh" khi làm việc với các ngày cuối hoặc gần cuối tháng: Nếu thành phần ngày của ngày tháng trả về không tồn tại (ví dụ, ngày 31 tháng Tư), EDATE() trả về ngày cuối của tháng đó (ngày 30 tháng Tư).

The EDATE() function is useful for calculating the coupon payment dates for bond issues. Given the bond’s maturity date, you first calculate the bond’s first payment as follows (assuming that the bond was issued this year and that the maturity date is in a cell named MaturityDate):
Hàm EDATE() hữu dụng với việc tính các ngày thanh toán phiếu (coupon) cho các lần phát hành trái phiếu. Với ngày đáo hạn đã biết, trước hết bạn tính khoản phải thanh toán lần đầu của trái phiếu đó như sau: (giả sử rằng trái phiếu đã được phát hành trong năm nay, và ngày đáo hạn nằm trong một ô có tên là MaturityDate):

=DATE(YEAR(TODAY()), MONTH(MaturityDate), DAY(MaturityDate))

If this result is in cell A1, the following formula will return the date of the next coupon payment:
Nếu kết quả này nằm trong ô A1, công thức sau đây sẽ trả về ngày thanh toán phiếu (coupon) kế tiếp:

=EDATE(A1, 6)



The EOMONTH() function returns the date of the last day of the month that is the specified number of months before or after a starting date:
Hàm EOMONTH() trả về ngày cuối cùng của một tháng trước hoặc sau ngày tháng đã cho một số tháng xác định.

EOMONTH (start_date, months)

For example, the following formula returns the last day of the month three months from now:
Ví dụ, công thức sau đây sẽ trả về ngày cuối của tháng sau ngày hôm nay 3 tháng:

=EOMONTH(TODAY(), 3)
 
Lần chỉnh sửa cuối:

BNTT

Bùi Nguyễn Triệu Tường
Thành viên danh dự
Tham gia ngày
3 Tháng bảy 2007
Bài viết
4,947
Được thích
23,120
Điểm
1,860
Tuổi
50
Nơi ở
Dalat
10.2.2. Returning Parts of a Date

  • Returning the Last Day of Any Month
    Trả về ngày cuối cùng của bất kỳ tháng nào
The EOMONTH() function returns the last date of some month in the future or the past. However, what if you have a date and you want to know the last day of the month in which that date appears? You can calculate this by using yet another trick involving the DATE() function’s capability to adjust wrong values for date components. You want a formula that returns the last day of a particular month. You can’t specify the day argument in the DATE() function directly because the months can have 28, 29, 30, or 31 days. Instead, you can take advantage of an apparently trivial fact: The last day of any month is always the day before the first day of the next month. The number before 1 is 0, so you can plug 0 into the DATE() function as the day argument:
Hàm EOMONTH() trả về ngày cuối cùng của một tháng nào đó trước hoặc sau một ngày tháng đã biết một số tháng nào đó. Tuy nhiên, điều gì xảy ra nếu bạn có một ngày tháng và muốn biết ngày cuối cùng của tháng đang xuất hiện trong ngày tháng này? Bạn có thể tự tính điều này bằng một thủ thuật: lợi dụng tính năng tự động điều chỉnh các giá trị sai của hàm DATE(). Bạn muốn một công thức trả về ngày cuối cùng của một tháng cụ thể, nhưng bạn không thể xác định đối số day trong hàm DATE() cách trực tiếp bởi vì các tháng có thể có 28, 29, 30 hoặc 31 ngày. Có một thực tế rõ ràng rất bình thường mà bạn có thể tận dụng: Ngày cuối cùng của một tháng luôn luôn là ngày trước ngày đầu tiên của tháng tiếp theo. Trước con số 1, là số 0, vậy bạn có thể đưa số 0 vào trong đối số của hàm DATE():

=DATE(YEAR(MyDate), MONTH(MyDate) + 1, 0)

Here, assume that MyDate is the date you want to work with.
Ở đây, giả sử rằng MyDate là ngày tháng mà bạn muốn tìm ngày cuối tháng của nó.

BNTT: Bài này chẳng qua chỉ là một cách để thực tập một trong những cách sử dụng của hàm DATE(). Chứ thật ra, chính bản thân hàm EOMONTH() vẫn có thể trả về ngày cuối tháng của một ngày tháng bất kỳ, bằng cách cho đối số months trong hàm bằng 0. Công thức sau đây sẽ trả về cùng kết quả với công thức ở trên: =EOMONTH(MyDate, 0)
 
Lần chỉnh sửa cuối:

BNTT

Bùi Nguyễn Triệu Tường
Thành viên danh dự
Tham gia ngày
3 Tháng bảy 2007
Bài viết
4,947
Được thích
23,120
Điểm
1,860
Tuổi
50
Nơi ở
Dalat
10.2.2. Returning Parts of a Date

  • Determining a Person’s Birthday Given the Birth Date
    Xác định Sinh Nhật của một người khi đã biết ngày tháng năm sinh
If you know a person’s birth date, determining that person’s birthday is easy: Just keep the month and day the same, and substitute the current year for the year of birth. To accomplish this in a formula, you could use the following:
Nếu bạn biết ngày tháng năm sinh của một người, thì việc xác định Sinh Nhật của người đó rất dễ dàng: Chỉ cần giữ lại tháng và ngày, và thay thế năm sinh bằng năm hiện hành. Để thực hiện điều này trong một công thức, bạn có thể làm như sau:

=DATE(YEAR(NOW()), MONTH(Birthdate), DAY(Birthdate))

Here, I’m assuming that the person’s date of birth is in a cell named BirthDate. The YEAR(NOW()) component extracts the current year, and MONTH(BirthDate) and DAY(BirthDate) extract the month and day, respectively, from the person’s date of birth. Combine these into the DATE() function, and you have the birthday.
Ở đây, tôi giả sử rằng ngày sinh của một người được đặt trong một ô có tên là BirthDay. Thành phần YEAR(NOW()) trích ra năm hiện hành, còn MONTH(BirthDate) và DAY(BirthDate) lần lượt trích ra tháng và ngày từ ngày sinh của người đó. Kết hợp những thành phần này trong hàm DATE(), bạn có được ngày Sinh Nhật.
 
Lần chỉnh sửa cuối:

BNTT

Bùi Nguyễn Triệu Tường
Thành viên danh dự
Tham gia ngày
3 Tháng bảy 2007
Bài viết
4,947
Được thích
23,120
Điểm
1,860
Tuổi
50
Nơi ở
Dalat
10.2.2. Returning Parts of a Date

  • Returning the Date of the Nth Occurrence of a Weekday in a Month
    Trả về ngày tháng của một ngày trong tuần thứ N của một tháng
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
 
Lần chỉnh sửa cuối:

BNTT

Bùi Nguyễn Triệu Tường
Thành viên danh dự
Tham gia ngày
3 Tháng bảy 2007
Bài viết
4,947
Được thích
23,120
Điểm
1,860
Tuổi
50
Nơi ở
Dalat
10.2.2. Returning Parts of a Date

  • Calculating Holiday Dates
    Tính toán những ngày nghỉ lễ
Given the formula from the previous section, it becomes a relative breeze to calculate the dates for most floating holidays (that is, holidays that occur on the nth weekday of a month instead of on a specific date each year, as do holidays such as Christmas, Independence Day, and Canada Day).
Với công thức của phần trước, việc tính ngày tháng cho hầu hết những ngày nghỉ lễ không cố định (nghĩa là các ngày nghỉ lễ xuất hiện vào một ngày trong tuần thứ n của một tháng, thay vì là một ngày riêng biệt trong mỗi năm như là ngày Noel, ngày Quốc Khánh, ngày Canada)

Here are the standard statutory floating holidays in the United States:
Sau đây là các ngày nghỉ lễ không cố định tại Mỹ:
  • Martin Luther King Jr. Day — Third Monday in January
    Ngày Martin Luther King Jr. — Thứ Hai của tuần thứ ba trong tháng Giêng

  • Presidents Day — Third Monday in February
    Ngày Tổng thống — Thứ Hai của tuần thứ ba trong tháng Hai

  • Memorial Day — Last Monday in May
    Ngày Liệt sĩ — Thứ Hai của tuần cuối cùng trong tháng Năm

  • Labor Day — First Monday in September
    Ngày Lao động — Thứ Hai của tuần đầu tiên trong tháng Chín

  • Columbus Day — Second Monday in October
    Ngày Columbus — Thứ Hai của tuần thứ hai trong tháng Mười

  • Thanksgiving Day — Fourth Thursday in November
    Lễ Tạ ơn — Thứ Năm của tuần thứ tư trong tháng Mười Một

Here’s the list for Canada:
Và đây là danh sách các ngày nghỉ lễ không cố định ở Canada:
  • Victoria Day — Monday on or before May 24
    Ngày Victoria — Thứ Hai ngay trước ngày 24 tháng Năm, hoặc là trùng với ngày này

  • Good Friday — Friday before Easter Sunday
    Ngày Thứ Sáu Thánh — Thứ Sáu trước Chủ Nhật Phục Sinh

  • Labor Day — First Monday in September
    Ngày Lao động — Thứ Hai của tuần đầu tiên trong tháng Chín

  • Thanksgiving Day — Second Monday in October
    Lễ Tạ ơn — Thứ Hai của tuần thứ hai trong tháng Mười

Figure 10.3 shows a worksheet used to calculate the holiday dates in a specified year:
Hình 10.3 minh họa một bảng tính sử dụng để tính những ngày nghỉ lễ của một năm đã xác định:


Column A holds the name of the holiday; column B holds the occurrence within the month or, for fixed holidays, the actual date within the month; column C holds the days of the week; and column D holds the number of the month.
Cột A chứa tên của các ngày nghỉ lễ; cột B chứa lần xuất hiện trong tháng, hoặc đối với các ngày lễ cố định thì là ngày thật sự trong tháng; cột C chứa các ngày trong tuần (thứ); và cột D chứa con số chỉ tháng.

Most of the values in column E are calculated. For the floating holidays, for example, several CHOOSE() functions are used to construct the description. Here’s an example for Martin Luther King Jr. Day:
Hầu hết các giá trị trong cột E là do tính toán mà có. Ví dụ, đối với những ngày nghỉ lễ không cố định, một số hàm CHOOSE() được sử dụng để làm phần mô tả. Đây là một ví dụ cho ngày Martin Luther King Jr. :

=B5 & CHOOSE(B5, "st", "nd", "rd", "th", "th") & " " & CHOOSE(C5, "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday") & " in " & CHOOSE(D5, "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")

Finally, column F contains the formulas for calculating the date of each holiday based on the year entered in cell B1.
Sau cùng, cột F chứa các công thức để tính ngày tháng của mỗi ngày nghỉ lễ dựa vào năm được cho trong ô B1.

Two exceptions exist in column F. The first is the formula for Memorial Day (cell F7), which occurs on the last Monday in May. To derive this date, you first calculate the first Monday in June and then subtract 7 days.
Có hai ngoại lệ trong cột F. Đầu tiên là công thức tính ngày Liệt sĩ (Memorial Day, ô F7), là ngày thứ Hai cuối cùng trong tháng Năm. Để tính được ngày này, đầu tiên bạn tính ngày thứ Hai đầu tiên của tháng Sáu, rồi trừ đi 7 ngày.

The second exception is the formula for Good Friday (cell F17). This occurs two days before Easter Sunday, which is a floating holiday, but its date is based on the phase of the moon, of all things. (Officially, Easter Sunday falls on the first Sunday after the first ecclesiastical full moon after the spring equinox.) There are no simple formulas for calculating when Easter Sunday occurs in a given year. The formula in the Holidays worksheet is a complex bit of business that uses the FLOOR() function, so I discuss it when I discuss that function in Chapter 11, "Working with Math Functions".
Ngoại lệ thứ hai là công thức cho ngày Thứ Sáu Thánh (Good Friday, ô F17). Ngày này trước ngày Chủ Nhật Phục Sinh hai ngày, cũng là một ngày lễ không cố định, bởi vì nó dựa vào tuần trăng và nhiều thứ khác (chính xác là, Chủ Nhật Phục Sinh là một ngày Chủ Nhật đầu tiên sau một ngày rằm (trăng tròn) đầu tiên sau ngày xuân phân). Không có một công thức đơn giản nào để tính ngày Chủ Nhật Phục Sinh với một năm đã cho. Công thức trong bảng tính này là một công thức khá phức tạp, sử dụng hàm FLOOR(), tôi sẽ bàn luận về nó khi nói về các hàm trong chương 11: "Làm việc với các hàm toán học".
 
Lần chỉnh sửa cuối:

BNTT

Bùi Nguyễn Triệu Tường
Thành viên danh dự
Tham gia ngày
3 Tháng bảy 2007
Bài viết
4,947
Được thích
23,120
Điểm
1,860
Tuổi
50
Nơi ở
Dalat
10.2.2. Returning Parts of a Date

  • Calculating the Julian Date
    Tính ngày Julian (ngày thứ mấy trong một năm)
Excel has built-in functions that convert a given date into a numerical day of the week (the WEEKDAY() function) and that return the numerical ranking of the week in which a given date falls (the WEEKNUM() function). However, Excel doesn’t have a function that calculates the Julian date for a given date — the numerical ranking of the date for the year in which it falls. For example, the Julian date of January 1 is 1, January 2 is 2, and February 1 is 32.
Excel có những hàm cài sẵn để chuyển đổi một ngày tháng cho trước thành một con số chỉ ngày trong tuần (hàm WEEKDAY), và trả về một con số cho biết tuần lễ đang chứa một ngày cho trước là tuần thứ mấy trong một năm (hàm WEEKNUM). Tuy nhiên, Excel không có hàm nào tính toán được ngày Julian của một ngày cho trước — ngày Julian là một con số chỉ thứ tự của một ngày trong một năm. Ví dụ, ngày Julian của ngày 1 tháng Một là 1, của ngày 2 tháng Một là, của ngày 1 tháng Hai là 32, v.v...

If you need to use Julian dates in your business, here’s a formula that will do the job:
Nếu bạn cần sử dụng ngày Julian trong công việc của mình, đây là công thức để tính:

=MyDate - DATE(YEAR(MyDate) - 1, 12, 31)

This formula assumes that the date you want to work with is in a cell named MyDate. The expression DATE(YEAR(MyDate) - 1, 12, 31) returns the date serial number for December 31 of the preceding year. Subtracting this number from MyDate gives you the Julian number.
Công thức này giả định rằng ngày mà bạn muốn tìm số thứ tự của nó trong năm nằm trong một ô có tên là MyDate. Biểu thức DATE(YEAR(MyDate) - 1, 12, 31) trả về con số tuần tự theo ngày tháng của ngày 31 tháng Mười Hai của năm trước. Lấy MyDate trừ đi con số này sẽ cho ra con số Julian.
 

BNTT

Bùi Nguyễn Triệu Tường
Thành viên danh dự
Tham gia ngày
3 Tháng bảy 2007
Bài viết
4,947
Được thích
23,120
Điểm
1,860
Tuổi
50
Nơi ở
Dalat
10.2. Using Excel’s Date Functions


10.2.3. Calculating the Difference Between Two Dates
Tính toán sự chênh lệch giữa hai ngày tháng

In the previous section, you saw that Excel enables you to subtract one date from another. Here’s an example:
Trong phần trước, bạn đã thấy Excel cho phép bạn lấy một ngày trừ đi một một ngày khác. Đây là một ví dụ:

= Date1 - Date2

Here, Date1 and Date2 must be actual date values, not just date strings. When you create such a formula, Excel returns a value equal to the number of days between the two dates. This date-difference formula returns a positive number if Date1 is larger than Date2; it returns a negative number if Date1 is less than Date2. Calculating the difference between two dates is useful in many business scenarios, including receivables aging, interest calculations, benefits payments, and more.
Ở đây, Date1 và Date2 phải là các giá trị ngày tháng thực sự, chứ không phải chỉ là những chuỗi ngày tháng. Khi bạn tạo một công thức như vậy, Excel trả về một giá trị bằng số ngày giữa hai ngày đó. Công thức tính sự chênh lệch giữa hai ngày tháng này trả về một số dương nếu Date1 lớn hơn Date2, và nó trả về một số âm nếu Date1 nhỏ hơn Date2. Việc tính sự chênh lệch giữa hai ngày tháng hữu dụng trong nhiều tình huống kinh doanh, như tính các khoản phải thu quá hạn, các phép tính lãi, các khoản thanh toán lợi tức, v.v...
NOTE: If you enter a simple date-difference formula in a cell, Excel automatically formats that cell as a date. For example, if the difference between the two days is 30 days, you’ll see 30/01/1900 as the result. (If the result is negative, you’ll see the cell filled with # symbols.) To see the result properly, you need to format the cell with the General format or some numeric format.
Khi bạn nhập công thức đơn giản để tính sự chênh lệch giữa hai ngày tháng như trên vào trong một ô, Excel tự động định dạng ô đó với kiểu ngày tháng. Ví dụ, nếu sự chênh lệch giữa hai ngày là 30 ngày, bạn sẽ thấy kết quả là 30/01/1900. (Nếu như kết quả là số âm, thì bạn chỉ thấy một ô toàn là những ký hiệu
#). Để thấy được đúng kết quả mình muốn, bạn cần phải định dạng ô chứa công thức với kiểu General hoặc là kiểu số.


Besides the basic date-difference formula, you can use the date functions from earlier in this chapter to perform date-difference calculations. Also, Excel boasts a number of worksheet functions that enable you to perform more sophisticated operations to determine the difference between two dates. The rest of this section runs through a number of these date difference formulas and functions.
Ngoài công thức tính như vừa trình bày ở trên, bạn có thể dùng những hàm ngày tháng ở phần đầu chương này để thực hiện các phép tính tính sự chênh lệch giữa hai ngày tháng. Excel cũng có một số hàm cho phép bạn thực hiện việc tìm sự chênh lệch giữa hai ngày tháng cách tinh vi hơn. Nội dung còn lại của phần này sẽ trình bày một số công thức và hàm để tính sự chênh lệch ngày tháng này.
 

BNTT

Bùi Nguyễn Triệu Tường
Thành viên danh dự
Tham gia ngày
3 Tháng bảy 2007
Bài viết
4,947
Được thích
23,120
Điểm
1,860
Tuổi
50
Nơi ở
Dalat
10.2.3. Calculating the Difference Between Two Dates

  • Calculating a Person’s Age
    Tính tuổi của một người
If you have a person’s birth date entered into a cell named Birthdate and you need to calculate how old the person is, you might think that the following formula would do the job:
Nếu bạn có một ngày tháng năm sinh của một người nào đó đã được nhập trong một ô có tên là Birthdate, và bạn cần tính người đó được bao nhiêu tuổi, có thể bạn nghĩ ngay đến công thức sau đây:

= YEAR(TODAY()) - YEAR(Birthdate)

This works, but only if the person’s birthday has already passed this year. If he hasn’t had a birthday yet, this formula reports the age as being one year greater than it really is.
Công thức này đúng, nhưng chỉ khi trong năm nay người đó đã mừng sinh nhật rồi. Nếu trong năm nay người đó chưa mừng sinh nhật, thì công thức trên cho người đó già hơn một tuổi so với thực tế.

To solve this problem, you need to take into account whether the person’s birthday has passed. To see how to do this, check out the following logical expression:
Để giải quyết vấn đề này, bạn cần xem xét liệu sinh nhật của người đó đã xảy ra hay chưa. Để xem cách làm này, bạn kiểm tra biểu thức logic sau đây:

= DATE(YEAR(TODAY()), MONTH(Birthdate), DAY(Birthdate)) > TODAY()

This expression asks whether the person’s birthday for this year (which uses the formula from earlier in this chapter — see “Determining a Person’s Birthday Given the Birth Date”) is greater than today’s date. If it is, the expression returns logical TRUE, which is equivalent to 1; if it isn’t, the expression returns logical FALSE, which is equivalent to 0. In other words, you can get the person’s true age by subtracting the result of the logical expression from the original formula, like so:
Biểu thức này đòi hỏi ngày sinh nhật của một người trong năm nay (sử dụng công thức đã nói đến trong phần đầu của chương này — xem bài "Xác định Sinh Nhật của một người khi đã biết ngày tháng năm sinh") phải lớn hơn ngày hôm nay. Nếu đúng, biểu thức trả về TRUE, tương đương với 1; còn nếu không, biểu thức trả về FALSE, tương đương với 0. Nói cách khác, bạn có thể có được tuổi chính xác của một người bằng cách lấy công thức ở đầu bài trừ đi kết quả của biểu thức logic trên, như sau:

= YEAR(TODAY()) - YEAR(Birthdate) - (DATE(YEAR(TODAY()), MONTH(Birthdate), DAY(Birthdate)) > TODAY())
 

BNTT

Bùi Nguyễn Triệu Tường
Thành viên danh dự
Tham gia ngày
3 Tháng bảy 2007
Bài viết
4,947
Được thích
23,120
Điểm
1,860
Tuổi
50
Nơi ở
Dalat
10.2.3. Calculating the Difference Between Two Dates

Perhaps the easiest way to perform date-difference calculations in Excel is to use the DATEDIF() function, which returns the difference between two specified dates based on a specified unit:
Có lẽ cách dễ nhất để thực hiện phép tính tính sự chênh lệch giữa hai ngày tháng trong Excel là dùng hàm DATEDIF(), nó trả về sự chênh lệch giữa hai ngày tháng theo những loại đơn vị tính đã xác định trước:

DATEDIF (start_date, end_date [, unit])

For example, the following formula calculates the number of days between the current date and Christmas:
Ví dụ, công thức sau đây tính số ngày từ hôm nay đến lễ Giáng Sinh:

= DATEDIF(TODAY(), DATE(YEAR(TODAY()), 12, 25), "d")

You can also use the DATEDIF() function to calculate a Julian date calculation, as explained earlier in this chapter (see “Calculating the Julian Date”). If the date you want to work with is in a cell named MyDate, the following formula calculates its Julian date using DATEDIF():
Bạn cũng có thể dùng hàm DATEDIF() để tính một ngày Julian, đã được giải thích ở phần trước trong chương này (xem bài "Tính ngày Julian - ngày thứ mấy trong một năm"). Nếu cái ngày mà bạn muốn tìm xem nó là ngày thứ mấy trong năm đang ở trong một ô có tên là MyDate, công thức sau đây sẽ dùng hàm DATEDIF() để tính ngày Julian của nó:

= DATEDIF(DATE(YEAR(MyDate)-1, 12, 31), MyDate, "d")



  • Calculating a Person’s Age, Part 2
    Tính tuổi của một người - phần 2
The DATEDIF() function can greatly simplify the formula for calculating a person’s age. If the person’s date of birth is in a cell named Birthdate, the following formula calculates his current age:
Hàm DATEDIF() có thể đơn giản hóa đáng kể công thức tính tuổi của một người. Nếu ngày tháng năm sinh của một người nằm trong một ô có tên là Birthday, công thức sau đây tính tuổi chính xác của người đó:

= DATEDIF(Birthdate, TODAY(), "y")
 
Lần chỉnh sửa cuối:
Top Bottom