KẾ TOÁN MINH VIỆT - Dịch Vụ Kế Toán - Đào Tạo Kế Toán

Các hàm Excel Date & Time: EDATE, YEARFRAC, EOMONTH, NETWORKDAYS.INTL

Các hàm Excel Date & Time tôi đang đề cập ở đây — EDATE, YEARFRAC, EOMONTH và NETWORKDAYS.INTL — là bốn trong số nhiều hàm được sử dụng để đếm ngày. Đối với mỗi hàm được liệt kê dưới đây, tôi sẽ định nghĩa nó trước, sau đó hiển thị các đối số của hàm, là các giá trị mà các hàm sử dụng để thực hiện các phép tính. Sau đó, tôi sẽ hiển thị một mẫu cú pháp của hàm - cách sắp xếp công thức, bao gồm tên, dấu ngoặc đơn, dấu phẩy và các đối số của hàm.

Lưu ý rằng các đối số luôn được bao quanh bởi dấu ngoặc đơn và các đối số riêng lẻ được phân tách bằng dấu phẩy.

EDATE ()

EDATE là một hàm thực tế để trả về một số tháng trong tương lai hoặc quá khứ, bằng cách sử dụng một giá trị dương cho các ngày trong tương lai, một giá trị âm cho các ngày trong quá khứ. Ví dụ: bạn có thể sử dụng hàm này để tính ngày nghỉ hưu hoặc ngày hết hạn, để tính tuổi của người nào đó từ ngày sinh hoặc để thêm số năm nhất định vào ngày được chỉ định.

Các đối số cho hàm này là:

start_date: chỉ định ngày đại diện cho ngày bắt đầu (phải ở định dạng số sê-ri Excel hợp lệ ).

tháng: số tháng trước hoặc sau ngày bắt đầu

Cú pháp trông giống như sau: = EDATE (ngày bắt đầu, tháng)

1. Đối với ví dụ này, chúng ta hãy tính toán một số ngày nghỉ hưu cho các đồng nghiệp của chúng tôi tại nơi làm việc. Mở một trang tính trống và nhập tên trường sau làm tiêu đề cột trong ô A3, B3, C3, D3 và E3 (tương ứng): Tên, Ngày sinh, Ngày nghỉ, Thời gian còn lại và Năm.

12 câu lệnh if bạn nên biết

download bài tập excel cơ bản

2. Nhập một số tên và ngày sinh trong cột A và B.

3. Nhấp vào ô C4, sau đó chuyển đến Công thức > Ngày & Giờ và chọn EDATE từ danh sách. Bạn cũng có thể nhập công thức EDATE () theo cách thủ công trong ô C4: = EDATE (B4,12 * 62).

Lưu ý: Nếu bạn không muốn tính toán và nhập số tháng cụ thể, thì việc nhân lên gấp 12 lần tuổi nghỉ hưu sẽ dễ dàng hơn nhiều (tức là 12 tháng mỗi năm trong độ tuổi 62 năm).

4. Sao chép công thức từ ô C4 sang các ô từ C5 đến C13 và bạn nhận được ngày mà mỗi cá nhân có thể nghỉ hưu.

YEARFRAC ()

Hàm này tính toán số ngày giữa hai ngày dưới dạng số thập phân, đại diện cho một phân số. Chức năng này đặc biệt hữu ích vì các hàm Excel Date & Time khác chỉ trả về một số nguyên. Sử dụng công thức này để tính ngày nghỉ hưu, để lấy tuổi của một người từ ngày sinh, tính năm giữa các ngày, để xác định phần trăm của năm đã hoàn thành (cho đến nay), v.v.

Lưu ý: Excel sử dụng toàn bộ ngày giữa hai ngày để tính phần thập phân của một năm dưới dạng giá trị thập phân.

Các đối số cho hàm này là:

start_date: ngày bắt đầu

end_date: ngày kết thúc

cơ sở (tùy chọn): loại cơ sở đếm ngày (hoặc mã) để sử dụng (xem biểu đồ bên dưới)

Cú pháp trông giống như sau: = YEARFRAC (start_date, end_date, base)

Ghi chú: Mặc dù Excel hiện cho phép dấu cách giữa các đối số, bạn vẫn không thể sử dụng dấu cách giữa hàm và dấu ngoặc mở. Hầu hết các bộ tính giờ cũ chỉ xóa tất cả các khoảng trống khỏi công thức của chúng để tránh bất kỳ khả năng nào xảy ra lỗi như vậy.

Nhập công thức này vào ô D4: = YEARFRAC (TODAY (), C4,1). Ngày bắt đầu trong công thức này là TODAY () - trái với ngày thực tế. Hàm TODAY () trả về ngày hôm nay. Ngày trong ô C4 là ngày kết thúc và cơ sở tùy chọn là 1, có nghĩa là ngày thực tế trong các tháng và ngày thực tế trong các năm.

Điều này có vẻ khó hiểu, nhưng Excel cung cấp năm tùy chọn để tính toán ngày và năm trong công thức này. Kế toán châu Âu, người giữ sách và một số kế toán viên Mỹ làm việc trong các tháng 30 ngày và 360 ngày; một số hệ thống được thiết lập cho các ngày thực tế trong một tháng, nhưng chỉ 360 ngày trong một năm; và một số được xác định là ngày thực tế trong mỗi tháng và năm. Các mã cho mỗi tùy chọn như sau:

0 = 30 ngày NASD của Hoa Kỳ 30 ngày / năm 360 ngày

1 = Ngày thực tế trong các tháng / Số ngày thực tế trong những năm

2 = Ngày thực tế trong các tháng / 360 ngày trong những năm

3 = Ngày thực tế trong các tháng / 365 ngày trong những năm

4 = Châu Âu 30 ngày trong những tháng / 360 ngày trong những năm

Khi chức năng được nhập, nếu bạn bấm phím bất kỳ (chẳng hạn như thanh khoảng trắng) sau đối số cuối cùng (C4), Excel sẽ cung cấp một menu bật lên liệt kê năm tùy chọn ở trên. Chọn mã thích hợp từ danh sách và nhấn Enter .

Nếu bạn không nhập số cơ sở, mặc định là 0, có nghĩa là các tháng 30 ngày và các năm 360 ngày — không phải là lựa chọn tốt nếu bạn đang tìm kiếm kết quả trong thời gian thực.

Lưu ý: Cột cuối cùng (E) trong bảng tính này cho biết cột D trông như thế nào khi cột được định dạng dưới dạng phân số sử dụng phần tư. Ví dụ, E4 là một mái tóc ít hơn hai năm, trong khi E5 là một mái tóc hơn một năm. E6 là 1-1 / 4 năm, E7 là 8-3 / 4 năm, v.v.

EOMONTH ()

Sử dụng chức năng này để xác định ngày của ngày cuối cùng của tháng (trong tương lai hoặc tháng trước). Tại sao không chỉ kiểm tra lịch hoặc sử dụng một trong những bài thơ ghi nhớ như “30 ngày vào tháng Chín…”? Bởi vì khi tạo bảng tính với nhiều tính toán, các bài thơ và lịch không thể được đưa vào công thức cần thiết để tạo ra các kết quả dạng số. Hàm này trả về một số sêri, đại diện cho một ngày cụ thể trong Excel.

Các đối số cho hàm này là:

start_date: một ngày đại diện cho ngày bắt đầu theo định dạng số sê-ri Excel hợp lệ

tháng: số tháng trước hoặc sau ngày bắt đầu

Cú pháp trông như sau: = EOMONTH (ngày bắt đầu, tháng)

Lưu ý: Trong nhiều tháng, hãy sử dụng số dương cho các ngày trong tương lai và số âm cho các ngày trong quá khứ.

1. Nhập 10 ngày trong các ô từ A4 đến A13.

2. Nhập công thức sau vào ô B4: = EOMONTH (A4,1). A4 là ngày trong ô đó và số '1' có nghĩa là một tháng. Điều này có nghĩa là hiển thị ngày cuối cùng của tháng trong một tháng qua ngày trong ô A4. Lưu ý rằng chương trình trả về một số sê-ri Excel.

3. Tiếp theo, định vị con trỏ của bạn trên Ô B4 và nhấn phím chức năng F2 (Chỉnh sửa). Con trỏ tới ký tự 'A' trong công thức và nhấn phím chức năng F4 (tham chiếu tuyệt đối) ba lần, hoặc cho đến khi công thức trông như thế này: = EOMONTH ($ A4,1).

4. Đối với những người không quen với tính năng này, ký hiệu đô la ở phía trước chữ 'A' có nghĩa là tham chiếu cột sẽ không thay đổi khi công thức được sao chép. Bây giờ, khi chúng ta sao chép công thức trong A4 xuống và hơn, các hàng ô thay đổi nhưng cột vẫn giữ nguyên.

5. Tiếp theo, định dạng số sê-ri trong ô B4 thành ngày trung bình; nghĩa là, ngày 26 tháng 2 năm 2016.

6. Sao chép ô B4 xuống từ B5 đến B13. Lưu ý rằng định dạng số trung dài cũng sao chép với công thức.

7. Tiếp theo, sao chép công thức trong B4 đến C4, D4 và E4. Chỉnh sửa từng công thức để phản ánh số tháng mới: Thay đổi '1' trong C4 thành số 6. Thay đổi '1' trong D4 thành '12' và '1' trong E4 thành số '18 '. Bây giờ sao chép C4 qua E4 đến C13 đến E13.

