PDA

View Full Version : Bộ hàm tính & đếm ngày



digita
11-06-07, 06:50 AM
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
21-08-08, 07:50 AM
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
21-08-08, 09:07 AM
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
21-08-08, 10:54 AM
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
21-08-08, 02:30 PM
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
12-09-08, 03:41 PM
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
12-09-08, 03:49 PM
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
12-09-08, 03:52 PM
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
23-09-08, 07:55 AM
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
01-10-08, 03:24 PM
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
02-10-08, 02:44 PM
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
02-10-08, 02:53 PM
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
08-12-08, 08:25 PM
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
08-12-08, 09:16 PM
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
09-12-08, 08:37 PM
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
10-12-08, 07:19 PM
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
10-12-08, 10:13 PM
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
11-12-08, 07:56 PM
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
12-12-08, 02:40 PM
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
12-12-08, 02:58 PM
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],"")

haibangplastic
12-12-08, 03:05 PM
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:
thưa bác em đã thử rồi vẫn không ra bác ơi--=0--=0--=0--=0

vì hàng ngày tháng năm của em ghi như sau nè :
20/11/2008 chảng hạn

cadafi
12-12-08, 03:07 PM
thưa bác em đã thử rồi vẫn không ra bác ơi--=0--=0--=0--=0
Vậy thưa bạn, bạn định dạng cột ngày tháng năm của bạn là gì vậy! bạn có thể gửi file của bạn lên không?


vì hàng ngày tháng năm của em ghi như sau nè :
20/11/2008 chảng hạn
Cái này không ảnh hưởng đến công thức!

ThuNghi
12-12-08, 04:04 PM
thưa bác em đã thử rồi vẫn không ra bác ơi--=0--=0--=0--=0

vì hàng ngày tháng năm của em ghi như sau nè :
20/11/2008 chảng hạn
Cẩn thận hơn thì dùng, A1: ngày tháng năm.
if(date(year(A1), month(A1), day(A1))<Date(1990,1,1),"",A1)

speed_123456
16-12-08, 11:32 PM
ÔI bạn xem lại ! "Điền giá trị cho cột Hạn Cuối Nộp Học Phí biết rằng học viên thi Tốt nghiệp Tháng nào thì Hạn Cuối Nộp Học Phí là Ngày cuối cùng của Tháng trước đó".và (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"). VD:tại ô D4 ngày thi tốt nghiệp là 12/07/2006 và ngày nộp học phí la 10/05/2006 và hạn cuối nộp học phí là 30/06/2006, số tiền 150.000( nộp đủ) và ko quá thời hạn đấy chứ! mình gửi file bạn xem lại nhé


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

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
???
Bạn xem lại dùm! VD:tại ô D4 hạn cuối nộp học phí là 30/06/2006 ngày nộp học phí là 10/05/2006 và số tiền nộp là 150.000(đã nộp đủ và ko quá hạn)


ndu96081631 bạn xem lại file này nhé!

BNTT
16-12-08, 11:46 PM
ÔI bạn xem lại ! "Điền giá trị cho cột Hạn Cuối Nộp Học Phí biết rằng học viên thi Tốt nghiệp Tháng nào thì Hạn Cuối Nộp Học Phí là Ngày cuối cùng của Tháng trước đó".và (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"). VD:tại ô D4 ngày thi tốt nghiệp là 12/07/2006 và ngày nộp học phí la 10/05/2006 và hạn cuối nộp học phí là 30/06/2006, số tiền 150.000( nộp đủ) và ko quá thời hạn đấy chứ! mình gửi file bạn xem lại nhé
Bạn dùng hàm này: EOMONTH (trong bộ Add-in Analysis ToolPak)

Công thức ở G4 (và kéo xuống dưới):

=EOMONTH(D4, -1)
(trừ 1, bởi vì lấy ngày cuối cùng của tháng trước)

Còn công thức ở cột H, mình nghĩ phải như vầy mới đúng:

Nếu ngày nộp học phí trước hạn nộp cuối cùng VÀ nộp đủ tiền (hoặc dư), thì được thi
Bạn phải dùng hàm AND chứ, sao lại dùng OR ?
Trong file bạn gửi, B02 nộp học phí trễ đến 3 tháng mà bạn vẫn cho nó thi ?

Tôi dùng cái này:

=IF(AND(HLOOKUP(LEFT(A4), $A$17:$D$19, 3, 0) <= E4, G4>F4), "Được thi", "")
Thì có: A01, C02, C01, B03 và A04 là được thi, vì đóng tiền đủ và trước hạn.

Bạn xem lại xem có đúng không.

ndu96081631
20-12-08, 08:12 AM
BẠN HÃY ĐỌC KỸ LẠI CÂU HỎI NHÉ! CÔNG THỨC TÍNH NGÀY CUỐI CỦA THÁNG TRƯỚC ĐÓ : A1-DAY(A1) KO SAI CHÚT NÀO. BÀI NÀY BẠN ANHTUÂN1066 LÀM NHƯNG SAI ĐIỀU KIỆN =IF(OR(HLOOKUP(LEFT(A4),$A$17:$D$19,3,0)>E4,G4>F4),"","Được thi")>>>NÓ SAI Ở CHỖ "G4>F4", G4: LÀ HẠN CUỐI, F4: LÀ NGÀY NỘP VÀ PHẢI LÀ "F4>G4". (NGÀY NỘP MÀ LỚN HƠN HẠN CUỐI THÌ KO ĐƯỢC THI ) BẠN MÀ LÀM THẾ BẠN CÒN AC HƠN CẢ TRƯỜNG ĐÓ!!!!????

Ôi... chỉ nhầm lẩn tí thôi ---> Lý ra bạn sửa lại được mà (dựa vào phát biểu OR hoặc AND)
Công thức củ:

=IF(OR(HLOOKUP(LEFT(A4),$A$17:$D$19,3,0)>E4,G4>F4),"","Được thi")Sửa lại thành:

=IF(OR(HLOOKUP(LEFT(A4),$A$17:$D$19,3,0)>E4,F4>G4),"","Được thi")Ổn chứ
---------------


Bạn phải dùng hàm AND chứ, sao lại dùng OR ?

AND hay OR gì đều được cả, tùy điều kiện đầu vào (mà ta đặt trong biểu thức)

speed_123456
28-12-08, 08:07 PM
Mình làm thay bác anhtuan1066 được không? bạn xem file đính kèm nhé!

chúc bác mọi điều tốt lành! em có 1 bài có lẽ sức của em thì chắc là ko thể làm được mong bác ra tay cúu giúp dùm em. em mới làm được có 1 nửa bài thôi bác ah. bác giúp em nhé. cảm ơn bác nhiều nhiều

Po_Pikachu
28-12-08, 08:45 PM
Đây là file đã hoàn thành.
File: http://www.mediafire.com/?gheinlnmwdt
Thân.

hoangminhtien
28-12-08, 10:18 PM
chúc bác mọi điều tốt lành! em có 1 bài có lẽ sức của em thì chắc là ko thể làm được mong bác ra tay cúu giúp dùm em. em mới làm được có 1 nửa bài thôi bác ah. bác giúp em nhé. cảm ơn bác nhiều nhiều
Thêm 1 cách nữa cho bạn tham khảo nhé!

BNTT
28-12-08, 10:58 PM
Bài của bạn Hoangminhtien có sáng tạo, tuy nhiên xin góp ý 3 điều sau:



1. Theo yêu cầu, nhân viên nào trực ngày lễ thì đánh dấu T, nhưng bài của bạn đánh dấu là X!

Công thức ở C24:G27 phải thêm hàm SUBSTITUTE để chuyển đổi chữ X thành chữ T nữa, thì mới đúng yêu cầu:

=SUBSTITUTE(IF(ISNA(VLOOKUP($B24,$B$5:$G$15, MATCH(C$23,$C$4:$G$4,0)+1,0)), "", VLOOKUP($B24,$B$5:$G$15, MATCH(C$23,$C$4:$G$4,0)+1,0)), "X", "T")


2. Ở chỗ tính tiền trực lễ, theo tôi nghĩ thì đề bài không cho phép làm thêm một hàng phụ như bạn đã làm (C28:G28), vì nếu như cho làm hàng phụ như thế, thì họ đã không bắt buộc chỉ sử dụng 1 công thức cho cột tính tiền ngày lễ.

Tôi xin đề nghị công thức sau, không cần dùng hàng phụ, nhưng vẫn chỉ dùng 1 công thức cho J24:J28

=COUNTIF(INDIRECT(ADDRESS(24, MATCH(I24,$A$23:$G$23,0)) & ":" & ADDRESS(27, MATCH(I24,$A$23:$G$23,0))), "T") * 100000


3. Chỗ tính tiền trực ngày, đâu cần phải bê nguyên công thức tính tiền trực lễ vào trong đó? Mà nên lợi dụng chính kết quả của tiền trực lễ: lấy số tiền trực lễ (đã tính) chia cho 100000 là ra ngược lại số ngày rồi mà. Công thức cho K24:K28 sẽ ngắn gọn như sau:

=(SUMIF($C$4:$G$4, $I24, $C$16:$G$16) - J24/100000) * 50000



Xin gửi file tôi đã làm với những công thức trên đây.

ndu96081631
29-12-08, 08:27 AM
Bảng 2 tôi nghĩ nên dùng SUMPRODUCT sẽ gọn hơn VLOOKUP

C24 =CHOOSE(SUMPRODUCT(($B$5:$B$15=$B24)*(OFFSET($B$5: $B$15,,MATCH(C$23,$C$4:$G$4,0))="X"))+1,"","T")
Bảng 3 chỉ dùng COUNTIF là đủ, không cần đến ADDRESS và INDIRECT đâu... cũng không cần cell phụ luôn

J24 =100000*COUNTIF(OFFSET($B$24:$B$27,,MATCH($I24,$C$ 23:$G$23,0)),"T")

vdcuong
07-10-09, 05:22 PM
Bài của bạn Hoangminhtien có sáng tạo, tuy nhiên xin góp ý 3 điều sau:


2. Ở chỗ tính tiền trực lễ, theo tôi nghĩ thì đề bài không cho phép làm thêm một hàng phụ như bạn đã làm (C28:G28), vì nếu như cho làm hàng phụ như thế, thì họ đã không bắt buộc chỉ sử dụng 1 công thức cho cột tính tiền ngày lễ.

Tôi xin đề nghị công thức sau, không cần dùng hàng phụ, nhưng vẫn chỉ dùng 1 công thức cho J24:J28

=COUNTIF(INDIRECT(ADDRESS(24, MATCH(I24,$A$23:$G$23,0)) & ":" & ADDRESS(27, MATCH(I24,$A$23:$G$23,0))), "T") * 100000
.

Xin bạn giải thích hơn cho mình sử dụng hàm INDIRECT ở đây như thế nào? Mình chưa hiểu rõ lắm về cách sử dụng hàm này.
Cám ơn bạn.

duongdv
07-10-09, 08:07 PM
Xin bạn giải thích hơn cho mình sử dụng hàm INDIRECT ở đây như thế nào? Mình chưa hiểu rõ lắm về cách sử dụng hàm này.
Cám ơn bạn.
Việc giải thích một công thức có sử dụng và kết hợp hàm đôi khi là rất khó trong việc diễn đạt. Mình tìm thấy một bài viết của bác Ndu giải thích về hàm Indirect() rất chi tiết. Bạn tham khảo và rồi sẽ hiểu được hàm Indirect() trong công thức mà bạn hỏi
Bạn xem link sau nhé:
http://www.giaiphapexcel.com/forum/showthread.php?t=7486

vdcuong
09-10-09, 09:51 AM
chúc bác mọi điều tốt lành! em có 1 bài có lẽ sức của em thì chắc là ko thể làm được mong bác ra tay cúu giúp dùm em. em mới làm được có 1 nửa bài thôi bác ah. bác giúp em nhé. cảm ơn bác nhiều nhiều
Mình cũng xin góp một công thức cho bảng 2. có lẽ công thức của bác NDU là ngắn nhất nhưng cũng xin đưa ra. Các bạn tham khảo và cho ý kiến.
Code:
C24=IF(IFERROR(MATCH($B24,$B$5:$B$15,0),"")="","",IF(OFFSET($C$4,MATCH($B24,$B$5:$B$15,0),MATCH(C$2 3,$C$4:$G$4,0)-1)="X","T",""))

emailquanghuy
22-04-10, 05:32 PM
cho mình hỏi mình muốn tính số ngày như sau (theo từng cột)
ngày bắt đầu, ngày kết thúc, đã làm được bao nhiêu ngày, còn lại bao nhiêu ngày
thi dùng hàm gì cho tiện
Xin các bác chỉ giáo

vity111009
23-02-11, 10:27 AM
Biết ngày, tháng bắt đầu và ngày, tháng kết thúc rồi tôi phải làm thế nào để đếm được số tháng?

dat_butmuc
23-02-11, 10:34 AM
Biết ngày, tháng bắt đầu và ngày, tháng kết thúc rồi tôi phải làm thế nào để đếm được số tháng?

=DATEDIF("ngày tháng bắt đầu","ngày tháng kết thúc","m")

trantien5203
26-02-11, 02:50 PM
Các bác ơi giúp em hàm này với nhé,
ví dụ em có danh sách hợp đồng lao động của công nhân từ ngày 1/12/2010 đến ngày 31/12/2011 (có nhiều người và nhiều thời hạn khác nhau nữa), giờ em muốn tạo 1 cột để theo dõi: tính đến ngày hôm nay hợp đồng đó còn bao nhiêu ngày (không kể ngày lễ tết)- chi tiết cho từng cá nhân
Giúp em với nhé ạ

uyen0919
12-10-11, 10:59 AM
Tôi xin hỏi thế này, tôi cần tính ra ngày hoàn tất khi công việc của tôi cần 25 ngày làm việc (trừ thứ 7, chủ nhật, ngày lễ) thì làm thế nào? Tôi không chuyên về excel nên khi đọc các hướng dẫn trên vẫn chưa làm được, các bác có thể hướng dẫn từng bước không ạ? Cám ơn nhiều

silvernam
21-10-11, 03:33 PM
Kính gửi anh,

Em sử dụng hàm Countdays thì không được ạ. em xài Excel 2007. Anh hướng dẫn em với. Namln2@msb.com.vn

leonguyenz
21-10-11, 03:44 PM
Excel chưa có hàm COUNTDAYS, chỉ có hàm tự tạo, nếu bạn dùng hàm tự tạo thì liên hệ người viết hàm đó. Bạn nên gửi file lên để mọi người có thể giúp bạn.

Lê Duy Thương
21-10-11, 08:15 PM
Tôi xin hỏi thế này, tôi cần tính ra ngày hoàn tất khi công việc của tôi cần 25 ngày làm việc (trừ thứ 7, chủ nhật, ngày lễ) thì làm thế nào? Tôi không chuyên về excel nên khi đọc các hướng dẫn trên vẫn chưa làm được, các bác có thể hướng dẫn từng bước không ạ? Cám ơn nhiều
nếu bạn dùng excel 2010 thì dùng hàm workday.inl(....)
http://www.giaiphapexcel.com/forum/showthread.php?34694-những-cái-mới-trong-excel-2010-beta&p=230535#post230535

laianhtu
02-12-11, 10:03 AM
Trong Excel có cách nào (công thức) để đếm tổng số ngày thứ bảy, chủ nhật và các ngày nghỉ theo luật lao động của các tháng trong năm 2012 không các Anh/Chị ?

ndu96081631
02-12-11, 10:07 AM
Trong Excel có cách nào (công thức) để đếm tổng số ngày thứ bảy, chủ nhật
Có sẵn trên GPE rồi còn gì:
http://www.giaiphapexcel.com/forum/showthread.php?2515-D%C3%B9ng-h%C3%A0m-ho%E1%BA%B7c-code-n%C3%A0o-%C4%91%E1%BB%83-t%C3%ADnh-%C4%91%C6%B0%E1%BB%A3c-s%E1%BB%91-ng%C3%A0y-Ch%E1%BB%A7-Nh%E1%BA%ADt-trong-m%E1%BB%99t-qu%C3%A3ng-T-G-b%E1%BA%A5t-k%E1%BB%B3&p=16111#post16111

và các ngày nghỉ theo luật lao động của các tháng trong năm 2012 không các Anh/Chị ?
Cái này bạn tự liệt kê thôi, anh Bill làm gì biết Việt Nam có ngày lễ nào chứ

laianhtu
02-12-11, 10:34 AM
Mình vừa đếm tay trong Excel, năm 2012 số ngày nghỉ như sau :

Chúa nhật: 53 ngày

Thứ bảy : 52 ngày

Ngày nghỉ VN theo luật Lao động (kể cả nghỉ bù) : 12 ngày.

P/s: chỉ Áp dụng cho các nhà bank và khối DN nhà nước.

laianhtu
13-12-11, 03:53 PM
Mình vừa đếm tay trong Excel, năm 2012 số ngày nghỉ như sau :

Chúa nhật: 53 ngày

Thứ bảy : 52 ngày

Ngày nghỉ VN theo luật Lao động (kể cả nghỉ bù) : 12 ngày.

P/s: chỉ Áp dụng cho các nhà bank và khối DN nhà nước.

Có cách nào lấy số ngày thứ bảy, ngày chủ nhật, ngày nghỉ theo luật Việt Nam để tính ra số ngày trong Excel không các Anh/Chị ?

Ví dụ: ngày 07/01/2012 là ngày thứ Bảy, đếm là 1 ngày... tương tự xem calendar năm 2012 sẽ tính ra ngày tháng năm và số ngày còn lại của năm 2012 !

hophoang
13-12-11, 04:25 PM
Có cách nào lấy số ngày thứ bảy, ngày chủ nhật, ngày nghỉ theo luật Việt Nam để tính ra số ngày trong Excel không các Anh/Chị ?

Ví dụ: ngày 07/01/2012 là ngày thứ Bảy, đếm là 1 ngày... tương tự xem calendar năm 2012 sẽ tính ra ngày tháng năm và số ngày còn lại của năm 2012 !
Bạn thử cái này xem sao
=A2-A1-NETWORKDAYS(A1;A2;$A$3:$A$7)
A1=ngày bắt đầu tính toán
A2=ngày kết thúc
$A$3:$A$7=các ngày nghỉ lễ

thangnet
28-02-12, 08:03 AM
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.

cho mình hỏi vùng liệt kê ngày lễ là: từ ô đến ô hay phải viết như thế nào
VD: H10:H20 hay như thế nào
còn ngày tháng đc liệt kê trong vùng đó đc định dạng và viết như thế nào
VD: 21/01/2012 hay 01/21/2012 (cách nhập) hay là "21/01/2012"
mình thử liệt kê 9 ngày nghỉ tết Nhâm Thìn vừa rồi mỗi ô 1 kiểu khác nhau mà kết quả vẫn ko thay đổi !$@!!

oshi
29-03-12, 02:37 PM
Anh ơi, làm sao để em dùng được hàm này ạ ??? phải tạo lập công thức cho êxcel trước đúng ko anh ?

Em đang dùng Excel 2010

bibehon
01-11-12, 01:18 PM
Chào các bạn mình có một bài nhưng không biết làm xin các bạn trợ giúp. Một người đi làm từ 11tháng 10 năm 2012 đến 31 tháng 10 năm 2012 trong đó ngày 24 nghỉ, chủ nhật nghỉ tính số ngày đi làm. Xin cảm ơn

cloudy2626
25-12-12, 10:15 AM
xin chào bạn!
mình đã thử dùng thử hàm edate trong máy của mình excell 2007. nhưng chỉ đúng với tháng thôi vì hàm edate có mặc định sẵn là: edate(start_date,months) vd: edate("14/12/2012",5)=14/05/2013.
bạn nào biết cách chỉnh để hàm trả về mặc định tính ngày không?
nếu các bạn có ý kiến gửi về mail này giúp mình nhé: cloudy2626@yahoo.com.vn
thanks


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

vuonglevan
27-12-12, 11:12 AM
Cảm ơn anh rất nhiều em đang đau dầu vì vấn đề này, may quá gặp được bài của anh