Đăng ký học Excel - PivotTable 3 buổi tối (31/7, 2 và 4/8) - TPHCM

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

Thảo luận trong 'Cấu trúc dữ liệu Excel và việc Xử lý dữ liệu' bắt đầu bởi digita, 11 Tháng sáu 2007.

  1. digita

    digita Guest

    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
     

    Các file đính kèm:

    • Book3.xls
      Kích thước:
      27.5 KB
      Đọc:
      3,408
  2. doanhhoang79

    doanhhoang79 Thành viên hoạt động

    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,
     
  3. ndu96081631

    ndu96081631 Ăn cùng GPE, Ở cùng GPE, Sống cùng GPE Staff Member Super Moderator

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

    BNTT Bùi Nguyễn Triệu Tường

    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.

     
  5. digita

    digita Guest

    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.

    Mã:
    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à:

    Mã:
    =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
     
  6. SonThuy

    SonThuy Thành viên mới

    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é!!!@#$
     
  7. ThuNghi

    ThuNghi Hãy cho rồi sẽ nhận!

    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
     
  8. ndu96081631

    ndu96081631 Ăn cùng GPE, Ở cùng GPE, Sống cùng GPE Staff Member Super Moderator

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

    echo07 Nguyệt Hà

    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.
     
  10. nguyenhaigiang1985

    nguyenhaigiang1985 Thành viên mới

    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)
     
  11. huunghi339

    huunghi339 Thành viên mới

    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 đở!
     
  12. BNTT

    BNTT Bùi Nguyễn Triệu Tường

    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.
     
  13. speed_123456

    speed_123456 Thành viên mới

    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
     
  14. anhtuan1066

    anhtuan1066 Thành viên gạo cội

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

    speed_123456 Thành viên mới

    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é:
     

    Các file đính kèm:

    Chỉnh sửa lần cuối bởi điều hành viên: 20 Tháng mười hai 2008
  16. anhtuan1066

    anhtuan1066 Thành viên gạo cội

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

    Các file đính kèm:

  17. speed_123456

    speed_123456 Thành viên mới

    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é
     

    Các file đính kèm:

    Chỉnh sửa lần cuối bởi điều hành viên: 20 Tháng mười hai 2008
  18. ndu96081631

    ndu96081631 Ăn cùng GPE, Ở cùng GPE, Sống cùng GPE Staff Member Super Moderator

    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í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 đủ 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
    ???
     
    Lần chỉnh sửa cuối: 11 Tháng mười hai 2008
  19. haibangplastic

    haibangplastic Thành viên mới

    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 đỡ
     
  20. cadafi

    cadafi New Style! New Life! Staff Member Super Moderator

    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:
     

Chia sẻ trang này