8. Hãy nhớ mở rộng các cột để chứa các định dạng mới, sau đó nhận thấy bạn có thể nhanh chóng tìm ra thông tin này bằng cách sử dụng hàm có giá trị này như thế nào.

NETWORKDAYS.INTL ()

Hàm này tính toán số ngày làm việc giữa hai ngày được chỉ định, trừ ngày cuối tuần. Điều này hữu ích nếu bạn tính ngày làm việc (hoặc ngày học) trong một quý, học kỳ hoặc năm.

Sự khác biệt giữa chức năng này và những người khác như nó là tùy chọn để chọn ngày nào được tính là ngày cuối tuần. Không phải ai cũng được nghỉ vào thứ Bảy và Chủ Nhật. Một số công nhân nhận được các ngày thứ Hai và thứ Ba, một số có được Thứ Tư và Thứ Sáu. Với chức năng này, bạn có thể đặt ngày cuối tuần cho lịch biểu riêng của mình.

Ngoài ra, chức năng này cho phép bạn chọn và đặt các ngày lễ riêng lẻ trong một khung thời gian cụ thể. Ví dụ: trong quý IV của năm dương lịch, có hai ngày lễ vào tháng 10, hai ngày trong tháng 11 và hai vào tháng 12 nếu bạn bao gồm Halloween và Đêm Giáng sinh. Nếu không, sau đó bạn có thể đặt các ngày lễ đến bốn trong quý IV thay vì sáu.

Đây là các số mã tương ứng với các ngày trong tuần cần thiết để xác định tùy chỉnh ngày cuối tuần của bạn.

Số ngày cuối tuần

1 Thứ Bảy, Chủ Nhật

2 Chủ Nhật, Thứ Hai

3 Thứ Hai, Thứ Ba

4 Thứ Ba, Thứ Tư

5 Thứ Tư, Thứ Năm

6 Thứ Năm, Thứ Sáu

7 Thứ Sáu, Thứ Bảy

Chỉ 11 Chủ Nhật

Chỉ thứ hai 12

13 Thứ ba chỉ

14 Thứ tư chỉ

Chỉ 15 ngày thứ Năm

Chỉ 16 thứ Sáu

Chỉ thứ 17

Nếu bạn để trống tham số này (hoặc không xác định), tham số mặc định là số 1.

Nhập ngày lễ dưới dạng phạm vi ô mà bạn đã chỉ định ngày nghỉ thực tế (ví dụ: F4: F10) hoặc dưới dạng danh sách số sê-ri đại diện cho ngày nghỉ thực tế.

Các đối số cho hàm này là:

start_date: ngày bắt đầu

end_date: ngày kết thúc

cuối tuần: cài đặt cho những ngày trong tuần nên được xem là cuối tuần (tham số tùy chọn)

ngày lễ: tham chiếu đến các ngày được coi là ngày không hoạt động (tham số tùy chọn)

Cú pháp trông giống như sau: = NETWORKDAYS.INTL (ngày bắt đầu, ngày kết thúc, [cuối tuần], [ngày lễ])

1. Nhập các tiêu đề trường / cột sau đây trên cột A, B, C, F và G tương ứng: Ngày bắt đầu , Ngày kết thúc , Số ngày làm việc . Cuối cùng, nhập ngày lễ (tập trung và hợp nhất trên F và G).

2. Nhập một số ngày ngẫu nhiên trong cột A và B. Hãy chắc chắn rằng Ngày Kết thúc không phải là ngày trước Ngày Bắt đầu.

3. Nhập một số ngày nghỉ ngẫu nhiên (tên và ngày) vào các cột F và G.

4. Định vị con trỏ của bạn trong ô C4. Chuyển đến Công thức > Ngày & Giờ và chọn hàm NETWORKDAYS.INTL .

5. Trong hộp thoại Function Arguments , nhấp vào bên trong hộp Start_Date, sau đó nhấn con trỏ của bạn trong ô A4.

6. Nhấn phím Tab xuống hộp trường End_Date và nhấp vào con trỏ của bạn trong ô B4.

7. Nhấn phím Tab xuống hộp trường Cuối tuần và nhập một trong các mã cuối tuần được xác định (hãy nhớ, 1 = Thứ Bảy và Chủ Nhật).

8. Nhấn phím Tab xuống hộp trường Ngày lễ , sau đó chọn / đánh dấu các ô từ G4 đến G11. Nhấp vào OK .

Quan trọng: Trước khi bạn sao chép công thức này từ ô C4 xuống C5 đến C11, hãy sử dụng phím chức năng F4 để làm cho các ô Tốt nhất tuyệt đối: = NETWORKDAYS.INTL (A4, B4,1, $ G $ 4: $ G $ 11), vì vậy các ngày trong phạm vi ngày lễ luôn từ G4 đến G11.