Excel's Date and Time Functions - Các Hàm về Ngày Tháng và Thời Gian

[BNTT]

List of Excel's Date and Time Functions


Nhóm hàm về Ngày Tháng và Thời Gian (Date and Time Functions) giúp chúng ta chuyển đổi những giá trị ngày tháng và thời gian thành những con số để có thể tính toán với chúng.
Hệ thống ngày giờ trong Excel phụ thuộc vào cách thiết lập trong Regional Options của Control Panel.

Trong bài viết này, tôi dùng định dạng ngày tháng theo kiểu Việt Nam: ngày / tháng / năm (dd/mm/yyyy).



Danh mục các hàm về ngày tháng và thời gian

DATE (year. month, day) : Trả về các số thể hiện một ngày cụ thể nào đó. Nếu định dạng của ô là General trước khi hàm được nhập vào, kết quả sẽ được thể hiện ở dạng ngày tháng năm.

DATEVALUE (date_text) : Trả về số tuần tự của ngày được thể hiện bởi date_text (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).

DAYhttp://www.giaiphapexcel.com/forum/showpost.php?p=50280&postcount=5 (serial_number) : Trả về phần ngày của một giá trị ngày tháng, được đại diện bởi số tuần tự. Kết quả trả về là một số nguyên từ 1 đến 31.

DAYS360http://www.giaiphapexcel.com/forum/showpost.php?p=51138&postcount=15 (start_date, end_date, method) : Trả về số ngày giữa hai ngày dựa trên cơ sở một năm có 360 ngày (12 tháng, mỗi tháng có 30 ngày) để dùng cho các tính toán tài chính.

EDATEhttp://www.giaiphapexcel.com/forum/showpost.php?p=50892&postcount=9 (start_date, months) : Trả về số tuần tự thể hiện 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.

EOMONTHhttp://www.giaiphapexcel.com/forum/showpost.php?p=50908&postcount=10 (start_date, months) : Trả về số tuần tự thể hiện ngày cuối cùng của một tháng 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.

HOURhttp://www.giaiphapexcel.com/forum/showpost.php?p=51396&postcount=19 (serial_number) : Trả về phần giờ của một giá trị thời gian. Kết quả trả về là một số nguyên từ 0 đến 23.

MINUTEhttp://www.giaiphapexcel.com/forum/showpost.php?p=51396&postcount=19 (serial_number) : Trả về phần phút của một giá trị thời gian. Kết quả trả về là một số nguyên từ 0 đến 59.

MONTHhttp://www.giaiphapexcel.com/forum/showpost.php?p=50280&postcount=5 (serial_number) : Trả về phần tháng của một giá trị ngày tháng, được đại diện bởi số tuần tự. Kết quả trả về là một số nguyên từ 1 đến 12.

NETWORKDAYShttp://www.giaiphapexcel.com/forum/showpost.php?p=51133&postcount=14 (start_date, end_date, holidays) : Trả về tất cả số ngày làm việc trong một khoảng thời gian giữa start_date và end_date, không kể các ngày cuối tuần và các ngày nghỉ (holidays).

NOWhttp://www.giaiphapexcel.com/forum/showpost.php?p=50061&postcount=2 () : Trả về số tuần tự thể hiện ngày giờ hiện tại. Nếu định dạng của ô là General trước khi hàm được nhập vào, kết quả sẽ được thể hiện ở dạng ngày tháng năm và giờ phút giây.

SECONDhttp://www.giaiphapexcel.com/forum/showpost.php?p=51396&postcount=19 (serial_number) : Trả về phần giây của một giá trị thời gian. Kết quả trả về là một số nguyên từ 0 đến 59.

TIME (hour, minute, second) : Trả về phần thập phân của một giá trị thời gian (từ 0 đến nhỏ hơn 1). Nếu định dạng của ô là General trước khi hàm được nhập vào, kết quả sẽ được thể hiện ở dạng giờ phút giây.

