Bộ hàm tính & đếm ngày

[digita]

Chào mọi người,

Đây là câu trả lời chung cho 2 thread sau đây:

http://www.giaiphapexcel.com/forum/showthread.php?t=2431

http://www.giaiphapexcel.com/forum/showthread.php?t=2422

Xin giới thiệu 3 hàm dùng để tính toán những vấn đề thường gặp phải có liên quan đến ngày tháng:

Hàm đếm số ngày - SoNgay(ngày 1, ngay 2, x, cột chứa các ngày lễ). Xin quí vị xem hướng dẫn chi tiết trong file đính kèm. Hàm này có rất nhiều chức năng bao gồm luôn cách tính datediff và Networkdays. Khác với khi dùng hàm networkdays, bạn khỏi cần cài đặt addin VBA analysis toolpak hay analysis toolpak.

Nếu ta biết ngày 1 công trình bắt đầu và dự tính là bao nhiêu ngày sẽ hoàn tất, hàm thứ 2 (Edate) sẽ giúp tính ngày kết thúc. Edate có nghĩa là end date (ngày cuối). Cú pháp dùng là edate(ngày đầu, số ngày làm việc,
cột chứa các ngày lễ).

Hàm thứ 3 là Sdate (start date - ngày bắt đầu). Cái hàm này làm ngược lại thao tác Edate khi ta biết ngày kết thúc và muốn tìm ngày bắt đầu. Cú pháp giống như hàm 2.

Các hàm trên đều tính ngày làm là từ Thứ 2 đến Thứ 6. Nếu tính ngày Thứ 7 là 1 ngày làm việc bình thường thì cần dùng hàm SoNgay để đếm bao nhiêu ngày Thứ 7 trong thời gian tính.

Thí dụ: 1 công việc bắt đầu vào ngày 29/12/2006 (ở ô J1) và mất 10 ngày công để hoàng thành. Nếu không làm ngày Thứ 7 thì Edate(J1, 10, cột chứa ngày lể) = 12/1/2007. Nếu có làm ngày Thứ 7 thì cần dùng 2 công thức liên kết như sau:

Edate(J1, 10 - SoNgay(J1,Edate(ngày bắt đầu, 10, Ngày lễ),7,Ngày lễ),ngày lễ).

Đã chạy thử các hàm này trong XL 2003 thì cho kết quả đúng hết. Tuy chưa thữ trong các bản XL khác, nhưng tôi tin là các hàm này tương hợp với tất cả ấn bản XL cũ và mới.

Mời mọi người xài thữ cho ý kiến. Nếu gặp trục trặc xin pm cho mình biết.

Mến chào

 

[doanhhoang79]

Cú pháp đếm ngày

Chào anh,
Tôi đã đọc bài của anh và áp dụng vào thực tế công việc của ttôi rất hiệu quả. Xin anh cho hỏi cú pháp đếm tháng được không?
VD: Giả sử tôi 01 nhân viên có HĐLĐ xác định thời hạn 36 tháng kể từ ngày 05/08/2008. Khi nhập số liệu đó vào thì chương trình tự ra kết quả hạn HĐLĐ là 05/08/2011.

Cảm ơn anh,

 

[ndu96081631]

Chào anh,
Tôi đã đọc bài của anh và áp dụng vào thực tế công việc của ttôi rất hiệu quả. Xin anh cho hỏi cú pháp đếm tháng được không?
VD: Giả sử tôi 01 nhân viên có HĐLĐ xác định thời hạn 36 tháng kể từ ngày 05/08/2008. Khi nhập số liệu đó vào thì chương trình tự ra kết quả hạn HĐLĐ là 05/08/2011.

Cảm ơn anh,

Cái này đâu cần hàm gì chứ... chỉ làm bài toán cộng là ra mà

 

[BNTT]

Chào anh,
Tôi đã đọc bài của anh và áp dụng vào thực tế công việc của ttôi rất hiệu quả. Xin anh cho hỏi cú pháp đếm tháng được không?
VD: Giả sử tôi 01 nhân viên có HĐLĐ xác định thời hạn 36 tháng kể từ ngày 05/08/2008. Khi nhập số liệu đó vào thì chương trình tự ra kết quả hạn HĐLĐ là 05/08/2011.

Cảm ơn anh,

Thích dùng hàm thì cũng có:

=DATE(YEAR(A2), MONTH(A2) + 36, DAY(A2))​

Với ô A2 là ô chứa ngày ký hợp đồng (05/8/2008)

Bạn có thể linh động hơn bằng cách dùng thêm 1 ô nữa chứa thời hạn hợp đồng.

Ví dụ, ô A2 là ngày ký hợp đồng, ô B2 là thời hạn hợp đồng (tính theo tháng), tại ô C2 (ngày kết thúc hợp đồng) bạn nhập công thức:

=DATE(YEAR(A2), MONTH(A2) + B2, DAY(A2))​

Nếu hợp đồng của bạn tính theo năm, hoặc theo ngày, thì bạn cộng vào chỗ tương ứng của nó trong công thức của hàm DATE() ở trên.

 

[digita]

Chào anh,
Tôi đã đọc bài của anh và áp dụng vào thực tế công việc của ttôi rất hiệu quả. Xin anh cho hỏi cú pháp đếm tháng được không?
VD: Giả sử tôi 01 nhân viên có HĐLĐ xác định thời hạn 36 tháng kể từ ngày 05/08/2008. Khi nhập số liệu đó vào thì chương trình tự ra kết quả hạn HĐLĐ là 05/08/2011.

Cảm ơn anh,

Rất vui khi biết cái bài đó giúp ích trong công việc của bạn. BTNN có hướng dẩn công thức tính tháng dùng các hàm có sằn trong XL. Tôi có cái UDF ngắn để bạn chọn.

Function CongThang(SThang As Integer, Date1 As Date) As Date
CongThang = DateAdd("m", SThang, Date1)
End Function

Bạn copy code trên vô cái module thường trong VBE. Cách dùng thì gõ trong XL là:

=congthang(A,B)

A = số tháng, B là ô có ngày bắt đầu hay là "dd/mm/yyy" bao gồm cập dấu kép.

Mến

 

[SonThuy]

Tính số ngày làm việc trừ ngày nghỉ

tôi có 1 công việc:
- bắt đầu từ ngày 02/01/2008
- kết thúc vào ngày 10/01/2008
Tôi muốn tính số ngày làm việc của công việc đó (trừ thứ Bảy, Chủ Nhật và ngày Lễ nếu có)
Mong các bạn giúp mình với nhé!!!

 

[ThuNghi]

tôi có 1 công việc:
- bắt đầu từ ngày 02/01/2008
- kết thúc vào ngày 10/01/2008
Tôi muốn tính số ngày làm việc của công việc đó (trừ thứ Bảy, Chủ Nhật và ngày Lễ nếu có)
Mong các bạn giúp mình với nhé!!!

Vào menu Tools, AddIns, chọn Analysic Toolpak
Sau đó dùng hàm networkday nhé.
Còn hông thì tìm bài của anh ndu hay là anhtuan1066

 

[ndu96081631]

tôi có 1 công việc:
- bắt đầu từ ngày 02/01/2008
- kết thúc vào ngày 10/01/2008
Tôi muốn tính số ngày làm việc của công việc đó (trừ thứ Bảy, Chủ Nhật và ngày Lễ nếu có)
Mong các bạn giúp mình với nhé!!!

File ở bài số #1 đã có đủ mọi thứ bạn cần rồi đấy! Sao không tham khảo thử

 

[echo07]

Mấy sếp cho em hỏi chứ làm mấy cái hàm tự tạo này thế nào nhỉ
em viết vào mấy đâu hiểu cho đâu?
thanks trước nha.

 

[nguyenhaigiang1985]

cần gì chứ ban dùng hàm =(days360(ngayđầu:ngaycuối))-(days360(ngayđầu:ngaycuối))/7*số ngày nghỉ trong tuần)

 

[huunghi339]

Chào mọi người tôi có một vấn đề cần được giải đáp như sau:
Để tính trợ cấp thôi việc cho một người lao động nghỉ việc cần phải biết tổng thời gian của người đó bằng cánh lấy ngày nghỉ việc trừ ngày vào làm việc
các bạn giúp mình tạo một công thức tự động tính thời gian công tác được không
ví dụ tại ô A1 gõ ngày bắt đầu, ô B1 gõ ngày kết thúc thì ô C1 ra kết quả là bao nhiêu năm bao nhiêu tháng và mấy ngày
Mong các bạn giúp đở!

 

[BNTT]

Chào mọi người tôi có một vấn đề cần được giải đáp như sau:
Để tính trợ cấp thôi việc cho một người lao động nghỉ việc cần phải biết tổng thời gian của người đó bằng cánh lấy ngày nghỉ việc trừ ngày vào làm việc
các bạn giúp mình tạo một công thức tự động tính thời gian công tác được không
ví dụ tại ô A1 gõ ngày bắt đầu, ô B1 gõ ngày kết thúc thì ô C1 ra kết quả là bao nhiêu năm bao nhiêu tháng và mấy ngày
Mong các bạn giúp đở!

Bạn chịu khó tìm trên diễn đàn đi, câu hỏi này đã có rất nhiều người hỏi và cũng đã có rất nhiều câu trả lời. Ngay trong bài này cũng có.

Riêng vấn đề của bạn, nếu bạn hỏi không không như thế, tôi nghĩ chắc bạn sẽ còn hỏi tiếp. Nếu tính tổng thời gian làm việc từ ngày vào làm đến ngày nghỉ, liệu có chính xác không? Tôi hỏi vậy, bởi có nhiều nơi thì tính kiểu khác à, họ sẽ trừ thêm những ngày mà người đó nghỉ phép, trừ đi thứ Bảy, Chủ Nhật, trừ đi nghỉ lễ, nghỉ tết...

Còn nếu như đúng là bạn chỉ muốn hỏi như thế, thì bạn dùng hàm DATEDIF:

Tính năm: =DATEDIF(ngày vào làm, ngày nghỉ việc, "y")

Tính tháng: =DATEDIF(ngày vào làm, ngày nghỉ việc, "ym")

Rồi ghép hai cái đó lại.

 

[speed_123456]

hàm ngày lễ

Hi all!
mình là dân kế toán mới vào nghề và cũng là thành viên mới mong anh(chị)& các bạn giúp đỡ.mình có 1 bài về ngày : có 2 cột dữ liệu đó là ngày thuê và ngày trả:
vd: ngày thuê:1/20/2006 đến 2/12/2006. tính trong khoảng thời gian đó có bao nhiêu ngày chủ nhật? mong các bạn giúp đỡ. mình cảm ơn nhiều nhiều

 

[anhtuan1066]

Hi all!
mình là dân kế toán mới vào nghề và cũng là thành viên mới mong anh(chị)& các bạn giúp đỡ.mình có 1 bài về ngày : có 2 cột dữ liệu đó là ngày thuê và ngày trả:
vd: ngày thuê:1/20/2006 đến 2/12/2006. tính trong khoảng thời gian đó có bao nhiêu ngày chủ nhật? mong các bạn giúp đỡ. mình cảm ơn nhiều nhiều

Xem chi tiết tại đây nhé:
http://www.giaiphapexcel.com/forum/showthread.php?t=2515

 

[speed_123456]

bác Anhtuân1066 thân mến! em đã áp dụng công thức của bác và đã làm ra kết quả, nhưng em chưa hiểu rõ về công thức =INT((A2-WEEKDAY(A2)-A1+8)/7) mong bác chỉ giúp em. cảm ơn bác nhiều, và giúp em bài này nhé:

 

[anhtuan1066]

và giúp em bài này nhé

Làm thử cho bạn đây! Bài này chả liên quan gì đến việc đếm số ngày CN cả
(BT thuộc cấp độ TB, nhưng cũng khá thú vị)

 

[speed_123456]

Em xin cảm ơn bác nhiều nhiều! Bài em nhờ bác làm ko liên wuan tới việc đếm ngày chủ nhật. Bài đếm tổng số ngày chủ nhật em lấy công thức của bác về dùng ok luôn, nhưng mong bác giúp em =int((a2-weekday(a2)-a1+8)/7) em chưa hiểu rõ về công thức này

BÁC XEM LẠI GIÚP EM! BÀI BÁC LÀM EM THẤY TẠI Ô H4 THỎA MẢN ĐIỀU KIỆN SAO KO ĐƯỢC THI VÀ CÂU HỎI NÀY "Tạo giá trị cho cột Ghi Chú: Nếu Học viên nào nộp đủ học phí và trước Hạn Cuối Nộp Học Phí thì ghi "Được Thi" TỨC LÀ HÀM AND CHỨ BÁC

bác xem đúng chưa nhé

 

[ndu96081631]

BÁC XEM LẠI GIÚP EM! BÀI BÁC LÀM EM THẤY TẠI Ô H4 THỎA MẢN ĐIỀU KIỆN SAO KO ĐƯỢC THI C

Thỏa mãn ĐK đâu mà thỏa chứ
- Cái ông ấy tuy nộp đủ tiền, nhưng... nộp trể
Ẹc.. Ẹc...
Yêu cầu là:
Tạo giá trị cho cột Ghi Chú: Nếu Học viên nào nộp đủ học phí và trước Hạn Cuối Nộp Học Phí thì ghi "Được Thi"
Quá hạn thì cấm thi thôi...
(Trường này.. ác thật)
Nói thêm

VÀ CÂU HỎI NÀY "Tạo giá trị cho cột Ghi Chú: Nếu Học viên nào nộp đủ học phí và trước Hạn Cuối Nộp Học Phí thì ghi "Được Thi" TỨC LÀ HÀM AND CHỨ BÁC

AND hay OR gì cũng được tuốt, chẳng qua là cách phát biểu:
- Nếu ai nộp đủ và trước thời hạn thi được thi, ngược lại để rỗng(kiểu AND)
vậy ta phát biểu theo kiểu OR
- Nếu ai không nộp đủ hoặc quá thời hạn thì để rổng, ngược lại thì được thi
Có vấn đề gì không với 2 cách phát biểu này
???

 

[haibangplastic]

Em cũng có vấn đề về hàm ngày tháng sao nhờ anh giúp đỡ
cột 1 tên khách hàng
cột 2 cmnd
cột 3 ngày tháng năm
giờ em định dùng hàm lọc nếu ngày tháng năm ít hơn 1990 thì tham chiều ra con không thì blank
nhưng khi em tham chiếu nhỏ hơn lớn hơn thì nó không hiểu , em cũng đã dùng right bắt 4 ký tự nhưng không được mong anh giúp đỡ

 

[cadafi]

Em cũng có vấn đề về hàm ngày tháng sao nhờ anh giúp đỡ
cột 1 tên khách hàng
cột 2 cmnd
cột 3 ngày tháng năm
giờ em định dùng hàm lọc nếu ngày tháng năm ít hơn 1990 thì tham chiều ra con không thì blank
nhưng khi em tham chiếu nhỏ hơn lớn hơn thì nó không hiểu , em cũng đã dùng right bắt 4 ký tự nhưng không được mong anh giúp đỡ

Bạn cần nói rõ, ngày tháng năm ít hơn 1990 là như thế nào? Có phải là ngày tháng năm nhỏ hơn 01/01/1990 hay không?
Nếu đúng vậy, thì cột 4 bạn gõ công thức sau:

=if([ngày tháng năm]<DATE(1990,01,01),[ngày tháng năm],"")