Đăng bởi: Âu Trường Sơn | Tháng Một 31, 2010

Sử dụng hàm trong Excel: Nhóm hàm ngày tháng

HÀM NGÀY THÁNG VÀ THỜI GIAN
Một số lưu ý khi sử dụng ngày tháng và thời gian trong Excel:
Excel hỗ trợ tính toán ngày tháng cho Windows và Macintosh. Windows dùng hệ ngày bắt đầu từ 1900. Macitosh dùng hệ ngày bắt đầu từ 1904. Tài liệu này được diễn giải theo hệ ngày 1900 dành cho Windows.
Hệ thống ngày giờ Excel phụ thuộc vào thiết lập trong Regional Options của Control Panel. Mặc định là hệ thống của Mỹ “Tháng/Ngày/Năm” (M/d/yyyy). Bạn có thể sửa lại thành hệ thống ngày của VN “Ngày/Tháng/Năm” (dd/MM/yyyy).
Khi bạn nhập một giá trị ngày tháng không hợp lệ nó sẽ trở thành một chuỗi văn bản. Công thức tham chiếu tới giá trị đó sẽ trả về lỗi.
=NOW() Cho hiện ngày giờ của hệ thống
=TODAY() Cho ngày của hệ thống
=DAY(D) Cho giá trị ngày của D (Trả về thứ tự của ngày trong tháng từ một giá trị kiểu ngày tháng)
=MONTH(D) Cho giá trị tháng của D
=YEAR(D) Cho giá trị năm của D
=DAYS360(BTNT1, BTNT2) Tính số ngày giữa 2 mốc ngày tháng dựa trên cơ sở một năm có 360 ngày.
=EDATE Trả về mốc thời gian xảy ra trước hoặc sau mốc chỉ định
=EOMONTH Trả về ngày cuối cùng của tháng xảy ra trước hoặc sau mốc chỉ định
Hàm HOUR()
Cho biết số chỉ giờ trong một giá trị thời gian
Cú pháp: = HOUR(serial_number)
serial_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(serial_number)
serial_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)
=MONTH Trả về số tháng của một giá trị kiểu ngày tháng.
=NETWORKDAYS Trả về số ngày làm việc trong mốc thời gian đưa ra sau khi trừ đi ngày nghĩ và ngày lễ.
=NOW Trả về ngày giờ hiện tại trong hệ thống của bạn.
Hàm SECOND()
Cho biết số chỉ giây trong một giá trị thời gian
Cú pháp: = SECOND(serial_number)
serial_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)
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)
=WEEKDAY Trả về số thứ tự của ngày trong tuần từ giá trị ngày tháng.
=WEEKNUM Trả về số thứ tự của tuần trong năm từ giá trị ngày tháng.
=WORKDAY Trả về ngày làm việc xảy ra trước hoặc sau mốc thời gian đưa ra.
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%
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
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″})
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)
*Tính tuổi (2):
Ở bài trước, tôi đã đưa ra một cái công thức để tính tuổi dài thoòng như vầy:
= YEAR(NOW() – YEAR(Birthdate) – (DATE(YEAR(NOW()), MONTH(Birthdate), DAY(Birthdate)) > TODAY())
Đó là khi chưa biết đến hàm DATEDIF().
Bây giờ, với DATEDIF(), công thức trên chỉ ngắn như vầy thôi, mà ra kết quả vẫn chính xác:
= DATEDIF(Birthdate, TODAY(), “y”)
Ví dụ, hôm nay là ngày 09/01/2007:
* Với ngày sinh là 05/01/1969 (đã tổ chức sinh nhật rồi), DATEDIF(“05/01/1969″, TODAY(), “y”) = 39
* Nhưng với ngày sinh là 11/1/1969 (chưa tổ chức sinh nhật), DATEDIF(“11/01/1969″, TODAY(), “y”) = 38
*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.
*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)
Lấy lại định dạng ngày tháng
Đôi khi, bạn nhận được một cái database mà không hiểu vì một lý do gì đó, cell chứa ngày tháng năm nó như sau: 20070823,
nghĩa là nó được định dạng theo kiểu YYYYMMDD.
Trường hợp này, không thể dùng Format Cell của Excel để định dạng lại, mà bạn phải dùng đến hàm DATE(year, month, day)
và các hàm xử lý text, ví dụ hàm LEFT(), MID() và RIGHT() để lấy các trị số ngày, tháng, năm cho hàm DATE():
Tôi giả sử con số 20070823 này đang nằm ở cell A1, thì công thức LEFT(A1, 4) sẽ cho ta trị số của năm, MID(A1, 3, 2) sẽ cho trị số của tháng
và RIGHT(A1, 2) sẽ cho trị số của ngày.
Giả sử trong Control Panel của bạn định dạng ngày tháng năm theo kiểu dd/mm/yyyy, và cell nhận kết quả của bạn đã được định dạng theo kiểu ngày tháng năm,
bạn dùng hàm DATE(year, month, day) với 3 tham số là 3 công thức vừa làm ở trên, ta sẽ có:
=DATE(LEFT(A1, 4), MID(A1, 5, 2), RIGHT(A1, 2))→ 23/8/2007