TIMEVALUE (time_text) : Trả về phần thập phân của một giá trị thời gian (từ 0 đến nhỏ hơn 1) thể hiện bởi time_text (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).

TODAY () : Trả về số tuần tự thể hiện ngày tháng hiện tại. Nếu định dạng của ô là General trước khi hàm được nhập vào, kết quả sẽ được thể hiện ở dạng ngày tháng năm.

WEEKDAY (serial_number, return_type) : Trả về thứ trong tuần tương ứng với ngày được cung cấp. Kết quả trả về là một số nguyên từ 1 đến 7.

WEEKNUM (serial_number, return_type) : Trả về một số cho biết tuần thứ mấy trong năm.

WORKDAY (start_day, days, holidays) : Trả về một số tuần tự thể hiện số ngày làm việc, có thể là trước hay sau ngày bắt đầu làm việc và trừ đi những ngày cuối tuần và ngày nghỉ (nếu có) trong khoảng thời gian đó.

YEARhttp://www.giaiphapexcel.com/forum/showpost.php?p=50280&postcount=5 (serial_number) : Trả về phần năm của một giá trị ngày tháng, được đại diện bởi số tuần tự. Kết quả trả về là một số nguyên từ 1900 đến 9999.

YEARFRAChttp://www.giaiphapexcel.com/forum/showpost.php?p=51145&postcount=16 (start_date, end_date, basis) : Trả về tỷ lệ của một khoảng thời gian trong một năm.

​

Xem thêm: Working with Date and Time Functions​

 

[BNTT]

Nhóm Hàm trả về một ngày


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 ô.
​

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.

Xem thêm: Working with Date and Time Functions​

 

[BNTT]

Nhóm Hàm dùng để trả về kết quả là một ngày


Hàm DATE()

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)

Xem thêm: Working with Date and Time Functions​

 

[BNTT]

Nhóm Hàm dùng để trả về kết quả là một ngày


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​

​

Xem thêm: Working with Date and Time Functions​

 

[BNTT]

Nhóm Hàm dùng để trả về kết quả là một phần của ngày tháng

Ngày tháng năm, đương nhiên là có 3 phần: Ngày, Tháng và Năm
Khi cần trích ra riêng giá trị Ngày, hoặc Tháng, hoặc Năm, chúng ta dùng 3 hàm sau đây:


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​

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​

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​

Xem thêm: Working with Date and Time Functions​

 

[BNTT]

Nhóm hàm làm việc với Tuần Lễ


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​

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​

Xem thêm: Working with Date and Time Functions​

 

[BNTT]

Tìm một ngày, một tháng, một năm nào đó tính từ hôm nay

Nếu biết kết hợp những hàm tôi đã trình bày ở trên, bạn sẽ thấy rất dễ dàng để xác định một ngày, một tháng, một năm nào đó tính từ hôm nay (hoặc từ bất kỳ một ngày nào)

Ví dụ, bạn muốn biết Quốc Khánh năm sau là thứ mấy ?

= WEEKDAY(DATE(2008, 9, 2)) = 3 (thứ Ba)​

Năm 2008 mới được có mấy ngày, có nhiều người chưa kịp sửa thói quen, vẫn dùng 2007 (!),
vậy thì dùng công thức này, xác định luôn số năm là năm nay (cho chắc ăn):

= WEEKDAY(DATE(YEAR(TODAY()), 9, 2)) = 3 (thứ Ba)​

Ví dụ khác, hôm nay là ngày 04/01/2008, 34 ngày nữa là ngày nào:

= DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) + 34) = 07/02/2008 (Mồng Một Tết Mậu Tý)​

Nói cho vui thôi, cái công thức trên dài lắm (thế nào cũng có người cười), đó là tôi ví dụ cách kết hợp hàm, còn nếu chỉ cần biết 34 ngày nữa là ngày nào, ta chỉ cần công thức này:

= TODAY() + 34​

Xem thêm: Working with Date and Time Functions​

 

[BNTT]

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"})
​

Xem thêm: Working with Date and Time Functions​

 

[BNTT]

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.

​

Ô 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
​

Xem thêm: Working with Date and Time Functions​

 

[BNTT]

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

​

Xem thêm: Working with Date and Time Functions​

 

[BNTT]

Xác định một ngày dựa vào tuần và thứ

Có những ngày lễ trong năm trong rơi vào một ngày nhất định, mà nó tùy thuộc vào ngày thứ mấy trong một tuần lễ nào đó của một tháng. Ví dụ, ở Mỹ, Ngày Lao Động (Labor Day) luôn là ngày thứ Hai (Monday) của tuần thứ nhất của tháng 9, đó là ngày mấy của tháng 9 năm nay? Hoặc, nếu bạn là người Công Giáo, có thể bạn biết Chủ Nhật Truyền Giáo là Chủ Nhật thứ hai của tháng 10, nhưng bạn không biết năm nay nó là ngày mấy của tháng 10...

Ở Việt Nam, thì hình như không có ngày lễ nào tính theo kiểu như vậy. Tuy nhiên, có thể có lúc nào đó, bạn có dự tính sẽ đi Nha-Trang chơi vào Chủ Nhật cuối tháng của tháng 6 chẳng hạn, bạn muốn biết hôm đó sẽ ngày bao nhiêu...

Giở lịch ra thì dễ rồi, nhưng dùng Excel được không? Chúng ta cùng thử nhé.

Chúng ta đã có Tháng và Năm rồi, vậy thử dùng ngày đầu tháng để tính tới thêm bao nhiêu ngày. Tôi sẽ dùng công thức có dạng sau:

= DATE(Năm, Tháng, 1) + số ngày cộng thêm
​

Vấn đề là ở chỗ tính cho ra số ngày cộng thêm này. Ta sẽ dựa vào Thứ (weekday).

Một tuần thì có 7 ngày. Ở trên, ta dùng ngày 1 của tháng để làm mốc tính, vậy ta sẽ xác định xem cái Thứ của ngày cần tìm thì lớn hơn hay nhỏ hơn Thứ của ngày 1 (tôi dùng "lớn hơn" hay "nhỏ hơn" là ý muốn dùng số, ví dụ thứ Năm thì nhỏ hơn thứ Bảy nhưng lớn hơn thứ Ba); sau đó dựa vào số Thứ chênh lệch này để tìm ra số ngày cần phải cộng thêm, tính từ ngày 1.

• Nếu Thứ của ngày cần tìm nhỏ hơn Thứ của ngày 1 (của tháng), ta sẽ lấy 7 trừ đi Thứ của ngày 1 rồi cộng thêm Thứ của ngày cần tìm:

7 - WEEKDAY(DATE(Năm, Tháng, 1)) + Thứ
​

Chữ Thứ in nghiêng ở trên là con số chỉ Thứ của ngày ta cần tìm.
Ví dụ, muốn tìm ngày thứ Hai trong tuần đầu tiên của tháng 9 năm nay, thì công thức trên sẽ là:​

7 - WEEKDAY(DATE(2008, 9, 1)) + 2
​

• Nếu Thứ của ngày cần tìm lớn hơn Thứ của ngày 1 (của tháng), ta sẽ lấy Thứ của ngày cần tìm trừ đi Thứ của ngày 1:

Thứ - WEEKDAY(DATE(Năm, Tháng, 1))
​

Cũng ví dụ trên, muốn tìm ngày thứ Hai trong tuần đầu tiên của tháng 9 năm nay, thì công thức này sẽ là:​

2 - WEEKDAY(DATE(2008, 9, 1))

​

Số ngày cộng thêm sẽ là kết quả có được của một trong hai cái "Nếu" mà tôi vừa trình bày. Chúng ta sẽ dùng IF() để viết công thức cho hai trường hợp "Nếu" đó, và công thức đầu tiên của bài này bây giờ sẽ có dạng như sau (tôi xuống hàng để dễ nhìn):

= DATE(Năm, Tháng, 1)
+ IF(Thứ < WEEKDAY(DATE(Năm, Tháng, 1)), 7 - WEEKDAY(DATE(Năm, Tháng, 1)) + Thứ, Thứ - WEEKDAY(DATE(Năm, Tháng, 1)))
​

Và đây là công thức để tính ngày thứ Hai trong tuần đầu tiên của tháng 9 năm nay là ngày mấy:

= DATE(2008, 9, 1) + IF(2 < WEEKDAY(DATE(2008, 9, 1)), 7 - WEEKDAY(DATE(2008, 9, 1)) + 2, 2 - WEEKDAY(DATE(2008, 9, 1)))
​

Xong rồi ? Chưa, bạn à. Công thức trên chỉ đúng cho ngày cần tìm nằm trong tuần đầu tiên của tháng thôi. Tôi đang làm cho ví dụ tìm ngày thứ Hai trong tuần đầu tiên của tháng 9 năm nay mà...

Tuần thứ hai cộng thêm 7 ngày nữa, tuần thứ ba cộng thêm 14 ngày nữa... Vậy số ngày cần cộng thêm tuần thứ n sẽ là: (n - 1) * 7

Rồi đó, công thức của chúng ta đây:

= DATE(Năm, Tháng, 1)
+ IF(Thứ < WEEKDAY(DATE(Năm, Tháng, 1)), 7 - WEEKDAY(DATE(Năm, Tháng, 1)) + Thứ, Thứ - WEEKDAY(DATE(Năm, Tháng, 1)))
+ (n - 1) * 7
​

Với Năm, Tháng là tháng và năm cần tính, Thứ, tuần thứ n là những cái đã biết.

Tôi thiết kế trong Excel một cái bảng để tính như sau.

​

Công thức vừa làm xong tôi bỏ trong ô B7 (ô đã được định dạng theo kiểu "dddd, dd mmmm, yyyy"):

= 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
​

Có hai cái công thức trong C2 và C4, dùng hàm CHOOSE() để minh họa cho con số trong cột B tương ứng. Xin xem lại ở đây.

Trong hình, tôi đang tính thử xem Chủ Nhật của tuần thứ ba của tháng Sáu năm nay là ngày mấy, và tôi có kết quả là ngày 15.

Sẵn đó, tôi tìm luôn Chủ Nhật cuối tháng 6 là ngày mấy, tôi nhập B1 = 4, B2 = 1, B3 = 6, B4 = 2008, kết quả là ngày Chủ Nhật 22/6/2008.
Hình như chưa phải Chủ Nhật cuối tháng, thì thay B1 = 5, nó ra Chủ Nhật 29/6/2008

Có cái này vui lắm, tôi tăng luôn ô B1 lên, bằng 8 (ý nói tuần thứ 8 của tháng 6 !), thử xem Excel nó cho ra bao nhiêu. Kết quả là: Chủ Nhật 20/7/2008... Công thức này cũng hay đấy chứ, tự động tính lên, chứ không báo lỗi...​

 

[BNTT]

Tính toán với ngày tháng năm

Chúng ta đã biết rằng, Excel quan niệm ngày tháng năm là một dãy những con số liên tục, với 1 = ngày 1/1/1900.
Cho nên một trong những cách dễ dàng nhất để tính số ngày giữa hai khoảng thời gian là lấy ngày này trừ đi ngày kia, còn kết quả hiện ra thế nào phụ thuộc vào định dạng của ô nhận kết quả (tốt nhất là định dạng số hoặc General, vì kết quả phải là một con số thì mới có lý):

= Date1 - Date2​

Bên cạnh đó, còn có cách sử dụng những hàm ngày tháng của Excel:

• Tính tuổi (1):

