Formulas & Functions Excel 2007 - 10. Working with Date and Time Functions (1 người xem)

[BNTT]

10.2.3. Calculating the Difference Between Two Dates

• NETWORKDAYS(): Calculating the Number of Workdays Between Two Dates
  Hàm NETWORKDAYS: Tính số ngày làm việc giữa hai ngày tháng

If you calculate the difference in days between two days, Excel includes weekends and holidays. In many business situations, you need to know the number of workdays between two dates. For example, when calculating the number of days an invoice is past due, it’s often best to exclude weekends and holidays.
Khi bạn tính số ngày chênh lệch giữa hai ngày tháng, Excel bao gồm trong đó cả những ngày cuối tuần và các ngày nghỉ lễ. Với những tình huống kinh doanh, bạn cần biết số ngày làm việc giữa hai ngày tháng. Ví dụ, khi tính số ngày quá hạn của một hóa đơn, tốt nhất là loại trừ những ngày cuối tuần và ngày nghỉ lễ.

This is easily done using the NETWORKDAYS() function, which returns the number of working days between two dates:
Điều này được thực hiện dễ dàng với hàm NETWORKDAYS(), là hàm trả về số ngày làm việc giữa hai ngày tháng:

NETWORKDAYS (start_date, end_date [, holidays])

For example, here’s an expression that returns the number of workdays between December 1, 2007, and January 10, 2008, excluding December 25, 2007, and January 1, 2008:
Ví dụ, biểu thức sau đây trả về số ngày làm việc giữa ngày 1 tháng Mười Hai năm 2007 và ngày 10 tháng Một năm 2008, không tính ngày lễ Giáng Sinh 25/12/2007 và ngày 01/01/2007:

= NETWORKDAYS("1/12/2007", "10/1/2008", {"25/12/2007", "1/1/2008"})

Figure 10.4 shows an update to the accounts receivable worksheet that uses NETWORKDAYS() to calculate the number of workdays that each invoice is past due.
Hình 10.4 minh họa bảng cập nhật cho một bảng tính các khoản phải thu, dùng hàm NETWORKDAYS() để tính số ngày làm việc cho mỗi hóa đơn quá hạn:

Figure 10.4- Date and Time Functions.xlsx​

 

[BNTT]

10.2.3. Calculating the Difference Between Two Dates

• DAYS360(): Calculating Date Differences Using a 360-Day Year
  Hàm DAYS360: Tính số ngày chênh lệch sử dụng hệ thống một năm có 360 ngày

Many accounting systems operate using the principle of a 360-day year, which divides the year into 12 periods of uniform (30-day) lengths. Finding the number of days between dates in such a system isn’t possible with the standard addition and subtraction of dates. However, Excel makes such calculations easy with its DAYS360() function, which returns the number of days between a starting date and an ending date based on a 360-day year:
Có nhiều hệ thống kế toán sử dụng nguyên tắc một năm có 360 ngày, nó chia một năm thành 12 kỳ có số ngày bằng nhau (30 ngày). Không thể áp dụng việc cộng hay trừ ngày tháng để tìm số ngày giữa hai ngày tháng trong một hệ thống như vậy. Tuy nhiên, Excel giúp cho việc tính toán này trở nên dễ hơn với hàm DAYS360(), là hàm trả về số ngày giữa ngày bắt đầu và ngày kết thúc dựa trên cơ sở một năm có 360 ngày:

DAYS360 (start_date, end_date [, method])

For example, the following expression returns the value 1:
Ví dụ, biểu thức sau đây trả về giá trị là 1:

DAYS360("30/3/2008", "1/4/2008")​

• YEARFRAC(): Returning the Fraction of a Year Between Two Dates
  Hàm YEARFRAC: Trả về tỷ lệ so với một năm của số ngày chênh lệch giữa hai ngày tháng

Business worksheet models often need to know the fraction of a year that has elapsed between one date and another. For example, if an employee leaves after three months, you might need to pay out a quarter of a year’s worth of benefits. This calculation can be complicated by the fact that your company might use a 360-day accounting year. However, the YEARFRAC() function can help you. This function converts the number of days between a start date and an end date into a fraction of a year:
Những mô hình bảng tính kinh doanh thường cần biết tỷ lệ của một khoảng thời gian giữa một ngày này với một ngày khác đã trôi qua so với một năm. Ví dụ, nếu có một nhân viên nghỉ việc sau ba tháng làm việc, bạn cần phải chi ra một khoản phúc lợi có giá trị tương đương với một quý (1/4 của một năm). Việc tính toán này có thể trở nên phức tạp nếu công ty của bạn sử dụng hệ thống kế toán một năm có 360 ngày. Tuy nhiên, hàm YEARFRAC() có thể giúp bạn. Hàm này chuyển đổi số ngày chênh lệch giữa một ngày bắt đầu và một ngày kết thúc thành một tỷ lệ so với một năm:

YEARFRAC (start_date, end_date [, basis])

For example, the following expression returns the value 0.25:
Ví dụ, biểu thức sau đây trả về giá trị là 0.25:

YEARFRAC("15/3/2008", "15/6/2008")​

 

[BNTT]

Chapter 10 - WORKING WITH DATE AND TIME FUNCTIONS


10.3. Using Excel’s Time Functions
Sử dụng các hàm thời gian của Ecel

Working with time values in Excel is not greatly different than working with date values, although there are some exceptions, as you’ll see in this section. Here you’ll work mostly with Excel’s time functions, which work with or return time serial numbers. All of Excel’s time-related functions are listed in Table 10.5. (For the serial_number arguments, you can use any valid Excel time.)
Làm việc với các giá trị thời gian trong Excel không có gì khác lắm so với làm việc với các giá trị ngày tháng, mặc dù cũng có một số ngoại lệ mà bạn sẽ thấy trong phần này. Ở đây, bạn sẽ làm việc với phần lớn các hàm thời gian của Excel, là những hàm làm việc với hoặc trả về một con số theo thứ tự thời gian. Tất cả các hàm thời gian trong Excel được liệt kê ở bảng 10.5. (Bạn có thể nhập bất kỳ giá trị thời gian hợp lệ nào ở các đối số serial_number)

Table 10.5 - Excel’s Time Functions
Bảng 10.5 - Các hàm thời gian trong Excel

HOUR (serial_number) : Extracts the minute component from the time given byserial_number
Trích ra thành phần giờ từ một thời gian đã cho bởi serial_number

MINUTE (serial_number) : Extracts the minute component from the time given by serial_number
Trích ra thành phần phút từ một thời gian đã cho bởi serial_number

NOWhttp://www.giaiphapexcel.com/forum/showpost.php?p=50061&postcount=2() : Returns the serial number of the current date and time
Trả về con số tuần tự của ngày tháng và thời gian hiện hành.

SECONDhttp://www.giaiphapexcel.com/forum/showpost.php?p=51396&postcount=15 (serial_number) : Extracts the second component from the time given by serial_number
Trích ra thành phần giây từ một thời gian được cho bởi serial_number

TIMEhttp://www.giaiphapexcel.com/forum/showpost.php?p=51267&postcount=14 (hour, minute, second) : Returns the serial number of a time, in which hour is a number between 0 and 23, and minute and second are numbers between 0 and 59
Trả về con số tuần tự của một thời gian, trong đó hour là một con số giữa 0 và 23, minute và second là những con số giữa 0 và 59.

TIMEVALUEhttp://www.giaiphapexcel.com/forum/showpost.php?p=51267&postcount=14 (time_text) : Converts a time from text to a serial number
Chuyển đổi một thời gian từ dạng text thành một con số tuần tự​

Xem thêm: Các hàm ngày tháng và thời gian​

 

[BNTT]

10.3. Using Excel’s Time Functions


10.3.1. Returning a Time
Trả về một thời gian

If you need a time value to use in an expression or function, either you can enter it by hand if you have a specific date that you want to work with, or you can take advantage of the flexibility of three Excel functions: NOW(), TIME(), and TIMEVALUE().
Nếu bạn cần sử dụng một giá trị thời gian như một toán hạng trong một biểu thức hay trong một hàm, bạn có thể nhập nó bằng tay nếu như bạn đã có một thời gian mà bạn muốn làm việc với nó, hoặc bạn có thể tận dụng sự linh hoạt của ba hàm Excel sau đây: NOW(), TIME(), và TIMEVALUE().​

• NOW(): Returning the Current Time
  Trả về thời gian hiện hành

When you need to use the current time in a formula, function, or expression, use the NOW() function, which doesn’t take any arguments:
Khi bạn cần sử dụng thời gian 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 NOW(), là một hàm không cần đối số:

NOW()

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

39447.5

If you just want the time component of the serial number, subtract TODAY() from NOW():
Nếu bạn chỉ muốn thành phần thời gian trong con số này, bạn lấy hàm NOW() trừ đi hàm TODAY():

NOW() - TODAY()

Just like the TODAY() function, remember that NOW() is a dynamic function that doesn’t keep its initial value (that is, the time at which you entered the function). Each time you edit the formula, enter another formula, recalculate the worksheet, or reopen the workbook, NOW() uptimes its value to return the current system time.
Cũng giống như hàm TODAY(), lưu ý rằng NOW() là một hàm động, nó không giữ lại giá trị ban đầu của nó (là giá trị khi bạn nhập hàm vào). 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, NOW() cập nhật giá tri của nó để trả về thời gian hiện hành của hệ thống.​

• TIME(): Returning Any Time
  Trả về bất kỳ một thời gian nào

A time consists of three components: the hour, minute, and second. It often happens that a worksheet generates one or more of these components and you need some way of building a proper time out of them. You can do that by using Excel’s TIME() function:
Một thời gian gồm có ba thành phần: giờ, phút, và giâ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 thời gian 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 TIME() của Excel:

TIME (hour, minute, second)

For example, the following expression returns the serial number of the time 2:45:30 p.m.:
Ví dụ, biểu thức sau đây trả về số thứ tự của thời gian lúc 2:45:30 p.m:

TIME(14, 45, 30)

Like the DATE() function, TIME() adjusts for wrong hour, month, and second values. For example, the following expression returns the serial number for 3:00:30 p.m.:
Giống hàm DATE(), TIME() tự động điều chỉnh các giá trị giờ, phút và giây. Ví dụ, biểu thức sau trả về số thứ tự của thời gian lúc 3:00:30 p.m.:

TIME(14, 60, 30)

Here, TIME() takes the extra minute and adds 1 to the hour value.
Ở đây, TIME() lấy ra giá trị phút và cộng thêm 1 vào giá trị giờ.​

• TIMEVALUE(): Converting a String to a Time
  Chuyển đổi một chuỗi thành một thời gian

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

TIMEVALUE (time_text)

For example, the following expression returns the time serial number for the string 2:45:00 PM:
Ví dụ, biểu thức sau đây trả về thứ tự thời gian liên tục của chuỗi 2:45:00 PM

TIMEVALUE("2:45:00 PM")​

 

[BNTT]

10.3. Using Excel’s Time Functions


10.3.2. Returning Parts of a Time
Trả về các thành phần của một thời gian

The three components of a time — hour, minute, and second — can also be extracted individually from a given time using Excel’s HOUR(), MINUTE(), and SECOND() functions.
Ba thành phần của một thời gian — giờ, phút, và giây — cũng có thể được trích xuất riêng lẻ từ một thời gian đã cho bằng cách sử dụng các hàm HOUR(), MINUTE() và SECOND() của Excel.​

• The HOUR Function — Hàm HOUR

The HOUR() function returns a number between 0 and 23 that corresponds to the hour component of a specified time:
Hàm HOUR() trả về một con số giữa 0 và 23 tương ứng với thành phần giờ của một thời gian đã xác định:

YEAR (serial_number)

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

HOUR(0.5)​

• The MINUTE Function — Hàm MINUTE

The MINUTE() function returns a number between 0 and 59 that corresponds to the minute component of a specified time:
Hàm MINUTE() trả về một con số giữa 0 và 59 tương ứng với thành phần phút của một thời gian đã xác định:

MINUTE (serial_number)

For example, if it’s currently 3:15 p.m., the following expression will return 15:
Ví dụ, nếu bây giờ là 3:15 P.M., biểu thức sau đây sẽ trả về 15:

MINUTE(NOW())​

• The SECOND Function — Hàm SECOND

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

SECOND (serial_number)

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

SECOND("2:45:30 PM") ​

 

[BNTT]

10.3.2. Returning Parts of a Time

• Returning a Time X Hours, Minutes, or Seconds from Now
  Trả về một thời gian nào đó tính từ bây giờ

As I mentioned earlier, TIME() automatically adjusts wrong hour, minute, and second values. You can take advantage of this by applying formulas to one or more of the TIME() function’s arguments. The most common use for this is to return a time that occurs X number of hours, minutes, or seconds from now (or from any time). For example, the following expression returns the time 12 hours from now:
Như tôi đã đề cập đến ở trên, là TIME() tự động điều chỉnh những giá trị giờ, phút, và giây nhập sai. Bạn có thể tận dụng điều này 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 TIME(). Công dụng phổ biến nhất cho chuyện này là dùng để tính một thời gian nào đó ở dạng giờ, phút, hoặc giây kể từ từ lúc này (hoặc từ bất kỳ lúc nào). Ví dụ, biểu thức sau đây trả về thời gian sau 12 tiếng kể từ lúc này:

TIME(HOUR(NOW()) + 12, MINUTE(NOW()), SECOND(NOW()))

Unlike the DATE() function, the TIME() function doesn’t enable you to simply add an hour, minute, or second to a specified time. For example, consider the following expression:
Không giống như hàm DATE(), hàm TIME() không cho phép bạn đơn giản cộng thêm vào một giờ, một phút hay một giây vào một giá trị thời gian đã xác định. Ví dụ, bạn xem biểu thức sau:

NOW() + 1

All this does is add one day to the current date and time.
Tất cả những gì nó làm chỉ là cộng thêm một ngày vào ngày tháng và thời gian hiện tại.

If you want to add hours, minutes, and seconds to a time, you need to express the added time as a fraction of a day. For example, because there are 24 hours in a day, 1 hour is represented by the expression 1/24. Similarly, because there are 60 minutes in an hour, 1 minute is represented by the expression 1/24/60. Finally, because there are 60 seconds in a minute, 1 second is represented by the expression 1/24/60/60. Table 10.6 shows you how to use these expressions to add n hours, minutes, and seconds.
Nếu bạn muốn thêm giờ, phút, giây vào một thời gian, bạn cần phải biểu diễn thời gian cộng thêm dưới dạng là một phần mấy của một ngày. Ví dụ, bởi vì có 24 tiếng trong môt ngày, nên 1 giờ được biểu diễn bằng biểu thức 1/24. Tương tự, bởi vì có 60 phút trong một giờ, nên 1 phút được biểu diễn bằng biểu thức 1/24/60. Cuối cùng, bởi vì có 60 giây trong một phút, nên 1 giây được biểu diễn bởi biểu thức 1/24/60/60. Bảng 10.6 minh họa cho bạn cách dùng những biểu thức này để thêm n giờ, phút và giây.

Table 10.6 Adding Hours, Minutes, and Seconds
Bảng 10.6. Thêm giờ, phút, giây

 

[BNTT]

10.3.2. Returning Parts of a Time

• Summing Time Values
  Tính tổng các giá trị thời gian

When working with time values in Excel, you need to be aware that there are two subtly different interpretations for the phrase “adding one time to another”:
Khi làm việc với các giá trị thời gian, bạn cần lưu ý rằng có hai cách diễn đạt khác nhau cho cụm từ "cộng một thời gian với một thời gian khác":

• Adding time values to get a future time. As you saw in the previous section, adding hours, minutes, or seconds to a time returns a value that represents a future time. For example, if the current time is 11:00 p.m. (23:00), adding two hours returns the time 1:00 a.m.
  Cộng các giá trị thời gian để có một giá trị thời gian trong tương lai. Như bạn đã thấy trong phần trước, thêm giờ, phút, giây vào một thời gian sẽ trả về một giá trị tượng trưng cho một thời gian tương lai. Ví dụ, nếu hiện giờ là 11:00 p.m. (23:00), thêm hai tiếng nữa sẽ có thời gian là 1:00 a.m.
  
  
• Adding time values to get a total time. In this interpretation, time values are summed to get a total number of hours, minutes, and seconds. This is useful if you want to know how many hours an employee worked in a week, or how many hours to bill a client. In this case, for example, if the current total is 23 hours, adding 2 hours brings the total to 25 hours.
  Cộng các giá trị thời gian để có tổng thời gian. Trong cách diễn đạt này, các giá trị thời gian được tính tổng để có được tổng số giờ, phút và giây. Điều này hữu dụng nếu bạn muốn biết một nhân viên đã làm việc bao giờ giờ trong một tuần, hoặc mất bao nhiêu thời gian để lập một hóa đơn bán hàng. Ví dụ, trong trường hợp này, nếu hiện giờ ta có tổng thời gian là 23 giờ, cộng thêm 2 giờ nữa sẽ có con số tổng là 25 giờ.

The problem is that adding time values to get a future time is Excel’s default interpretation for added time values. So, if cell A1 contains 23:00 and cell A2 contains 2:00, the following formula will return 1:00:00 AM:
Vấn đề là, Excel mặc định diễn đạt cho việc cộng thêm các giá trị thời gian là để có một thời gian tương lai. Do đó, nếu ô A1 chứa 23:00 và ô A2 chứa 2:00, công thức sau đây sẽ trả về 1:00 AM:

= A1 + A2

The time value 25:00:00 is stored internally, but Excel adjusts the display so that you see the “correct” value 1:00:00 AM. If you want to see 25:00:00 instead, apply the following custom format to the cell:
Giá trị thời gian là 25:00:00 được lưu trữ bên trong, còn bên ngoài thì Excel hiển thị để bạn thấy được giá trị "đúng" là 1:00:00 AM. Nếu thay vì vậy, bạn muốn thấy 25:00:00, bạn hãy áp dụng định dạng sau đây cho ô chứa kết quả:

[h]:mm:ss​

 

[BNTT]

10.3. Using Excel’s Time Functions


10.3.3. Calculating the Difference Between Two Times
Tính toán sự chênh lệch giữa hai giá trị thời gian

Excel treats time serial numbers as decimal expansions (numbers between 0 and 1) that represent fractions of a day. Beáause they’re just numbers, there’s nothing to stop you from subtracting one from another to determine the difference between them:
Excel xem các số theo thứ tự thời gian là những con số thập phân (giữa 0 và 1) tượng trưng cho một phần của một ngày. Bởi vì chúng chỉ là những con số, nên không có gì ngăn cản bạn việc lấy một số ngày trừ đi một số khác để có được sự chênh lệch giữa chúng:

EndTime - StartTime

This expression works just fine, as long as EndTime is greater than StartTime. (I used the names EndTime and StartTime purposefully so you would remember to always subtract the later time from the earlier time.)
Biểu thức trên làm việc tốt, miễn là EndTime thì lớn hơn StartTime. (Tôi dùng cái tên EndTime và StartTime để nhắc rằng bạn phải nhớ luôn luôn lấy thời gian sau trừ đi thời gian trước).

However, there’s one scenario in which this expression will fail: If EndTime occurs after midnight the next day, there’s a good chance that it will be less than StartTime. For example, if a person works from 11:00 p.m. to 7:00 a.m., the expression 7:00 AM - 11:00 PM will result in an illegal negative time value. (Excel displays the result as a series of # symbols that fill the cell.)
Tuy nhiên, có một tình huống mà trong đó biểu thức trên sẽ sai: Nếu EndTime rơi vào khoảng sau nửa đêm ngày hôm sau, có khả năng nó sẽ nhỏ hơn hơn StartTime. Ví dụ, nếu một người làm việc từ 11 giờ tối cho đến 7 giờ sáng, biểu thức 7:00 AM - 11:00 PM sẽ cho kết quả là một giá trị thời gian âm, không hợp lệ. (Excel sẽ hiển thị một kết quả ở dạng toàn là những ký hiệu # trong ô).

To ensure that you get the correct positive result in this situation, use the following generic expression:
Để bảo đảm rằng bạn có được một kết quả dương trong tình huống này, bạn dùng biểu thức chung sau đây:

IF(EndTime < StartTime, 1 + EndTime - StartTime, EndTime - StartTime)

The IF() function checks to see if EndTime is less than StartTime. If it is, it adds 1 to the value EndTime – StartTime to get the correct result; otherwise, just EndTime – StartTime is returned.
Hàm IF() kiểm tra xem EndTime có nhỏ hơn StartTime hay không. Nếu nó nhỏ hơn, thì cộng thêm 1 vào giá trị EndTime – StartTime để có được kết quả đúng; còn không thì chính giá trị EndTime – StartTime được trả về.​

 

[BNTT]

Case Study


10.4. Building an Employee Time Sheet
Xây dựng một bảng chấm công

In this case study, you’ll put your new knowledge of time functions and calculations to good use building a time sheet that tracks the number of hours an employee works each week, takes into account hours worked on weekends and holidays, and calculates the total number of hours and the weekly pay. Figure 10.5 shows the completed time sheet.
Trong bài thực hành này, bạn sẽ vận dụng những kiến thức mới về các hàm thời gian và các kỹ năng tính năng của mình để xây dựng một bảng chấm công, theo dõi số giờ làm việc trong mỗi tuần của một nhân viên, cả số giờ làm việc trong các ngày cuối tuần và ngày nghỉ lễ, tính toán tổng số giờ làm và tiền lương mỗi tuần. Hinh 10.5 minh họa một bảng tính chấm công đã hoàn chỉnh.

Figure 10.5- Date and Time Functions.xlsx​

Before starting, you need to understand three terms used in this case study:
Trước khi bắt đầu, bạn cần hiểu về 3 thuật ngữ được dùng trong bài thực hành này:

• Regular hours — These are hours worked for regular pay.
  Là những giờ làm được tính tiền công bình thường.
  
  
• Overtime hours — These are hours worked beyond the maximum number of regular hours, as well as any hours worked on the weekend.
  Là những giờ làm thêm: số giờ vượt quá số giờ quy định, hoặc là những số giờ làm trong những ngày cuối tuần.
  
  
• Holiday hours — These are hours worked on a statutory holiday.
  Là những giờ làm trong những ngày nghỉ lễ.

Entering the Time Sheet Data
Nhập dữ liệu vào bảng chấm công

Let’s begin at the top of the time sheet, where the following data is required:
Hãy bắt đầu ở phần trên cùng của bảng tính, là nơi yêu cầu có các dữ liệu sau đây:

• Employee Name — You’ll create a separate sheet for each employee, so enter the person’s name here. You might also want to augment this with the date the person started or other data about the employee.
  Bạn sẽ tạo cho mỗi nhân viên một Sheet riêng biệt, do đó, bạn nhập tên của nhân viên ở đây. Bạn cũng có thể thêm vài chi tiết về nhân viên này, như là ngày vào làm, hoặc là vài dữ liệu khác.
  
  
• Maximum Hours Before Overtime — This is the number of regular hours an employee has to work in a week before overtime hours take effect. Enter the number using the hh:mm format. Cell D3 uses the [h]:mm custom format, to ensure that Excel displays the actual value.
  Đây là số thời gian quy định mà một nhân viên phải làm trong một tuần trước khi được tính số giờ làm thêm. Nhập một số với định dạng hh:mm. Ô C3 sử dụng kiểu định dạng tùy biến là [h]:mm, để bảo đảm Excel sẽ hiển thị một giá trị thực.
  
  
• Hourly Wage — This is the amount the employee earns per regular hour of work.
  Đây là số tiền lương mà nhân viên đó được hưởng cho mỗi giờ làm việc.
  
  
• Overtime Pay Rate — This is the factor by which the employee’s hourly rate is increased for overtime hours. For example, enter 1.5 if the employee earns time and a half for overtime.
  Đây là một hệ số lương sẽ nhân với mức lương cơ bản cho mỗi giờ làm thêm của nhân viên. Ví dụ, nhập 1.5 vào, nếu nhân viên được nhập gấp rưỡi tiền lương (tăng thêm 50%) cho mỗi giờ làm thêm.
  
  
• Holiday Pay Rate — This is the factor by which the employee’s hourly rate is increased for holiday hours. For example, enter 2 if the employee earns double time for holidays.
  Đây là một hệ số lương sẽ nhân với mức lương cơ bản cho mỗi giờ làm trong những ngày nghỉ lễ. Ví dụ, nhập số 2 nếu nhân viên sẽ được nhân gấp đôi tiền lương cho mỗi giờ làm trong những ngày nghỉ lễ.

Calculating the Daily Hours Worked
Tính số giờ làm hằng ngày

Figure 10.6 shows the portion of the time sheet used to record the employee’s daily hours worked. For each day, you enter five items:
Hình 10.6 minh họa phần chính của bảng chấm công, sử dụng để tính số giờ làm việc hằng ngày của nhân viên. Mỗi ngày, bạn phải nhập 5 khoản này:

Figure 10.6- Date and Time Functions.xlsx

• Date — Enter the date the employee worked. This is formatted to show the day of the week, which is useful for confirming overtime hours worked on weekends.
  Nhập ngày làm việc của nhân viên. Nó được định dạng để hiển thị cả tên ngày trong tuần, giúp xác nhận những giờ làm thêm trong những ngày cuối tuần.
  
  
• Work Start Time — Enter the time of day the employee began working.
  Nhập thời gian bắt đầu làm trong ngày của nhân viên.
  
  
• Lunch Start Time — Enter the time of day the employee stopped for lunch.
  Nhập thời gian nhân viên nghỉ để ăn trưa trong ngày (thời gian bắt đầu nghỉ giữa giờ)
  
  
• Lunch End Time — Enter the time of day the employee resumed working after lunch.
  Nhập thời gian nhân viên tiếp tục công việc sau khi ăn trưa.
  
  
• Work End Time — Enter the time of day the employee stopped working.
  Nhập thời gian nhân viên ngưng việc (ra về).

The first calculation occurs in the Total Hours Worked column (F). The idea here is to sum the total number of hours the employee worked in a given day. The first part of the calculation uses the time-difference formula from the previous section to derive the number of hours between the Work Start Time (column B) and the Work End Time (column E). Here’s the expression for the first entry (row 9):
Việc tính toán trước tiên là ở cột Total Hours Worked (Tổng số giờ làm việc - cột F). Gợi ý ở đây là cộng hết tổng số giờ mà nhân viên đã làm trong một ngày. Phần đầu của việc tính toán sử dụng công thức tính sự chênh lệch thời gian (đã nói ở bài trước) để suy ra số giờ giữa Work Start Time (thời gian bắt đầu làm - cột B) và Work End Time (thời gian kết thúc - cột E). Đây là biểu thức ở hàng đầu tiên (hàng 9):

IF(E9 < B9, 1 + E9 - B9, E9 - B9)

However, we also have to subtract the time the employee took for lunch, which is the difference between the Lunch Start Time (column C) and the Lunch End Time (column D). Here’s the expression for the first entry (row 9):
Tuy nhiên, chúng ta phải trừ đi thời gian mà nhân viên này đi ăn trưa, là sự chênh lệch giữa Lunch Start Time (thời gian nghỉ để ăn trưa - cột C) và Lunch End Time (thời gian ăn trưa xong - cột D). Biểu thức cho mục này (hàng 9) như sau:

IF(D9 < C9, 1 + D9 - C9, D9 - C9)

Let’s skip over the Overtime Hours calculation (column H). The idea behind this column is that if the employee worked on the weekend, all of the hours worked should be booked as overtime hours. So, the formula checks to see if the date is a Saturday or Sunday:
Chuyển sang việc tính Overtime Hours (giờ làm thêm - cột H). Gợi ý cho cột này là nếu nhân viên đó làm trong những ngày cuối tuần, thì toàn bộ số giờ làm việc sẽ tính là thời gian làm thêm. Công thức để kiểm tra xem ngày làm việc có phải là Thứ Bảy hay Chủ Nhật hay không:

= IF(OR(WEEKDAY(A9) = 7, WEEKDAY(A9) = 1), F9, 0)

If the OR() function returns TRUE, the date is on the weekend, so the value from the Total Hours Worked column (F9, in the example) is entered into the Overtime Hours column; otherwise, 0 is returned.
Nếu hàm OR() trả về TRUE, ngày làm là ngày cuối tuần, thì giá trị trong cột Total Hours Worked (trong ví dụ này là giá trị ở F9) sẽ được nhập vào trong cột Overtime Hours, còn không thì nhập số 0 vào.​

TO BE CONTINUED...

 

[BNTT]

Case Study

10.4. Building an Employee Time Sheet
Xây dựng một bảng chấm công

(CONTINUE)

Next up is the Holiday Hours calculation (column I). Here you want to see if the date is a statutory holiday. If it is, all of the hours worked that day should be booked as holiday hours. To that end, the formula checks to see if the date is part of the range of holiday dates calculated earlier in this chapter:
Tiếp theo là tính cột Holiday Hours (cột H). Ở đây chúng ta muốn biết ngày làm việc có phải là một ngày nghỉ lễ hay không. Nếu là ngày nghỉ lễ, thì toàn bộ số giờ làm việc của ngày này sẽ được tính theo giờ làm trong ngày nghỉ lễ. Công thức để kiểm tra xem ngày làm có phải là một ngày trong số những ngày nghỉ lễ đã được tính toán ở bài trước trong chương này hay không:

{= SUM(IF(A9 = Holidays!F4:F13, 1, 0)) * F9}

This is an array formula that compares the date with the dates in the holiday range (Holidays!F4:F13). If a match occurs, the SUM() function returns 1; otherwise, it returns 0. This result is multiplied by the value in the Total Hours Worked column (F9, in the example). So, if the date is a holiday, the hours for that day are entered as holiday hours.
Đây là một công thức mảng, so sánh ngày làm với những ngày trong dãy ngày nghỉ lễ ((Holidays!F4:F13 - dãy này nằm bên Sheet Holidays)). Nếu thấy trùng nhau, hàm SUM() trả về 1, còn không thì trả về 0. Kết quả này được nhân với kết quả trong cột Total Hours Worked (ở ví dụ này là ô F9). Do đó, nếu ngày làm là một ngày nghỉ lễ, số giờ làm sẽ được tính là những giờ làm vào ngày nghỉ lễ.

Finally, the value in the Non-Weekend, Non-Holiday Hours column (cột G) is calculated by subtracting Overtime Hours and Holiday Hours from Total Hours Worked:
Cuối cùng, giá trị trong cột Non-Weekend, Non-Holiday Hours (cột G) được tính bằng cách lấy số giờ làm ở Total Hours Worked trừ đi số giờ làm Overtime Hours và Holiday Hours:

= F9 - H9 - I9​

Calculating the Weekly Hours Worked
Tính số giờ làm việc hằng tuần

Next up is the Total Weekly Hours section (see Figure 10.5), which adds the various types of hours the employee worked during the week.
Tiếp theo là phần Total Weekly Hours (tổng số giờ làm trong tuần - xem hình 10.5), có thêm việc phân loại giờ làm của nhân viên trong suốt tuần.

The Total Hours value is a straight sum of the values in the Total Hours Worked column (F):
Giá trị Total Hours là cộng hết những giá trị trong cột Total Hours Worked (cột F):

= SUM(F9:F15)

To derive the Weekly Regular Hours value, the calculation has to check to see if the total in the Non-Weekend, Non-Holiday Hours column (G) exceeds the number in the Maximum Hours Before Overtime cell (D3):
Để tính giá trị Weekly Regular Hours (số giờ làm quy định hằng tuần), phải kiểm tra xem tổng số ở cột Non-Weekend, Non-Holiday Hours (cột G) có vượt con số ở ô Maximum Hours Before Overtime (D3) hay không:

= IF(SUM(G9:G15) > D3, D3, SUM(G9:G15))

If this is true, the value in D3 is entered as the Regular Hours value; otherwise, the sum is entered.
Nếu đúng (là lớn hơn), thì nhập giá trị ở D3 vào giá trị Regular Hours, còn không thì nhập tổng số giờ (tổng cột G) vào.

Calculating the Weekly Overtime Hours value is a two-step process. First you have to check to see if the sum in the Non-Weekend, Non-Holiday Hours column (G) exceeds the number in the Maximum Hours Before Overtime cell (D3). If so, the number of overtime hours is the difference between them; otherwise, it’s 0:
Tính giá trị Weekly Overtime Hours (số giờ làm thêm trong tuần) là một quá trình gồm có 2 bước. Đầu tiên bạn phải kiểm tra xem tổng số ở cột Non-Weekend, Non-Holiday Hours (cột G) có lớn hơn con số ở ô Maximum Hours Before Overtime (D3) không. Nếu lớn hơn, thì số giờ làm thêm sẽ bằng số chênh lệch giữa chúng, còn không thì bằng 0:

IF(SUM(G9:G15) > D3, SUM(G9:G15) - D3, "0:00")

Second, you need to add the sum of the Overtime Hours column (H):
Thứ hai, bạn cần phải cộng thêm tổng số của cột Overtime Hours (cột H):

= IF(SUM(G9:G15) > D3, SUM(G9:G15) - D3, "0:00") + SUM(H9:H15)

Finally, the Weekly Holiday Hours value is a straight sum of the values in the Holiday Hours column (I):
Cuối cùng, giá trị Weekly Holiday Hours (số giờ làm trong những ngày nghỉ lễ) là cộng hết các giá trị trong cột Holiday Hours (cột H):

= SUM(I9:I15)​

Calculating the Weekly Pay
Tính số tiền lương hằng tuần

The final section of the time sheet is the Weekly Pay calculation. The dollar amounts for Regular Pay, Overtime Pay, and Holiday Pay are calculated as follows:
Phần cuối cùng của bảng chấm công là tính Weekly Pay (lương hằng tuần). Giá trị (tiền) ở Regular Pay (lương cơ bản), Overtime Pay (lương làm thêm), và Holiday Pay (lương ngày nghỉ) được tính như sau:

Regular Pay = Weekly Regular Hours * Hourly Wage * 24
Lương cơ bản = Số giờ làm cơ bản * Mức lương cơ bản * 24

Overtime Pay = Weekly Overtime Hours * Hourly Wage * Overtime Pay Rate * 24
Lương làm thêm = Số giờ làm thêm * Mức lương cơ bản * Hệ số lương làm thêm * 24

Holiday Pay = Weekly Holiday Hours * Hourly Wage * Holiday Pay Rate * 24
Lương làm ngày nghỉ = Số giờ làm ngày nghỉ * Mức lương cơ bản * Hệ số lương ngày nghỉ * 24

Note that you need to multiply by 24 to convert the time value to a real number. Finally, the Total Pay is the sum of these values.
Lưu ý rằng bạn cần phải nhân thêm 24 để chuyển đổi giá trị thời gian thành giá trị thật. Cuối cùng, Total Pay (tổng số lương) là cộng hết các giá trị ở trên lại.​

----------------------------------------------- End Of Chapter 10 -----------------------------------------------​