View Full Version:
Trả về một ngày tháng năm nào đó
Cú pháp: = DATE(year, month, day)
year: Số chỉ năm
Con số này có thể là 1 đến 4 ký số.
– Nếu nhỏ hơn 1900, Excel sẽ tự động cộng thêm 1900 vào để tính (ví dụ year = 100 thì Excel sẽ hiểu đó là năm 2000)
– Nếu từ 1900 đến 9999, thì Excel sẽ coi đó chính là năm cần tính
– Nếu nhỏ hơn 0 hoặc lớn hơn 10.000, Excel sẽ báo lỗi #NUM!
month: Số chỉ tháng
Nếu con số này lớn hơn 12, thì Excel sẽ tự động quy đổi thành 12 bằng 1 năm và tăng số năm lên.
day: Số chỉ ngày
Nếu con số này lớn hơn số ngày của tháng, thì Excel sẽ tự động quy đổi thành số ngày nhiều nhất của tháng cho phù hợp và tăng số tháng lên, nếu cần thì tăng cả số năm lên luôn.
Ví dụ:
DATE(2007, 12, 25) = Giáng Sinh năm 2007
DATE(2007, 12, 32) = 01/01/2008
DATE(2007, 13, 25) = 25/01/2008
DATE(7, 25, 32) = 01/02/1909
(số ngày (date) = 32, lớn hơn số ngày nhiều nhất của một tháng (31), do đó, Excel sẽ lấy ngày là 01, và tăng số tháng (month) thêm 1; số tháng (month) = 25 + 1 = 26 = 2 + (2 x 12), do đó Excel sẽ lấy tháng là 02, và tăng số năm thêm 2; số năm (year) = 7 + 2 = 9, Excel sẽ cộng thêm 1900 = 1909)
Hàm DATE() rất hữu dụng khi year, month, day 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
Khi nhập hàm DATE(), bạn phải cẩn thận thứ tự year, month, day, vì nó rất dễ nhầm lẫn (theo kiểu VN chúng ta: ngày, tháng, năm)
2. Hàm DATEVALUE()
Chuyển đổi một chuỗi văn bản có dạng ngày tháng năm thành một giá trị ngày tháng năm để có thể tính toán được
Cú pháp: = DATEVALUE(date_text)
– date_text: Chuỗi văn bản cần chuyển đổi
– date_text có giới hạn trong khoảng từ 01/01/1900 đến 31/12/9999, nếu nằm ngoài khoảng này, hàm sẽ báo lỗi #VALUE!
– date_text phải được nhập trong cặp dấu móc kép (“”)
– Nếu date_text chỉ có hai phần, Excel sẽ hiểu như sau: nếu phần sau là một giá trị < 13 và phần đầu là một giá trị < 32, nó xem như phần đầu là ngày, phần sau là tháng, và lấy năm hiện hành làm giá trị để tính năm; còn nếu phần sau là một giá trị > 12 và phần đầu là một giá trị < 13, nó xem như phần đầu là tháng, phần sau là năm, và cho giá trị tính ngày là 1.
Ví dụ:
DATEVALUE(“25/12/2007”) = 39441 (= 25/12/2007)
DATEVALUE(“25/12”) = 39807 (= 25/12/2008)
DATEVALUE(“12/25”) = 45992 (= 01/12/2025)
DATEVALUE(“12/25/2007”) = #VALUE!
DATEVALUE(“25 December 2009”) = 40172 = 25/12/2009
3. Hàm YEAR()
Cho biết số chỉ năm trong một giá trị ngày tháng
Cú pháp: = YEAR(serial_number)
serial_number: Biểu thức ngày tháng hoặc là một con số chỉ giá trị ngày tháng
Ví dụ: YEAR(TODAY()) = 2008
4. Hàm MONTH()
Cho biết số chỉ tháng trong một giá trị ngày tháng
Cú pháp: = MONTH(serial_number)
serial_number: Biểu thức ngày tháng hoặc là một con số chỉ giá trị ngày tháng
Ví dụ: MONTH(TODAY()) = 1
5. Hàm DAY()
Cho biết số chỉ ngày trong một giá trị ngày tháng
Cú pháp: = DAY(serial_number)
serial_number: Biểu thức ngày tháng hoặc là một con số chỉ giá trị ngày tháng
Ví dụ: DAY(TODAY()) = 3
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.
6. 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
Vấn đề với việc cộng trừ tháng
Đôi khi, chúng ta muốn tính một ngày trước hay sau vài tháng nữa, lấy số tháng cộng trừ, dùng hàm DATE() chẳng hạn ?
Vấn đề là: số ngày trong mỗi tháng đâu có bằng nhau, khi cộng trừ theo tháng, Excel sẽ tự động tính số ngày trong mỗi tháng cho phù hợp, và như thế, nhiều lúc không ra cái chúng ta mong muốn.
Tôi ví dụ, ô A1 đang chứa ngày 31/1/2008, có bạn đã dùng công thức sau để tính ngày cuối tháng của tháng 4, với mong muốn kết quả cho ra ngày 30/4/2008:
= DATE(YEAR(A1), MONTH(A1) + 3, DAY(A1))
Mới xem qua, thì hợp lý chứ… lấy tháng 1 cộng thêm 3 tháng nữa. Nhưng, nếu làm như vậy thì kết quả sẽ ra là ngày 31/4/2008, mà tháng 4 có 30 ngày thôi, nên Excel sẽ tự động cộng thêm 1 ngày sau ngày 30/4/2008, và kết quả của công thức trên sẽ là ngày 01/5/2008.
Vậy thì làm sao, ví dụ như để tìm ngày cuối tháng của một tháng nào đó?
Chúng ta cùng xem hai hàm sau đây (cũng trong nhóm Analysis Toolpak): EDATE() và EOMONTH()
7. Hàm EDATE()
Hàm này trả về một ngày nào đó tính từ mốc thời gian cho trước và cách mốc thời gian này một số tháng được chỉ định
Thường người ta dùng hàm này để tính hạn bảo hành cho một sản phẩm, hoặc ngày đáo hạn hợp đồng…
Cú pháp: = EDATE(start_date, months)
start_date: Ngày dùng làm mốc để tính. Nên nhập ngày này 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.
months: Số tháng trước hoặc sau mốc thời gian start_date (nếu trước thì dùng số âm). Trong trường hợp months là số không nguyên, nó sẽ được làm tròn.
Xin lấy lại ví dụ đã nói ở đầu bài: ô A1 đang chứa ngày cuối tháng 01/2008, tính ngày cuối tháng sau 3 tháng nữa, dùng công thức:
= EDATE(A1, 3) = 30/4/2008
Lưu ý: EDATE() không phải là hàm để tính ngày cuối tháng, mà cho kết quả là ngày trùng với ngày của mốc thời gian muốn tính (start_date). Nếu như trường hợp kết quả trả về là một ngày không hợp lệ của một tháng (ngày 31/4 chẳng hạn), thì EDATE() sẽ lấy ngày cuối tháng của tháng đó (30/4).
Ví dụ khác: Tôi mua một cái USB ngày hôm nay (08/01/2008), hạn bảo hành 36 tháng, vậy nó được bảo hành tới ngày nào?
= EDATE(TODAY(), 36) = 08/01/2011
8. Hàm EOMONTH()
Xin dịch ra để dễ hiểu: End Of Month = Ngày cuối tháng
Cú pháp: = EOMONTH(start_date, months)
start_date: Ngày dùng làm mốc để tính. Cũng giống hàm EDATE(), nên nhập ngày này 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.
months: Số tháng trước hoặc sau mốc thời gian start_date (nếu trước thì dùng số âm). Trong trường hợp months là số không nguyên, nó sẽ được làm tròn.
Ví dụ: EOMONTH(TODAY(), 25) = 40237 = 28/02/2010
Tính ngày cuối tháng của một tháng bất kỳ
Hàm EOMONTH() ở trên cho biết ngày cuối tháng của một tháng nào đó trong tương lai hoặc trong quá khứ.
Bây giờ, nếu bạn có một dữ liệu ngày tháng năm, và bạn muốn biết ngày cuối tháng của tháng trong cái dữ liệu đó (28, 29, 30, hay 31), thì làm sao?
Chúng ta đã biết rằng hàm DATE() sẽ tự động sửa ngày, tháng, năm thành một ngày hợp lệ, tùy vào các thông số ngày, tháng, năm chúng ta nhập trong công thức. Đây là một cái “mánh” của tôi:
Vì ngày cuối tháng bao giờ cũng là ngày trước ngày đầu tiên của tháng kế tiếp, ta cứ cho thông số ngày trong hàm DATE() là 0, và tăng số tháng muốn tính thêm 1; mà làm gì có ngày 0, nên chắc chắn DATE() sẽ cho ra kết quả là ngày cuối tháng của tháng trước tháng bị cộng 1, tức là ngay tháng ta muốn tính!
Và đây là công thức để tính ngày cuối tháng của một ngày tháng năm nào đó (MyDate)
= DATE(YEAR(MyDate), MONTH(MyDate) + 1, 0)
Ví dụ:
Today = 08/01/2008
= DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 0) = 31/01/2008
Năm nay có nhuận không?
= DATE(YEAR(“01/02/2008”), MONTH(“01/02/2008”) + 1, 0) = 29/02/2008: Nhuận (!)
Trích ra từng giá trị của thời gian
Thời gian có 3 phần: Giờ, Phút và Giây. Khi cần trích ra riêng từng giá trị này, chúng ta dùng 3 hàm sau đây:
9. 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ờ)
10. 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)
11. 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)
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!
12. 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/2007 và ngày 10/1/2008, trong đó có nghỉ ngày Noel (25/12) và ngày Tết Tây (1/1):
= NETWORKDAYS(“01/12/2007”, “10/01/2008”, {“12/25/2007”, “1/1/2008”})
13. Hàm TODAY()
Trả về ngày tháng năm hiện hành
Cú pháp: = TODAY()
Hàm này không có thông số nào kèm theo cả.
Kết quả của hàm TODAY() là một con số, đại diện cho ngày tháng năm hiện hành, với thời gian được ngầm hiểu là 0 giờ (nửa đêm).
Ví dụ, hôm nay là ngày 02/01/2008, hàm TODAY() sẽ cho ra con số 39449.0, để xem được theo dạng “dễ nhìn”, bạn chỉ cần định dạng lại ô.
14. Hàm NOW()
Trả về ngày tháng năm và giờ phút giây hiện hành
Cú pháp: = NOW()
Hàm này cũng không có thông số nào kèm theo cả.
Kết quả của hàm NOW() là một con số, đại diện cho ngày tháng năm và thời gian của hệ thống lúc vừa nhập xong công thức.
Ví dụ, hôm nay, và lúc này là 11 giờ 56 phút ngày 09/01/2008, hàm NOW() sẽ cho ra con số 39456.49643. Để xem được theo dạng “dễ nhìn”, bạn chỉ cần định dạng lại ô, theo kiểu dd/mm/yyyy hh:ss:mm chẳng hạn.
Ghi chú:
– Không phải lúc nào hàm TODAY() và NOW() cũng cho ra một kết quả như nhau, mà mỗi khi bạn sửa chữa bảng tính, gõ 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… thì hàm TODAY() và NOW() sẽ cập nhật theo ngày giờ của hệ thống.
Sự khác nhau giữa hai hàm này chính là:
– TODAY nghĩa là hôm nay, mà lúc 7 giờ sáng hay 9 giờ 30 tối thì cũng là hôm nay. Kết quả của TODAY() vào những lúc khác nhau trong ngày sẽ giống nhau.
– NOW nghĩa là chính lúc này, là lúc ta vừa nhập xong NOW() và nhấn Enter… Kết quả của NOW() vào những lúc khác nhau trong ngày sẽ khác nhau.
15. 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
16. 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)
17. Hàm WEEKDAY()
Cho biết số thứ tự của ngày trong tuần
Cú pháp: = WEEKDAY(serial_number [, return_type])
serial_number: Biểu thức ngày tháng hoặc là một con số chỉ giá trị ngày tháng
return_type: Chọn kiểu kết quả trả về
return_type = 1 (mặc định): Chủ Nhật là 1 (thứ Bảy là 7)
return_type = 2: Thứ Hai là 1 (Chủ Nhật là 7)
return_type = 3: Thứ Hai là 0 (Chủ Nhật là 6)
Ví dụ: (Today = 03/01/2008)
WEEKDAY(TODAY()) = 5
WEEKDAY(TODAY(), 2) = 4
WEEKDAY(TODAY(), 3) = 3
18. Hàm WEEKNUM()
Cho biết số thứ tự của tuần trong năm
Cú pháp: = WEEKNUM(serial_number [, return_type])
serial_number: Biểu thức ngày tháng hoặc là một con số chỉ giá trị ngày tháng
return_type: Chọn kiểu trả về (tùy thuộc vào cách chọn ngày đầu tiên trong tuần)
return_type = 1 (mặc định): Chủ Nhật là ngày đầu tuần
return_type = 2: Thứ Hai là ngày đầu tuần
Ví dụ: Thử xem ngày hạnh phúc của ANHPHUONG nằm vào tuần nào trong năm nay…
WEEKNUM(“06/01/2008”) = 2
WEEKNUM(“06/01/2008”, 2) = 1
Dự đoán ngày sẽ hoàn thành một công việc
Giả sử chúng ta có một dự án, theo dự tính sẽ hoàn thành trong một thời gian nào đó (tính bằng ngày), nhưng khoảng thời gian này, bình thường sẽ bao gồm cả số ngày làm việc, số ngày cuối tuần, và cả số ngày lễ nghỉ… Vấn đề là phải dự đoán được ngày nào sẽ hoàn thành dự án này, mà đã trừ bớt đi những ngày nghỉ, không làm…
Bạn có thể làm điều đó, bằng cách dùng hàm WORKDAY(), một hàm thuộc nhóm Analysis Toolpak (phải cài Add-in này thì mới sử dụng được), hàm này trả về số ngày làm việc trước hoặc sau ngày được chỉ định, và trừ đi những ngày nghỉ.
19. Hàm WORKDAY()
Cú pháp: = WORKDAY(start_day, days [, holidays])
start_day: Ngày làm mốc để tính.
days: Số ngày làm việc trước hoặc sau start_day. Dùng days > 0 cho số ngày làm việc của một dự án chưa hoàn thành, dùng days < 0 cho số ngày làm việc của một dự án đã kết thúc.
holidays: Danh sách các ngày nghỉ. Có thể gõ trực tiếp một ngày cụ thể, trong trường hợp có nhiều ngày thì các ngày cách nhau bằng dấu phẩy, và đặt tất cả trong một cặp dấu móc {}.
Ví dụ, để tính số ngày làm việc cho một dự án 30 ngày. tính từ hôm nay, ta dùng công thức:
= WORKDAY(TODAY(), 30)
Tính ngày hoàn thành của một dự án 30 ngày, khởi công ngày 1/12/2007, trong đó nghỉ ngày Noel (25/12) và ngày đầu năm (01/01/2008), dùng công thức:
= WORKDAY(“1/12/2007”, 30, {“25/12/2007”, “1/1/2008”})
Tính tỷ lệ của một khoảng thời gian so với một năm
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…):
20. 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/2007 và ngày 30/7/2007 so với 1 năm:
YEARFRAC(“15/3/2007”, “30/7/2007”) = 37%
Learn to be done 