Nếu bạn có một ngày sinh nhật trong một ô đã được đặt tên là Birthdate, bạn sẽ nghĩ rằng công thức để tính tuổi đơn giản như sau:

= YEAR(NOW() - YEAR(Birthdate)​

Nhưng... nếu tính đủ tuổi nghĩa là phải đủ tháng, đủ năm, đủ ngày, thì công thức trên chỉ đúng với người nào đã tổ chức sinh nhật rồi trong năm nay, chứ nếu chưa đến sinh nhật của họ, thì kết quả nhận được sẽ lớn hơn giá trị thực của nó.

Ví dụ, hôm nay là ngày 08/1/2008, đồng ý rằng 2008 - 1980 = 28, nhưng nếu nói bạn nào đó sinh ngày 30/12/1980 đã 28 tuổi thì... cũng hơi tội, phải không ? Em mới có 27 tuổi mấy ngày thôi, các bác à! Và, các bác bán bảo hiểm nhân thọ cũng nghĩ như thế đó!

Hóa ra không đơn giản nhỉ!
Để tính tuổi chính xác, chúng ta làm một biểu thức logic để so sánh thử xem ngày sinh nhật với ngày hôm nay, ngày nào lớn hơn:

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

Biểu thức trên sẽ cho ra kết quả là TRUE nếu đúng và FALSE nếu sai.
TRUE, nghĩa là ngày sinh nhật "lớn hơn" hôm nay, hay có nghĩa là chưa tới ngày sinh nhật; và FALSE là ngược lại.

Và chúng ta cũng đã biết rằng, trong Excel, TRUE tương đương với giá trị 1, và FALSE thì tương đương với giá trị 0.
Lợi dụng chính điểm này, ta sẽ ghép biểu thức trên vào công thức lấy năm trừ năm đã làm hồi nãy:

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

Nghĩa là, nếu sinh nhật đã qua, thì lấy năm nay trừ đi năm sinh ra là đúng, còn không thì phải trừ đi 1. Ai bảo lập công thức tính tuổi là đơn giản nhỉ!​

​

 

[BNTT]

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().
Nhưng có một điều tôi không hiểu là: hàm này dùng tốt, nhưng không hề có trong danh sách hàm của Excel (Excel 2007 cũng không), và cũng không có một cái help nào cho nó cả! Cho nên, có một số người sử dụng Excel phải nói là có thâm niên, mà vẫn không hề biết Excel có 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)
​

 

[BNTT]

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/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"})

​

 

[BNTT]

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
​

 

[BNTT]

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...):


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%
​

 

[BNTT]

Tính giờ phút hiện hành

Không có hàm nào để trả về kết quả là giờ phút giây hiện hành. Nhưng có cách:

Ở trên tôi có nói tới hàm TODAY(), là hàm trả về ngày tháng năm hiện hành, trong đó, thời gian được xem như là 0 (nửa đêm)

Và hàm NOW(), cũng trả về ngày tháng năm hiện hành, nhưng có thêm giờ phút giây nữa.

Vậy, chỉ việc lấy hàm NOW() trừ bớt đi ngày tháng năm là ra ngay thôi:

= NOW() - TODAY()​

 

[BNTT]

Tương tự hàm DATE() và DATEVALUE(), trả về kết quả là một giá trị ngày tháng, để tính thời gian, có hai hàm TIME() và TIMEVALUE(), trả về kết quả là một giá trị thời gian


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)​

 

[BNTT]

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:


Hàm HOUR()

Cho biết số chỉ giờ trong một giá trị thời gian

Cú pháp: = HOUR(******_number)

******_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(******_number)

******_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)
​

Hàm SECOND()

Cho biết số chỉ giây trong một giá trị thời gian

Cú pháp: = SECOND(******_number)

******_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)
​

 

[BNTT]

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.

Bảng sau đây sẽ hướng dẫn cách cộng thêm n giờ, phút, hay giây vào một giá trị thời gian:

​

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)
​