About these ads

Responses

  1. Rất cảm Æ¡n bạn đã có những thông tin thật tuyệt vời, mình đọc các hàm date ma chả hieur gì cả nhÆ°ng bây giờ thì đã rõ rồi. Cam Æ¡n SÆ° phụGá%Bt=Gá»­i phản hồi

  2. Chào bạn Âu Trường Sơn.

    Mình có bài toán đơn giản như sau, muốn nhờ bạn hướng dẫn cách tính: “Công trình phải hoàn thành sau 760 ngày tính cả ngày lễ và chủ nhật. Thời gian khởi công là ngày 5/9/2010″. Bạn chỉ giúp mình cách tính để mình biết đó là ngày tháng nào. máy tính của mình đang để ở hệ thống ngày tháng kiểu Mỹ.
    (Mình lớn tuổi rồi, học chậm lắm nên nhờ bạn chỉ chi tiết một tí nhé). Cảm ơn bạn nhiều, thông báo vào thư điện tử của mình nhé.

    • Theo toi bai toan cua anh co the giai quyet nhu sau: lay ngay 5-9-2010 + 760 (2 gia tri nay dat trong 2 o rieng), excel se tinh ra ngay hoan thanh cong trinh la 6-7-2010 (o nay lap cong thuc tinh tong).
      Vi dụ ô A1 chứa ngay khoi cong 5-9-2010, ô B1 chứa số ngày phải hoàn thành 760, thì ô C1 có công thức là =A1+B1 sẽ trả về ngày hoàn thành.
      (toi cung dang su dung ngay kieu My).
      Excel xem mỗi ngày ứng với một số nguyên, do đó có thể lấy ngày tháng cộng với số nguyên, kết quả sẽ trả về là ngày tháng.

      • Chao Anh!
        Toi muon dat dieu kien neu gia tri mot o trong excel nho hon “A” thi dong chua o do se thanh mot mau khac nhung khong biet thuc hien the nao? Anh co the giup voi.
        Cam on anh nhieu

    • Bạn macthieu làm dự án à! Có nhiều cách quản lý tiến độ – thủ tục pháp lý, CL,… theo exel lắm đó. (cách sử dụng hàm, ko phải vẽ)

      Nếu bạn cần mình có thể lập cho bạn.

      Tiến độ còn bao nhiêu ngày? chậm hay không? đã hoàn thành chưa? Khi hoàn thành hạng mục hoặc chưa sẽ thể hiện màu sắc,….

  3. Em có bài toán khó nhờ anh giúp đỡ: Dữ liệu cho một hàng có sẵn các ngày trong tháng từ 1 đến 31. hàng bên dưới là thứ ngày tháng trong tuần, và một ô chờ nhập số tháng trong năm. Yêu cầu là khi nhập tháng thìvoi hàng thứ trong tuần phải ứng với hàng ngày trong tháng. VD khi nhập tháng 7, ngày 1 sẽ có thứ ứng với bên dưới là thứ Thusday ( đúng với lịch dương). Vì là dân kĩ thuật nên excel không am hiểu lắm. Cảm ơn anh rất nhiều, phiền anh gửi thông báo vào mail cho em được không?

    • Câu hỏi này hơi khó hiểu, theo tôi có thể giải quyết như sau:
      – Để xác định thứ trong tuần ta sử dụng hàm WEEKDAY, hàm này có đối số là ngày tháng năm, bạn định dạng hiển thị cho ô có công thức này là DDD (thứ)
      – Tuy nhiên, ngày – tháng và năm muốn xác định thứ của bạn lại không nằm trong cùng một ô nên phải dùng hàm DATEVALUE(date_text) đề ghép nối lại thành ngày tháng.
      Cụ thể như sau:
      Năm 2010
      Ngày 1 2 3 4 5 …
      Tháng 7
      Thứ =Weekday(Datevalue(B3&”/”&B2″/”B1))
      (giả sử ô chứa giá trị 2010 trong ô B1, ô B2 chứa số 1 và ô B3 chứa số 1 – định dạng mm/dd/yyyy kiểu Mỹ)
      Chú ý định đạng hiển thị cho ô B4 là DDD

      Ngoài ra nếu bạn muốn hiển thị thứ trong tuần bằng tiếng Việt thì sử dụng hàm CHOOSE (đọc tại đây http://autruongson.wordpress.com/2010/07/11/ham-choose-trong-excel/)

  4. Cho mình hỏi, khi mình nỗi 2 ô:
    A1 = Hôm nay là ngày (dạng chuỗi)
    A2 = 26/08/2010

    Thì nó ko được chuỗi “Hôm nay là ngày 26/08/2010″ mà nó lại convert luôn cái ngày ra 1 số 40416 và gắn vào chuỗi.

    Bạn có cách nào nối như trên thì chỉ mình với.. mình đã search nhiều trên mạng mà vẫn chưa tìm được cách

    Nếu được bạn gửi trả lời vào thư của mình nhé
    Thanks

    • Chao ban,
      Excel xem moi ngay la mot so nguyen. Theo toi ban hay su dung 3 ham Day-Month-Year de tach ngay thang nam roi ghep lai, hoac su dng ham TEXT
      Cu the:
      A1 = Hôm nay là ngày
      A2 = 26/08/2010
      Cách 1: Su dung 3 ham Day-Month-Year
      A3 = A1 & day(A2) &” thang ” & month(A2) &” nam ” & year(A2)
      Cách 2: Sử dụng hàm TEXT (chuyển nội dung trong 1 ô thành định dạng chỉ định)
      A3 = A1 & text(A2, “dd-mm-yyyy”)

  5. chu oi! cho chau hoi a, chau tính hiệu số các ngày ví dụ 21/12/2010 với ngày 21/1/2011 đánh hàm vào rồi enter nhưng nó lại cho ra là #value ạ? cháu đã định dạng lại ngày tháng la đ/mm/yyyy rồi ạ nhưng cứ đánh mấy hàm kiểu này là không được ạ! vậy cháu làm sao để khắc phục lỗi này ạ? cháu cám ơn chú ạ!

    • Xin chao,
      Theo mac dinh, Windows hien thi ngay thang kieu My (thang-ngay-nam) boi vay Excel cung hien thi nhu vay.
      De thay doi, ban hay vao Control Panel chon Regional and Language Options, trong the Regional Options chon mot quoc gia co kieu hien thi ngay-thang-nam chang han nhu English (New Zealand), chojn OK.
      Mo Excel len tai o A1 go 27-8-2010, tai o B1 go 15-5-2011, tai o C1 go =B1-A1, xem con loi khong?

  6. tôi muốn hỏi tôi có ngày: 25/05/2009 ở ô A1. sang ô A2 tôi muốn viết thành: ngày 25 tháng 05 năm 2009 thì làm thế nào?

    • Bạn hãy sử dụng 3 hàm DATE, MONTH và YEAR để tách ngày tháng năm.
      Cụ thể:
      A1 = 25/5/2009
      A2 = “Ngày ” & date(A1) & ” tháng ” & month(A1) & ” năm ” & year(A1)

  7. Anh cho em hỏi dùm công thức tính tháng trong trường hợp sau:
    Ô A1 01/10/2005 đến Ô A2 ngày 01/12/2007 bao gồm nhiều dòng nha. Nếu dùng Datedif thì phải ngồi đánh từng dòng Datedif(“01/12/2007″,”01/05/2005″,”m”). Nhưng trong sheet của em có hơn 500 dòng thì em phải làm sao:
    =datedif(“a2″,”a1″,”m”) thì nó không ra

    • Bạn bỏ dấu nháy đôi của địa chỉ A1 và A2 là được.
      =datedif(“a1″,”a2″,”m”) thành =datedif(a1,a2,”m”)
      Lưu ý:
      – a1 <= a2
      – Nếu nhiều ô (lập công thức) có cùng mốc thời gian thì sử dụng địa chỉ tuyệt đối =datedif($a$1,$a$2,”m”)
      Xem ví dụ mẫu

  8. máy nha tui làm hàm dateif ko ra
    bạn có thể chỉ thêm cho tui ko

    • Vấn đề này tôi đã trả lời rồi, bạn đọc các comment cũ sẽ thấy.

  9. Anh oi e co bai tập này a co thể giúp e k?

    Số ĐT gọi đi: 04 3251330
    Số gọi đến Thời gian gọi đj thời gian kết thúc Số phút gọi Tính tiền
    912345678 10:30:29 11:01:07 ? ?
    5531889 11:05:30 11:17:30 ? ?
    45531889 12:01:15 12:25:05 ? ?
    Nội tỉnh: 2000đ/phút
    Ngoài ra: 3000 đ/phút

    • Bài tóan này đơn giản thôi. Bạn làm như sau:
      Lập công thức cho
      ô [Số phút gọi] = [Thời gian kết thúc] – [Thời gian gọi đi] => kết quả sẽ cho số phút gọi
      ô [Tính tiền] = minute([thời gian gọi đi]) * giá

      Còn để xác định Số ĐT gọi đến là nội hay ngọai tỉnh, bạn bổ sung thêm hàm If kiểm tra hai chữ số đầu có là 04 hay không từ đó sẽ đưa giá 2000 hay 3000.

  10. chú cho cháu hỏi tại sao công thức tính số phút
    VD: 3:05
    =hour(A1)*60+minute(A1) sao không cho kêt quả đúng
    cháu cảm ơn!

    • Hàm Hour tách ra giờ, hàm Minute tách ra phút
      Ví dụ:
      Ô A1 = 3:50 AM
      hour(A1) = 3; minute(A1) = 50
      Như vậy: hour(A1)*60+minute(A1) = 230
      (Nếu bạn không thấy kết quả đúng thì nhấn Ctrl+1, định dạng lại hiển thị là General).

  11. Giúp em với. Em muốn lập một bài toán sau: VD: Ô A ngày 1/1/2010, ô B là 11/1/2010, Ô C là 20/1/2010, ô D là kết quả. Em muốn khi em nhập ngày vào ô A thì cho KQ là CA, nhập ngày vào ô B thì cho KQ là VKS, nhập ngày vào ô C thì cho KQ là XX. Em phải làm sao?

    • Bạn chỉ cần sử dụng hàm IF là xong.
      Xem file đính kèm trong mail.

  12. Em chào anh. em đã thử làm theo hướng dẫn của anh nhưng vẫn không ra được kết quả. mà hàm vẫn báo lỗi. Có cách khác để giải bài toán này không a?Em cảm ơn anh.

    • Công thức gõ vào đây hiển thị không đầy đủ nên báo lỗi là đúng. Bạn xem trong mail của bạn nhé!

  13. các Pác giúp mình vấn đề này với.
    trong excel tôi muốn dùng công thức nào để lấy số nguyên trong phép chia và ngược lại. Thanks.

    • Hàm INT(x). Hàm trả về giá trị nguyên của x.
      Ví dụ: INT(8/3) = 2

  14. Cho minh hoi
    minh co bai toan the nay
    tai O A1 ngay 25/11/2010
    tai O A2 minh danh la 24 tháng
    tai O A3 cho minh kết qua la ngày 25/11/2012
    thi minh phai dung cong thuc Excel the nao ah
    cam on ban cho minh cong thuc nhe
    ngay va tháng lưu động khong cố định

    • Trong Excel, ô dữ liệu ngày tháng có thể cộng với một số nguyên, số nguyên này sẽ là ngày.
      VD:
      A1=25-11-2010
      A2=5
      A3=A1+A2 => kết quả là 30-11-2010
      Như vậy bài tóan của bạn có thể làm như sau: chuyển đổi số tháng thành ngày (nhân với 30) rồi mới cộng với ô ngày.
      A3=A1+A2*30
      Thử xem!

  15. A1=25-11-2010
    A2=5
    A3=A1+A2 => kết quả là 30-11-2010
    Như vậy bài tóan của bạn có thể làm như sau: chuyển đổi số tháng thành ngày (nhân với 30) rồi mới cộng với ô ngày.
    A3=A1+A2*30
    theo nhu cacch tinh cua ban thi la dung voi so thang con ngay thi bi lech
    minh muon la 5 thang thi tuc la den ngay 25/04/2011 moi la chinh xac
    ban co cach nao khac nua khong minh excel khog ranh lam minh lam mai ma khong tinh dc
    ban giup minh nha
    cam on ban nhieu

    • Vậy thì đây:
      1. Sử dụng hàm DATE
      A3=date(year(A1),month(A1)+A2,day(A1))
      2. Sử dụng hàm EDATE: Cộng tháng vào ngày-tháng-năm hịện hành
      Cú pháp EDATE(, )
      A3=edate(A1, A2)
      Lưu ý khi dùng EDATE:
      – Nếu kết quả không hiển thị dạng ngày-tháng-năm bạn phải định dạng lại cách hiển thị là Date trong lệnh Format Cell.
      – Hàm EDATE không phả là hàm mặc định trong Excel, bạn phải cài thêm mới có, nếu không Excel sẽ báo lỗi #Name.
      Cách thêm hàm: Vào Tools/Add-In đánh dấu kiểm vào hộp Analysis ToolPak rồi trả lời Ok đồng ý install là nó tự tìm bộ office cài đặt luôn (nếu máy không tìm thấy bộ nguồn Office bạn phải chỉ ra vị trí).
      Have fun!

  16. Minh cam on ban rat nhieu
    chuc ban luon manh khoe thanh cong hon nua va luon cap nhat nhung bai viet hay hon

  17. cháu có bài toán như thế này, nhờ chú giúp cháu vs
    A1 là 01/01/07 B1 là 10/01/07
    A2 là 02/01/07 B2 là 08/01/07
    A3 là 05/01/07 B3 là 09/01/07
    tính tiền được giảm nếu ở ngày 09/01/07 =10%số tiền của ngày đó
    và có 2 loại tiền phòng.
    có thể gửi vào mail của cháu được ko ạ? thak chú

    • Đề bài của bạn không rõ ràng nên tôi không thể trả lời được!

  18. Tôi muốn nhờ anh chỉ dẫn 1 chút:
    Tôi làm kinh doanh, các thông tin về khách hàng tôi lưu vào bảng excel. Để theo dõi thông tin khách hàng, tôi nhập ngày làm việc với khách hàng và ngày GỌI LẠI cho họ, thường là sau vài ngày hoặc đôi khi cả vài tháng (tôi bán hàng dự án nên thời gian theo 1 dự án tương đối lâu).
    Nhưng đôi khi ko thể theo dõi hết các thông tin. Tôi muốn hỏi anh làm sao đến ngày GỌI LẠI có cách nào để BÁO THỨC các thông tin đã lưu từ trước được ko?
    Vì lượng khách hàng khá nhiều nên tôi ko thể lưu trên outlook hay các phần mềm khác. Mong anh chỉ dẫn giúp, thành thật cảm ơn anh rất nhiều.
    Anh có thể gửi cho tôi qua email: caotrungdn@gmail.com

    Chúc anh vui vẻ

    • BAO DONG cua ban la o trong file excel luon phai khong?
      Gia su o A1 la ten khach, B1 la Ngay lam viec, C1 la ngay goi lai, o D1 Bao dong se co cong thuc
      IF(TODAY()>=C1,”Goi cho khach hang “&A1,””)
      Do la cach hieu cua toi!

  19. Em chào anh!
    Anh làm ơn hướng dẫn dùm em cách chuyển định dạng ngày tháng năm như sau với ạ:
    Chuyển từ dạng: 15/02/1986 2:25 PM thành 2:25 PM (không còn phần ngày thàng năm ở đó nữa).
    Em cảm ơn anh!

    • Bạn thực hiện thứ tự như sau:
      – bôi đen khối ô muốn chuyển
      – nhấn tổ hợp phím tắt Ctrl-1
      – xuất hiện bảng Forrmat Cell
      – trong thẻ Number, Chọn Custom trong ô Category
      – tại ô Typy gõ h:mm
      – nhấn OK
      Xong!

  20. Chào anh!
    Em mới tập tành làm excell nên còn nhiều bỡ ngỡ, có bài này mong anh giúp cho. Trong một danh sách khách hàng có cột khách hàng và cột ngày giao dịch, yêu cầu: đưa ra ngày giao dịch của khách hàng cuối cùng trong danh sách. Nếu yêu cầu là các khách hàng khác ví dụ như khách hàng thứ 3 thứ 4 chẳng hạn thì làm thế nào?
    Cảm ơn anh!

    • Theo cách hiểu của tôi thì bạn sử dụng hàm VLOOKUP để dò tìm.
      VD: Cột A: STT, Cột B: Tên khách, cột C: Ngày
      STT Khach Ngay GD
      1 a 1/2/1990
      2 b 8/7/1990
      3 c 6/8/1990
      4 d Feb-98
      5 r 7/8/1992
      6 g 7/5/1995
      7 e 4/5/2011
      8 t 7/8/2011
      9 y 6/9/2011
      10 d 1/5/2010
      Giả sử lại ô D15 ta nhập vào số 9 (ứng với khách hàng thứ 9 thì tại ô H15 sẽ hiển thị ngày giao dịch với khách hàng đó như sau:
      Cho biet thu thu tu cua khach hàng 9 Ngay GD la 09-06-2011
      Để được như vậy bạn sử dụng hàm VLOOKUP tại ô H15 như sau:
      VLOOKUP(D12,A2:C11,3)
      Bạn hãy tham khảo thêm về hàm này trên mạng nhé.

  21. T co bai exel day so phut cua o A3 la hieu cua o A2 va A1 t tinh ra so phut nhung de bao co yeu cau lam tron neu so giay la tren 30(gia su 1phut 30 giay thi ket qua la 2 phut ) anh giup toi voi! cam on rat nhieu

    • Sau đây là mọt cách:
      Bạn sử dụng hàm MINUTE lấy phút, hàm SECOND lấy giây. Để làm tròn số phút bạn kết hợp hàm IF.
      =IF(SECOND(A3)>=30,MINUTE(A3)+1,MINUTE(A3))

  22. Truoc khi nhan duoc tra loi toi da lam duoc roi
    cam on anh!

  23. Em phần bài này mà làm không ra mong anh giúp đỡ.
    Tên khách hang ngày đến Ngày đi Số tuần khách ở số ngày lẻ khách ở
    A 20/3/2010 31/6/2010 ? ?
    Em đang cần lam gấp,anh giúp em nhanh nhanh nha ^^ cám ơn anh nhiều.Thật vui vì tìm thấy blog này của anh.rất vui được quen anh!

    • Bạn lấy (ngày đi – ngày đến)/7 sẽ ra số tuần. Tôi không hiểu Số ngày lẻ khách ở là gì?

  24. Công việc tôi cần tính ra lịch dương hàng tháng trong excel để tiện cho việc tính toán số liệu theo hàng tuần và cả tháng, tôi thiết lập công thức sẳn sàng rồi đã sử dụng từ 2009 đến 2010 nhưng không hiểu sao tháng 12/2010 thì lịch nó lại nhảy trước đi một ngày vi dụ: Lịch đúng 1/12/2010 là ngày thứ 4 thì nó lại nhảy trước 1 ngày là ngày thứ 3.
    Còn sang năm 2011 thì tháng 1 nhảy đúng ngày, gõ vào tháng 2 thi nó lại nhảy trước 3 ngày, tôi không hiểu tại sao nữa. Xin Anh hỗ trợ và chỉ giúp tôi với, xin gửi file excel vào thư điện tử của tôi nhé.
    Cam on Anh rất nhiều.

    • Ban xem cai nay nhe, tai ve

  25. Em muon dat mac dinh tung o tra ve ket qua tung ngay, thang , nam. Chu ko phai 1 o tra cung luc ket qua ngay thang nam

    Vi du: o A1 tra ve mac dinh ngay, o A2, tra ve mac dinh thang, 0 A3 tra ve mac dinh nam.
    Anh huong dan giup em lam duoc ko a? Thanks

    • Ban co the dat kai cau hoi duoc khong?
      Toi khong biet luc truoc ban hoi cai gi (!)
      * De tach rieng ngay-thang-nam vao 3 o khac nhau ban dung 3 ham Day, Month và Year
      VD
      A1=25-12-2010
      A2=Day(A1) -> 25
      A3=Month(A1) -> 12
      A4=Year(A1) -> 2010

      • cam on, bai viet cua ban

  26. chào a!! cho e hỏi là: A1=02/03/2009, câu hỏi là nếu bán (a1) trước ngày 31/1 thì lấy giảm 5% sl*dg, nhưng khi tính thì e ko tính ngày được, e dùng hàm datevalue nưng vẫn ko đc. no báo ######## hay !value. cám ơn a nhìu!!!

    • Tôi đã tìm ra cách khắc phục.
      Cách 1: Bạn nhập vào một ô nào đó giá trị 31-1-2009, sau đó trong công thức so sánh với địa chỉ của ô này.
      Cách 2: Nếu sử dụng hàm DATEVALUE thì bạn phải đặt 31-1-2009 trong dấu nháy kép =IF(A1<DATEVALUE("31-1-2009", … , …)
      Thử đi nha! Chúc thành công.

  27. Chao A.
    Em không rành Excel lam mong anh giúp đỡ. Em đang cần tính công thực tế cho nhân viên xin anh chỉ giúp em công thức. Bên em làm từ T2 đến hết sáng ngày thứ 7.
    Em đã thử nhiều hàm nhưng đều cho kết quả ko dung.
    Em cam on anh nhiều

  28. Chao ban.
    Minh su dung dinh dang ngay thang nam trong excel de lap phieu thu chi, trong cung mot file minh cho ra nhieu sheet. Nhung co sheet minh sd dinh dang nay thi cho ket qua dung nhu mong muon, nhung co sheet cho ket qua la dd/mm/yyyy, mac du minh cung da dinh dang theo ngay thang nam nhung khong duoc.
    Phien ban giup minh som nhe. Cam on ban nhieu.

    • Để cho Excel hiển thị định dạng ngày-tháng-năm theo kiểu của Việt Nam cần thực hiện hai việc:
      1. Điều chỉnh trong Control Panel: Chọn Regional… (biểu tượng quả địa cầu), trong mục Regional Option chọn quốc gia English (Canada), OK
      2. Trong Excel bôi đen cột chứa ngày tháng năm, nhấn Ctrl-1, trong thẻ Number chọn Custom chọn dd-mm-yyyy, OK (tất cả các trang đều phải thục hiện như vậy). Thao tác này phải thục hiện trước khi nhập liệu, trường hợp nhập rồi thì xóa đi, gõ lại.
      Chúc thành công!

  29. Cam on ban, minh van dinh dang nhu ban noi truoc day, nhung chi bi 1 sheet la khong the hien dung con may sheet khac thi binh thuong, mac du minh da xoa va dinh dang lai nhung van khong duoc.

  30. Minh tim duoc loi roi, do minh su dung ham tham chieu o ngay cua sheet khac ma o ngay do minh chua dinh dang ngay ma minh sd dinh dang text

    • OK!

  31. xin chào anh.em có bài toán như sau,mong anh chỉ giáo.ví dụ em nhập vào ô a1 là ngày tháng năm,thì làm sao?rồi sau đó em muốn ở ô a2,a3,a4……tự động cộng thêm 7 ngày từ kết qủa của ô a1 mà ko cần phải nhập lại hàm như ô a1.xin cám ơn anh nhìu

    • Theo tôi hiểu thì bạn muốn:
      -Tại ô A1 bạn nhập dữ liệu ngày dạng ngày-tháng-năm? Cái này tôi đã nói rồi:
      Để cho Excel hiển thị định dạng ngày-tháng-năm theo kiểu của Việt Nam cần thực hiện hai việc:
      1. Điều chỉnh trong Control Panel: Chọn Regional… (biểu tượng quả địa cầu), trong mục Regional Option chọn quốc gia English (Canada), OK
      2. Trong Excel bôi đen ô (cột) chứa ngày tháng năm, nhấn Ctrl-1, trong thẻ Number chọn Custom chọn dd-mm-yyyy, OK (tất cả các trang đều phải thục hiện như vậy). Thao tác này phải thục hiện trước khi nhập liệu, trường hợp nhập rồi thì xóa đi, gõ lại.
      -Tại ô A2, A3, A4 bạn muốn nó tự động cộng thêm 7 ngày từ kết quả trong ô A1? Bạn chỉ việc lập công thức trong các ô đó là =A1+7, vậy là xong!
      Chúc thành công.

  32. xin cám ơn anh nhìu

    • Rất vui nếu bạn đã làm được!

  33. Tôi muốn lấy ra ngày, tháng, năm trong các cột ngày, tháng, năm đã có sẵn trong biểu nhưng không được. Máy của tôi định dạng ngày, tháng, năm theo kiểu: MM/DD/YYYY. Xin anh hãy chỉ dùm. Xin cảm ơn! Biểu tính như sau:

    TEN NG_VAY NG_DHAN
    Phùng Thị Hồng 29/08/2008 06/02/2012 2012
    Nguyễn Minh Nghĩa 29/08/2008 06/02/2012 2012
    Dương Thị An 29/08/2008 06/02/2012 2012
    Võ Thị Huệ 16/08/2008 16/02/2012 #VALUE!
    Điểu Thị Lốt 28/09/2009 20/03/2012 #VALUE!
    Nịnh Văn út 21/09/2009 23/03/2012 #VALUE!
    Bế Văn Hữu 14/04/2010 23/03/2012 #VALUE!
    Trần T.Thanh Thảo 25/09/2008 14/03/2012 #VALUE!

    • Theo cách hiểu của tôi thì chị muốn tách riêng ra ngày, tháng, năm của ô chứa ngày sinh. Nếu như vậy thì chị chỉ cần sử dụng 3 hàm, DAY, MONTH, YEAR là xong (với đối số là ô chwusa ngày sinh).
      Ví dụ: Ô A2 chứa giá trị nghày sinh là 22-12-1998
      tại ô B2 nếu gõ =DAY(A2) sẽ cho kết quả là 22
      tại ô C2 nếu gõ =MONTH(A2) sẽ cho kết quả là 12
      tại ô D2 nếu gõ =YEAR(A2) sẽ cho kết quả là 1988
      Chúc chị thành công.

  34. anh ơi cho em hỏi, em có một bài toán là nối ngày với số phòng, em dùng hàm & : D3=A3&-C3 thì nó ra dãy số 39814-305. 305 là đúng rồi vì đó là số phòng còn ngày khách đặt phòng lại thành 1 dãy số. Anh giúp em được ko ạ!

    • Bạn dùng cách này nha:
      =DAY(A1)&”/”&MONTH(A1)&”/”&YEAR(A1)&-B1
      với A1 là ô chứa ngày, B1 là ô chứa số phòng
      Chúc thành công!

  35. tôi gặp bài toán thế này, mong anh giúp đỡ!
    đó là một phạm nhân bị bắt ngày 23/7/2008 có mức án là 7 năm 3 tháng 24 ngày. vậy tính đến ngày bao nhiêu thì phạm nhân này chấp hành được 1/3 mức án? tính đến ngày hôm nay, phạm nhân đã chấp hành được bao nhiêu thời gian? còn lại bao nhiêu thời gian? (thời gian tính theo năm-tháng- ngày)
    thực tế chúng toi phải tính bằng cách quy mỗi tháng bằng 30 ngày, mỗi năm có 12 tháng là cơ sở để tính.nếu ngày cuối cùng của tháng nhỏ hơn ngày tính ra thì lấy ngày cuối cùng của tháng đó!nếu ngày tính ra nhỏ hơn ngày cuối cùng trong tháng tính được thì lấy ngày tính đó!

    • Anh hay quy thoi gian cua muc an ve don vi ngay theo cach quy doi cua cac anh, sau do lay ngay bat giu cong them so ngay da tinh la xong.

  36. anh ơi cho em hỏi muốn đổi format từ hh:mm:ss sang chỉ hh:mm thì làm thế nào ạ? Nếu chuyển ở format cells thì giá trị vẫn giữ nguyên nên ở bước sau em gặp vấn đề

  37. giúp e làm bái này với mọi người ơi:
    e có cột 1 là họ và chữ lót, cột 2 là tên, giờ e muốn lấy mấy chữ đầu trong cột 1 rồi gắn với tên trong cột 2, mà tên trong cột 2 là chữ in hoa ko có dấu
    Ví Dụ: cột 1: Nguyễn Văn, cột 2:Thắng, kết quả là: NVTHANG
    anh chị nào biết chỉ e với nhe, e nhớ ơn suốt đời :(

    • C6=LEFT(A6,1)&MID(A6,FIND(” “,A6)+1,1)&B6
      Voi A6 chua HoTen, o B6 chua Ten, o C6 la ket qua.

  38. ANH ƠI CHO EM HỎI ….EM CÓ MỌT BÀI TOÁN LÀ CỘT THANH TOÁN NẾU THANH TOÁN SAU NGÀY GIAO HÀNG NHỎ HƠN 10 NGÀY THÌ SE GIẢM ĐƯỢC 5%, CÒN NẾU KHÔNG THÌ VẪN TRẢ BÌNH THƯỜNGBÌNH THƯỜNG….EM MỚI LÀM NÊN KHÔNG RÀNH LẮM…EM CAM ƠN ANH MONG ANH GIÚP EM

    • D2=IF(B2-A2<10,95%*C2,C2)
      Voi A2 la ngay giao, B2 ngay thanh toan, C2 la so tien can thanh toan, D2 la tien thanh toan thuc su.

  39. Anh cho e hỏi công thức tính như thế nào đối với trường hợp này:
    – Có ngày nhận hàng.
    – Theo quy định phải giao hàng trong vòng 3 ngày làm việc, ko tính thứ 7, chủ nhật.
    – Vậy ngày giao hàng là ngày mấy?

    Cảm ơn anh đã đọc.

    • =IF(OR(WEEKDAY(A2)=2,WEEKDAY(A2)=3),A2+3,A2+5)
      Voi A2 la o ngay nhan don hang, B2 la ngay giao hang.
      Cong thuc tren se lay ngay nhan + 3 se ra ngay giao hang, tu dong khong tinh thu 7 va chu nhat vao ngay them vao.
      Than men!


Gửi phản hồi

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Log Out / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Log Out / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Log Out / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Log Out / Thay đổi )

Connecting to %s

Danh mục

Theo dõi

Get every new post delivered to your Inbox.

%d bloggers like this: