Hàm ngày tháng và thời gian trong excel 2010

Một số lưu ý khi sử dụng ngày tháng và thời gian trong Excel: 
Excel hỗ trợ tính toán ngày tháng cho Windows và Macintosh. Windows dùng hệ ngày bắt đầu từ 1900. Macitosh dùng hệ ngày bắt đầu từ 1904. Tài liệu này được diễn giải theo hệ ngày 1900 dành cho Windows.
Hệ thống ngày giờ Excel phụ thuộc vào thiết lập trong Regional Options của Control Panel. Mặc định là hệ thống của Mỹ "Tháng/Ngày/Năm" (M/d/yyyy). Bạn có thể sửa lại thành hệ thống ngày của VN "Ngày/Tháng/Năm" (dd/MM/yyyy).
Khi bạn nhập một giá trị ngày tháng không hợp lệ nó sẽ trở thành một chuỗi văn bản. Công thức tham chiếu tới giá trị đó sẽ trả về lỗi.
=NOW() Cho hiện ngày giờ của hệ thống
=TODAY() Cho ngày của hệ thống
=DAY(D) Cho giá trị ngày của D (Trả về thứ tự của ngày trong tháng từ một giá trị kiểu ngày tháng)
=MONTH(D) Cho giá trị tháng của D
=YEAR(D) Cho giá trị năm của D
=DAYS360(BTNT1, BTNT2) Tính số ngày giữa 2 mốc ngày tháng dựa trên cơ sở một năm có 360 ngày.
=EDATE Trả về mốc thời gian xảy ra trước hoặc sau mốc chỉ định
=EOMONTH Trả về ngày cuối cùng của tháng xảy ra trước hoặc sau mốc chỉ định
Hàm HOUR()
Cho biết số chỉ giờ trong một giá trị thời gian
Cú pháp: = HOUR(serial_number)
serial_number: Biểu thức thời gian hoặc là một con số chỉ giá trị thời gian
Ví dụ: HOUR(0.5) = 12 (giờ)
Hàm MINUTE()
Cho biết số chỉ phút trong một giá trị thời gian
Cú pháp: = MINUTE(serial_number)
serial_number: Biểu thức thời gian hoặc là một con số chỉ giá trị thời gian
Ví dụ: Bây giờ là 10:20 PM, MINUTE(NOW()) = 20 (phút)
=MONTH Trả về số tháng của một giá trị kiểu ngày tháng.
=NETWORKDAYS Trả về số ngày làm việc trong mốc thời gian đưa ra sau khi trừ đi ngày nghĩ và ngày lễ.
=NOW Trả về ngày giờ hiện tại trong hệ thống của bạn.
Hàm SECOND()
Cho biết số chỉ giây trong một giá trị thời gian
Cú pháp: = SECOND(serial_number)
serial_number: Biểu thức thời gian hoặc là một con số chỉ giá trị thời gian
Ví dụ: SECOND("2:45:30 PM") = 30 (giây)
Hàm TIME()
Trả về một giá trị thời gian nào đó
Cú pháp: = TIME(hour, minute, second)
hour: Số chỉ giờ, là một con số từ 0 đến 23. Nếu lớn hơn 23, Excel sẽ tự trừ đi một bội số của 24.
minute: Số chỉ phút, là một con số từ 0 đến 59. Nếu lớn hơn 59, Excel sẽ tính lại và tăng số giờ lên tương ứng.
second: Số chỉ giây, là một con số từ 0 đến 59. Nếu lớn hơn 59, Excel sẽ tính lại và tăng số phút, số giờ lên tương ứng.
Ví dụ:
TIME(14, 45, 30) = 2:45:30 PM
TIME(14, 65, 30) = 3:05:30 PM
TIME(25, 85, 75) = 2:26:15 AM
* Cũng như DATE(), hàm TIME() rất hữu dụng khi hour, minute, second là những công thức mà không phải là một con số, nó sẽ giúp chúng ta tính toán chính xác hơn
Hàm TIMEVALUE()
Chuyển đổi một chuỗi văn bản có dạng thời gian thành một giá trị thời gian để có thể tính toán được
Cú pháp: = TIMEVALUE(time_text)
time_text: Chuỗi văn bản cần chuyển đổi
Ví dụ:
TIMEVALUE("26:15") = 0.09375 (= 2:15:00 AM)
=WEEKDAY Trả về số thứ tự của ngày trong tuần từ giá trị ngày tháng.
=WEEKNUM Trả về số thứ tự của tuần trong năm từ giá trị ngày tháng.
=WORKDAY Trả về ngày làm việc xảy ra trước hoặc sau mốc thời gian đưa ra.
Trong công việc hằng ngày, chắc hẳn chúng ta hay nghĩ đến chuyện việc làm này của mình mất hết mấy phần trăm của một năm, ví dụ, một ngày ngủ hết 6 tiếng, là 1/4 ngày, vậy một năm chúng ta ngủ hết 25% (hic) thời gian...
Hoặc một nhân viên của công ty xin nghỉ việc vào tháng 5, lương tính theo năm, vậy công ty phải trả cho người đó bao nhiêu phần trăm lương khi cho nghỉ việc?
Excel có một hàm để tính tỷ lệ của một khoảng thời gian trong một năm, và cho phép tính theo nhiều kiểu (năm 365 ngày, hay năm 360 ngày, tính theo kiểu Mỹ hay theo kiểu châu Âu...):
Hàm YEARFRAC()
(Dịch từ chữ Year: năm, và Frac = Fraction: tỷ lệ)
Cú pháp: = YEARFRAC(start_date, end_date [, basis])
start_date, end_date: Ngày tháng đại diện cho ngày bắt đầu và ngày kết thúc của khoảng thời gian cần tính toán. Nên nhập bằng hàm DATE(), hoặc dùng một kết quả trả về của một công thức khác, vì có thể sẽ xảy ra lỗi nếu bạn nhập trực tiếp ngày tháng dưới dạng text.
basis: Một con số, quy định kiểu tính:
* 0 : (hoặc không nhập) Tính toán theo kiểu Bắc Mỹ, một năm có 360 ngày chia cho 12 tháng, một tháng có 30 ngày.
* 1 : Tính toán theo số ngày thực tế của năm và số ngày thực tế của từng tháng
* 2 : Tính toán theo một năm có 360 ngày, nhưng số ngày là số ngày thực tế của từng tháng
* 3 : Tính toán theo một năm có 365 ngày, và số ngày là số ngày thực tế của từng tháng
* 4 : Tính toán theo kiểu Châu Âu,mỗi tháng có 30 ngày (nếu start_date hoặc end_date mà rơi vào ngày 31 của một tháng thì chúng sẽ được đổi thành ngày 30 của tháng đó)
Ví dụ: Tính tỷ lệ giữa ngày 15/3/2010 và ngày 30/7/2010 so với 1 năm:
YEARFRAC("15/3/2010", "30/7/2010") = 37%
Tính số ngày chênh lệch theo kiểu một năm có 360 ngày
Hiện nay, vẫn còn một số hệ thống kế toán dùng kiểu tính thời gian là một tháng coi như có 30 ngày và một năm coi như có 360 ngày!
Gặp trường hợp này, việc tính toán thời gian sẽ không đơn giản, vì thực tế thì số ngày trong mỗi tháng đâu có giống nhau.
Có lẽ vì nghĩ đến chuyện đó, nên Excel có một hàm dành riêng cho các hệ thống kế toán dựa trên cơ sở một năm có 360 ngày, đó là hàm DAYS360.
Hàm DAYS360()
Cú pháp: = DAYS360(start_date, end_date [, method])
start_date, end_date: Ngày tháng đại diện cho ngày bắt đầu và ngày kết thúc của khoảng thời gian cần tính toán. Nên nhập bằng hàm DATE(), hoặc dùng một kết quả trả về của một công thức khác, vì có thể sẽ xảy ra lỗi nếu bạn nhập trực tiếp ngày tháng dưới dạng text.
method: Một giá trị logic (TRUE, FALSE) để chỉ cách tính toán, theo kiểu châu Âu hay theo kiểu Mỹ.
* FALSE: (hoặc không nhập) Tính toán theo kiểu Mỹ: Nếu start_date là ngày 31 của tháng, thì nó được đổi thành ngày 30 của tháng đó. Nếu end_date là ngày 31 của tháng và start_date nhỏ hơn 30, thì end_date được đổi thành ngày 1 của tháng kế tiếp.
* TRUE: Tính toán theo kiểu châu Âu: Hễ start_date hoặc end_date mà rơi vào ngày 31 của một tháng thì chúng sẽ được đổi thành ngày 30 của tháng đó.
Ví dụ: So sánh số ngày chênh lệch giữa 01/01/2008 và 31/5/2008 theo kiểu một năm có 360 ngày và theo kiểu thường (dùng hàm DATEDIF)
DAYS360("01/01/2008", "31/5/2008") = 150
DAYS360("01/01/2008", "31/5/2008", TRUE) = 149
DATEDIF("01/01/2008", "31/5/2008", "d") = 151
Tính số ngày làm việc giữa hai khoảng thời gian
Bình thường, nếu lấy ngày tháng trừ ngày tháng, kết quả sẽ bao gồm luôn những ngày lễ, ngày nghỉ, v.v... Còn nếu tính số ngày làm việc trong một khoảng thời gian, thì phải trừ bớt đi những ngày không làm việc.
Trong Excel có một hàm chuyên để tính toán những ngày làm việc giữa hai khoảng thời gian mà không bao gồm các ngày thứ Bảy, Chủ Nhật và những ngày nghỉ khác được chỉ định: Hàm NETWORKDAYS (đúng nguyên nghĩa của nó: net workdays).
Dĩ nhiên hàm này chỉ thích hợp với những cơ quan làm việc 5 ngày một tuần, chứ như chúng ta, làm tuốt, có khi là 365 ngày một năm (hic) thì hàm này vô tác dụng!
Hàm NETWORKDAYS()
Cú pháp: = NETWORKDAYS(start_date, end_date [, holidays])
start_date, end_date: Ngày tháng đại diện cho ngày bắt đầu và ngày kết thúc công việc. Nên nhập bằng hàm DATE(), hoặc dùng một kết quả trả về của một công thức khác, vì có thể sẽ xảy ra lỗi nếu bạn nhập trực tiếp ngày tháng dưới dạng text.
holidays: Danh sách những ngày nghỉ ngoài những ngày thứ Bảy và Chủ Nhật. Danh sách này có thể là một vùng đã được đặt tên. Nếu nhập trực tiếp thì phải bỏ trong cặp dấu móc {}.
Ví dụ: Công thức tính số ngày làm việc giữa ngày 1/12/2010 và ngày 10/1/2011, trong đó có nghỉ ngày Noel (25/12) và ngày Tết Tây (1/1):
= NETWORKDAYS("01/12/2010", "10/01/2011", {"12/25/2007", "1/1/2011"})
Hàm DATEDIF()
Có lẽ cách dễ nhất khi muốn tính toán ngày tháng năm là dùng hàm DATEDIF().
Cú pháp: = DATEDIF(start_day, end_day, unit)
start_day: Ngày đầu
end_day: Ngày cuối (phải lớn hơn ngày đầu)
unit: Chọn loại kết quả trả về (khi dùng trong hàm phải gõ trong dấu ngoặc kép)
y : số năm chênh lệch giữa ngày đầu và ngày cuối
m : số tháng chênh lệch giữa ngày đầu và ngày cuối
d : số ngày chênh lệch giữa ngày đầu và ngày cuối
md : số ngày chênh lệch giữa ngày đầu và tháng ngày cuối, mà không phụ thuộc vào số năm và số tháng
ym : số tháng chênh lệch giữa ngày đầu và ngày cuối, mà không phụ thuộc vào số năm và số ngày
yd : số ngày chênh lệch giữa ngày đầu và ngày cuối, mà không phụ thuộc vào số năm
Ví dụ:
DATEDIF("01/01/2000", "31/12/2100", "y") = 100 (năm)
DATEDIF("01/01/2000", "31/12/2100", "m") = 1211 (tháng)
DATEDIF("01/01/2000", "31/12/2100", "d") = 36889 (ngày)
DATEDIF("01/01/2000", "31/12/2100", "md") = 30 (= ngày 31 - ngày 1)
DATEDIF("01/01/2000", "31/12/2100", "ym") = 11 (= tháng 12 - tháng 1)
DATEDIF("01/01/2000", "31/12/2100", "yd") = 365 (= ngày 31/12 - ngày 1/1)
*Tính tuổi (2):
Ở bài trước, tôi đã đưa ra một cái công thức để tính tuổi dài thoòng như vầy:
= YEAR(NOW() - YEAR(Birthdate) - (DATE(YEAR(NOW()), MONTH(Birthdate), DAY(Birthdate)) > TODAY())
Đó là khi chưa biết đến hàm DATEDIF().
Bây giờ, với DATEDIF(), công thức trên chỉ ngắn như vầy thôi, mà ra kết quả vẫn chính xác:
= DATEDIF(Birthdate, TODAY(), "y")
Ví dụ, hôm nay là ngày 09/01/2007:
* Với ngày sinh là 05/01/1969 (đã tổ chức sinh nhật rồi), DATEDIF("05/01/1969", TODAY(), "y") = 39
* Nhưng với ngày sinh là 11/1/1969 (chưa tổ chức sinh nhật), DATEDIF("11/01/1969", TODAY(), "y") = 38
*Tìm một giờ, phút, giây nào đó tính từ lúc này
Như tôi đã nói ở trên, hàm TIME() sẽ tự động điều chỉnh kết quả của một giá trị thời gian khi những thông số trong hàm không hợp lý (giờ > 24, phút và giây > 60). Và do đó, khi cần tính toán hoặc tìm một giá trị thời gian nào đó kể từ lúc này (hoặc bất kỳ lúc nào), người ta thường sử dụng hàm TIME().
Ví dụ, công thức sau đây sẽ cho ra kết quả là thời gian vào 12 tiếng nữa kể từ lúc này:
= TIME(HOUR(NOW()) + 12, MINUTE(NOW()), SECOND(NOW()))
Không giống như hàm DATE(), bạn không thể đơn giản cộng thêm giờ, phút, hay giây trong hàm TIME(). Ví dụ công thức sau đây chỉ làm mỗi chuyện là tăng thêm 1 ngày vào ngày tháng năm và thời gian hiện tại:
= NOW() + 1
Nếu bạn muốn cộng thêm giờ, phút, hay giây vào một giá trị thời gian, bạn phải tính thời gian cộng thêm đó theo một tỷ lệ của một ngày. Ví dụ, bởi vì một ngày thì có 24 giờ, nên một giờ được tính như là 1/24. Cũng vậy, bởi vì một giờ thì có 60 phút, nên một phút sẽ được tính như là 1/24/60 (của một ngày). Và cuối cùng, bởi vì có 60 giây trong một phút, nên 1 giây trong một ngày sẽ được tính bằng 1/24/60/60.
*Tính tổng thời gian
Khi tính tổng thời gian, bạn nên phân biết hai trường hợp sau đây:
* Cộng thêm giờ, phút, giây: Ví dụ, bây giờ là 8 giờ, cộng thêm 2 tiếng nữa, là 10 giờ... Hoặc bây giờ là 23 giờ, cộng thêm 3 tiếng nữa là 2 giờ sáng (chớ không phải 26 giờ)... Nếu cộng kiểu này thì bạn cứ cộng bình thường, dùng hàm TIME() và nếu cần thì theo bảng hướng dẫn ở trên.
* Cộng tổng thời gian làm việc: Mỗi ngày tôi làm việc 18 tiếng, vậy hai ngày tôi làm mấy tiếng? là 36 tiếng. Nhưng nếu bạn dùng format bình thường dạng thời gian thì Excel nó sẽ tự quy ra (36-24) = 12:00... Để được kết quả là 36:00, bạn phải định dạng thời gian cho ô theo kiểu:
[h]:mm:ss (giờ nằm trong một cặp dấu ngoặc vuông)
Lấy lại định dạng ngày tháng
Đôi khi, bạn nhận được một cái database mà không hiểu vì một lý do gì đó, cell chứa ngày tháng năm nó như sau: 20070823,
nghĩa là nó được định dạng theo kiểu YYYYMMDD.
Trường hợp này, không thể dùng Format Cell của Excel để định dạng lại, mà bạn phải dùng đến hàm DATE(year, month, day)
và các hàm xử lý text, ví dụ hàm LEFT(), MID() và RIGHT() để lấy các trị số ngày, tháng, năm cho hàm DATE():
Tôi giả sử con số 20070823 này đang nằm ở cell A1, thì công thức LEFT(A1, 4) sẽ cho ta trị số của năm, MID(A1, 3, 2) sẽ cho trị số của tháng
và RIGHT(A1, 2) sẽ cho trị số của ngày.
Giả sử trong Control Panel của bạn định dạng ngày tháng năm theo kiểu dd/mm/yyyy, và cell nhận kết quả của bạn đã được định dạng theo kiểu ngày tháng năm,
bạn dùng hàm DATE(year, month, day) với 3 tham số là 3 công thức vừa làm ở trên, ta sẽ có:
=DATE(LEFT(A1, 4), MID(A1, 5, 2), RIGHT(A1, 2))→ 23/8/2007