Nguyên nhân và cách xử lý lỗi hàm Excel về ngày tháng ở một số máy tính

 Trong thực tế rất nhiều người dùng Excel lập công thức sử dụng các hàm ngày tháng trả về kết quả lỗi, sai ở máy tính này nhưng lại đúng ở máy tính khác.

Nguyên nhân lỗi công thức hoặc sai kết quả khi sử dụng hàm ngày tháng

Nguyên nhân chủ yếu là dữ liệu ngày tháng ở dạng text hoặc nhập sai kiểu cấu trúc ngày tháng.

- Hàm ngày tháng nhận giá trị khác nhau giữa các máy tính. Ví dụ: ô A1 nhập là '10/11/2022 thì với máy tính có Control Panel kiểu dd/mm/yyyy thì dùng hàm =MONTH(A1) sẽ trả về 11, với máy tính kiểu mm/dd/yyyy thì hàm =MONTH(A1) sẽ trả về là 10

- Hàm ngày tháng trả kết quả lỗi ở một số máy tính. Ví dụ: Ô A1 nhập là '31/12/2022 thì với máy tính có Control Panell là dd/mm/yyyy thì dùng hàm =MONTH(A1) sẽ trả về 12, với máy tính kiểu mm/dd/yyyy thì hàm =MONTH(A1) sẽ trả về lỗi #VALUE! do không có tháng 31

Tương tự như vậy với các hàm thời gian khác như: DAY, YEAR, WEEKDAY, EDATE, WEEKNUM, ISOWEEKNUM, DATEDIF, WORKDAY, WORKDAY.INTL, NETWORKDAYS, NETWORKDAYS.INTL,... cũng trả về kết quả lệch giữa các máy hoặc lỗi khi dữ liệu ngày tháng ở dạng text hoặc sai kiểu giá trị.

Cách xử lý lỗi hoặc sai kết quả công thức khi dùng hàm ngày tháng

Trường hợp 1: Nếu dữ liệu ngày tháng ở dạng text thì cần convert về dạng date chuẩn. 

Làm theo hướng dẫn TẠI ĐÂY

Trường hợp 2: Nếu dữ liệu ngày tháng lộn xộn giữa kiểu text và kiểu date

Làm theo hướng dẫn TẠI ĐÂY

Trường hợp 3: Người dùng không phân biệt được giá trị ngày và tháng

Ví dụ với dữ liệu 11/12/2022 thì người dùng không biết được thực tế là tháng 11 hay tháng 12 thì phải kiểm tra thủ công thông qua chứng từ gốc để nhập và sửa lại.

Phương pháp hạn chế lỗi hoặc sai kết quả khi dùng hàm ngày tháng

- Các máy tính trong công ty nên đưa về đồng nhất kiểu hiển thị ngày tháng trong Control Panel. Ví dụ: Tất cả các máy đều là dd/mm/yyyy

- Khi nhập dữ liệu ngày tháng thì người dùng cần nhìn đồng hồ máy tính xem hiển thị kiểu nào để nhập số liệu đúng theo kiểu đó

- Tạo thêm cột phụ bên cạnh có công thức chờ sẵn để kiểm tra. Ví dụ: Cột phụ dùng hàm MONTH

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp

{Đt Zalo} - 038 696 1334

Xem chi tiết

Chia sẻ công thức tách ngày tháng và thời gian trong chuỗi số

 Trong nhiều tình huống nhập liệu hoặc xuất ra từ hệ thống phần mềm thì dữ liệu ngày tháng được hiển thị ở dạng chuỗi số. Ví dụ: 20221010 hoặc 202210101830,....

Chìa khóa để giải bài toán này là dùng các hàm tách chuỗi để tách riêng phần năm, tháng, ngày, giờ, phút rồi sau đó gộp lại thành giá trị ngày tháng và thời gian chuẩn

Giả sử chuỗi được nhập theo cấu trúc yyyymmddhhmm (Ví dụ: 202211120930) thì: 4 ký tự đầu là phần năm, 2 ký tự tiếp theo là phần tháng, 2 ký tự tiếp theo là phần ngày,...

Tách 4 ký tự phần năm:

=MID(A1,1,4) hoặc =LEFT(A1,4)

Tách 2 ký tự phần tháng

=MID(A1,5,2)

Tách 2 ký tự phần ngày

=MID(A1,7,2)

Dùng hàm DATE để hợp các giá trị đã tách thành giá trị ngày tháng chuẩn:

=DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))

Tách 2 ký tự phần giờ:

=MID(A1,9,2)

Tách 2 ký tự phần phút:

=MID(A1,11,2)

Dùng hàm TIME để hợp nhất các giá trị đã tách thành giá trị thời gian chuẩn:

=TIME(MID(A1,9,2),MID(A1,11,2),0)

Và dùng hàm DATE và TIME đưa các giá trị đã tách thành giá trị ngày tháng và thời gian đầy đủ:

=DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),0)

Thực hiện Format ô kết quả về dạng Format Custom dd/mm/yyyy hh:mm

Tương tự như vậy, nếu chuỗi giá trị ngày tháng được xuất ra theo cấu trúc khác như ddmmyyyy hoặc mmddyyyy,... thì cũng dùng hàm MID để tách các khối ngày tháng năm ở các vị trí trong chuỗi số đó.

Trường hợp phần năm được viết thiếu theo cấu trúc như yymmdd (ví dụ: 221112) thì vẫn dùng hàm MID để tách phần năm nhưng cần ghép thêm 20 vào đằng trước. Khi đó công thức sẽ là:

=DATE("20" & MID(A1,1,2),MID(A1,3,2),MID(A1,5,2))

Ngoài ra, thực tế có thể phát sinh tình huống giá trị ngày tháng nằm trong 1 chuỗi văn bản (ví dụ như trong diễn giải, ghi note thông tin) và cần tách giá trị ngày tháng riêng thì tham khảo công thức TẠI ĐÂY

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp

{Đt Zalo} - 038 696 1334

Xem chi tiết

Chia sẻ công thức Excel đọc ngày tháng thành chữ

 Trong nhiều tình huống tạo form chứng từ, hợp đồng, hơn hàng, phiếu,... trên Excel thì người dùng thường phải đưa giá trị ngày tháng sang dang chữ. Dưới đây là một số tình huống đọc giá trị ngày tháng thành chữ

Tình huống 1: Chuyển giá trị ngày tháng sang giá trị văn bản thuần (Chuyển Date sang Text)

Công thức chuyển: =TEXT(A1,"dd/mm/yyyy") hoặc =TEXT(A1,"mm/dd/yyyy") .... tùy theo hiển thị người dùng mong muốn

Thông thường cách chuyển này được áp dụng trong ghép chuỗi. Ví dụ:

="Ngày ký: " & TEXT(A1,"dd/mm/yyyy")

Tình huống 2: Đọc giá trị ngày tháng Tiếng Việt

Công thức đọc có Tỉnh/Thành đi cùng:

=TEXT(A1,"""Hà Nội, ngày "" dd ""tháng"" mm ""năm"" yyyy")

Công thức đọc có Thứ đi cùng:

=TEXT(A1,"[$-42A]dddd, ""ngày "" dd ""tháng"" mm ""năm"" yyyy")

Tình huống 3: Đọc giá trị ngày tháng Tiếng Anh

Công thức đọc có thứ và tháng đầy đủ:

=TEXT(A1,"dddd dd mmmm yyyy")

Trường hợp tên thứ và tháng được viết tắt thì công thức là:

=TEXT(A1,"ddd dd mmm yyyy")

Công thức đọc có phân biệt st, nd, rd, th:

=TEXT(A1,"dd")&LOOKUP(DAY(A1),{1,2,3,4,21,22,23,24,31},{"st","nd","rd","th","st","nd","rd","th","st"}) & TEXT(A1," mmmm yyyy")

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp

{Đt Zalo} - 038 696 1334

Xem chi tiết

Chia sẻ công thức Excel đổi dương lịch sang âm lịch

 Trong thực tế đôi lúc người dùng cần đưa dữ liệu ngày tháng về dạng âm lịch cho tiện theo dõi như: Kế hoạch hoặc hẹn ngày gọi điện nhắc nợ, thanh toán nên tránh ngày mùng một âm lịch

Bài viết sẽ chia sẻ công thức chuyển ngày tháng dương lịch sang âm lịch:

CÔNG THỨC EXCEL ĐỔI NGÀY THÁNG DƯƠNG LỊCH SANG ÂM LỊCH

Giả sử ô A1 nhập ngày dương lịch bất kỳ thì công thức chuyển đổi là:

=TEXT(A1,"[$-11042A]dd/mm/yyyy")

Tương tự, nếu chuyển ngày hôm nay sang âm lịch thì công thức là:

=TEXT(TODAY(),"[$-11042A]dd/mm/yyyy")

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp

{Đt Zalo} - 038 696 1334

Xem chi tiết

Chia sẻ 06 trường hợp công thức Excel tính từ ngày đến ngày

 Trong thực tế việc tính toán dữ liệu ngày tháng trong khoảng thời gian từ ngày đến ngày được sử dụng thường xuyên như tính thâm niên, công chuẩn, số ngày hoàn thành công việc,....

Tình huống 1: Tính số ngày trong khoảng thời gian từ ngày đến ngày

Ví dụ: Ô A1 nhập 01/10/2022, ô B1 nhập 10/10/2022 thì khi đó sẽ có trường hợp:

Trường hợp 1: Tính cả ngày đầu và ngày cuối (tức tính cả ngày 01 và ngày 10)

Khi đó công thức tính số ngày là: =B1-A1+1

Trường hợp 2: Tính từ ngày bắt đầu đến trước ngày, hoặc tính từ sau ngày bắt đầu đến hết ngày kết thúc

Khi đó công thức tính số ngày là: =B1-A1

Trong tình huống này, nếu sử dụng hàm DATEDIF kiểu "d" để tính ra số ngày thì hàm DATEDIF sẽ theo trường hợp số 2. Tức: =DATEDIF(A1,B1,"d")

Tình huống 3: Tính số ngày làm việc trong khoảng thời gian - trừ ngày chủ nhật và nghỉ lễ

Ví dụ: Ô A1 nhập 01/10/2022, ô B1 nhập 10/10/2022 và từ S1:S20 là các ngày nghỉ lễ năm 2022

=NETWORKDAYS.INTL(A1,B1,11,S1:S20)

Tình huống này có thể tham khảo công thức tính công chuẩn TẠI ĐÂY

Tình huống 4: Tính số ngày chủ nhật hoặc thứ bảy trong khoảng thời gian

Ví dụ: Ô A1 nhập 01/10/2022, ô B1 nhập 31/10/2022

Công thức tính số ngày chủ nhật

=NETWORKDAYS.INTL(A1,B1,"1111110")

Công thức tính số ngày thứ 7

=NETWORKDAYS.INTL(A1,B1,"1111101")

Công thức tính số ngày thứ 7 và chủ nhật

=NETWORKDAYS.INTL(A1,B1,"1111100")

Tình huống số 5: Tính số năm, số tháng, hoặc tính số năm, số tháng lẻ, số ngày lẻ

Ví dụ: Ô A1 nhập ngày 15/02/2020, ô B1 nhập ngày 31/10/2022

Công thức tính số năm là: 

=DATEDIF(A1,B1,"y")

Công thức tính số tháng là:

=DATEDIF(A1,B1,"m")

Công thức tính số tháng lẻ còn lại là:

=DATEDIF(A1,B1,"ym")

(Kết quả công thức tính số tháng lẻ còn lại thì kết quả luôn nhỏ hơn 12)

Công thức tính số ngày lẻ còn lại là: 

=DATEDIF(A1,B1,"md")

Tình huống này có thể tham khảo cong thức tính thâm niên TẠI ĐÂY

Tình huống số 6: Tính số năm có phần thập phân

Ví dụ: Ô A1 nhập ngày 15/02/2020, ô B1 nhập ngày 31/10/2022

Công thức tính số năm có phần thập phân (tức số tháng lẻ sẽ được quy về năm)

=YEARFRAC(A1,B1,1)

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp

{Đt Zalo} - 038 696 1334

Xem chi tiết

Chia sẻ công thức Excel tính ngày hết hạn theo các tình huống

 Việc tính ngày hết hạn có ý nghĩa quan trọng trong quản trị thông tin để đưa ra kế hoạch hoặc quyết định. 

Tình huống 1: Ngày hết hạn sau bao nhiêu ngày

Giả sử ô A1 là ngày bắt đầu, hạn trong vòng 15 ngày thì công thức tính ngày hết hạn là:

=A1+15

Tình huống này áp dụng trong các trường hợp sau:

- Hạn sử dụng của các sản phẩm có thời gian sử dụng ngắn. Ví dụ như: Thực phẩm, đồ tươi sống,...

- Số ngày lưu kho tối đa

- Ngày hết hạn thử việc. Theo luật lao động thì thời gian thử việc sẽ không quá 30 ngày hoặc 60 ngày theo từng vị trí. Khi đó công thức sẽ là =A1+30 hoặc =A1+60. Nếu dùng theo phương pháp sau 1 hoặc 2 tháng trong đó có tháng 31 ngày thì thời gian thử việc sẽ lớn hơn 30 hoặc 60 ngày

- Trường hợp tính theo tuần thì một tuần có 7 ngày nên số ngày=số tuần*7. Giả sử ngày hết hạn sau 3 tuần thì công thức là:

=A1+3*7

Tình huống số 2: Ngày hết hạn sau bao nhiêu tháng

Tình huống này gặp khá phổ biến, đặc biệt là tính hạn sử dụng sản phẩm, hết hạn hợp đồng lao động, công nợ hạn thanh toán theo tháng,...

Giả sử A1 là ngày bắt đầu, ngày hết hạn sau 6 tháng thì công thức tính ngày hết hạn là:

=EDATE(A1,6)

Khi đó tương tự, nếu sau 1 năm thì =EDATE(A1,12) và nếu sau 3 năm thì =EDATE(A1,36)

Tức ý nghĩa tham số thứ 2 của hàm EDATE là tìm ra ngày sau đó bao nhiêu tháng

Tình huống 3: Ngày hết hạn sau bao nhiêu tháng bao nhiêu ngày

Tình huống này kết hợp cho cả tình huống số 1 và số 2. 

Giả sử ô A1 là ngày bắt đầu, thời hạn sau 3 tháng 15 ngày thì công thức tính ngày hết hạn là:

=EDATE(A1,3)+15

Tình huống 4: Ngày hết hạn là ngày cuối tháng sau bao nhiêu ngày hoặc bao nhiêu tháng

Tình huống này thường gặp về quy định hạn thanh toán công nợ của một số doanh nghiệp. 

Giả sử A1 là ngày bắt đầu, thời hạn sau 3 tháng nhưng sẽ lấy ngày cuối cùng của tháng thứ 3 thì công thức tính ngày hết hạn là:

=EOMONTH(EDATE(A1,3),0)

Trường hợp cố định hạn thanh toán vào ngày nào đó trong tháng. Ví dụ: Thời hạn sau 3 tháng nhưng sẽ lấy ngày 25 của tháng thứ 3 thì công thức là:

=DATE(YEAR(EDATE(A1,3)),MONTH(EDATE(A1,3)),25)

Tình huống 5: Ngày hết hạn sau bao nhiêu ngày làm việc, không tính thứ 7, chủ nhật và nghỉ lễ

Tình huống này thường gặp trong quản lý công việc, giao khoán, dự án,...

Giả sử ô A1 là ngày bắt đầu, thời hạn sau 20 ngày không tính thứ 7, chủ nhật và nghỉ lễ thì công thức tính ngày hết hạn là:

=WORKDAY.INTL(A1,20,1,$D$2:$D$13)

(Trong đó D2:D13 là danh sách các ngày nghỉ lễ trong năm 2022)

Trường hợp thời hạn sau 20 ngày và không tính chủ nhật và nghỉ lễ thì công thức tính ngày hết hạn là

=WORKDAY.INTL(A1,20,11,$D$2:$D$13)

Ngoài ra có thể tham khảo sử dụng Conditional formatting để cảnh báo quá hạn và cận hạn TẠI ĐÂY

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334

Xem chi tiết

Các công thức Excel tạo tên Thứ trong tuần

 Trong các biểu mẫu thường xuyên phải đưa thông tin về Thứ trong tuần để rõ thông tin hơn và biểu mẫu chuyên nghiệp hơn như: Bảng chấm công, bảng kế hoạch công việc,...

CHIA SẺ CÁC CÔNG THỨC EXCEL TẠO THỨ TRONG TUẦN

Thứ viết tắt (Ví dụ: Mon)

- Tiếng Anh: =TEXT(A1,"ddd") - Nếu giáo diện excel không phải Tiếng anh thì: =TEXT(TODAY(),"[$-en-GB]ddd")

- Tiếng Việt: =TEXT(A1,"[$-42A]ddd")

- Tiếng Hàn: =TEXT(A1,"[$-412]ddd")

- Tiếng Nhật: =TEXT(A1,"[$-411]ddd")

- Tiếng Trung: =TEXT(A1,"[$-804]ddd")

Thứ viết đầy đủ: (Ví dụ: Monday)

- Tiếng Anh: =TEXT(A1,"dddd") - Nếu giáo diện excel không phải Tiếng anh thì: =TEXT(TODAY(),"[$-en-GB]dddd")

- Tiếng Việt: =TEXT(A1,"[$-42A]dddd")

- Tiếng Hàn: =TEXT(A1,"[$-412]dddd")

- Tiếng Nhật: =TEXT(A1,"[$-411]dddd")

- Tiếng Trung: =TEXT(A1,"[$-804]dddd")

Thứ viết dạng kết hợp: (Ví dụ: Thứ 2)

=IF(WEEKDAY(A1)=1,"Chủ nhật","Thứ " & WEEKDAY(A1))

Tham khảo bảng danh sách các mã code ngôn ngữ. Cấu trúc [$-code]. Ví dụ: [$-040C] là Format cho Tiếng Pháp

Code Language

0436 Afrikaans

041C Albanian

045E Amharic

0401 Arabic

042B Armenian

044D Assamese

082C Azeri (Cyrillic)

042C Azeri (Latin)

042D Basque

0423 Belarusian

0445 Bengali

0402 Bulgarian

0403 Catalan

045C Cherokee

0804 Chinese (Simplified)

0404 Chinese (Traditional)

041A Croatian

0405 Czech

0406 Danish

0465 Dhivehi

0413 Dutch

0466 Edo

0C09 English (Australian)

1009 English (Canadian)

0809 English (U.K.)

0409 English (U.S.)

0425 Estonian

0438 Faeroese

0464 Filipino

040B Finnish

040C French

0C0C French (Canadian)

0462 Frisian

0467 Fulfulde

0456 Galician

0437 Georgian

0407 German

0C07 German (Austrian)

0807 German (Swiss)

0408 Greek

0447 Gujarati

0468 Hausa

0475 Hawaiian

040D Hebrew

0439 Hindi

040E Hungarian

0469 Ibibio

040F Icelandic

0470 Igbo

0421 Indonesian

045D Inuktitut

0410 Italian

0411 Japanese

044B Kannada

0471 Kanuri

0460 Kashmiri (Arabic)

043F Kazakh

0457 Konkani

0412 Korean

0440 Kyrgyz

0476 Latin

0426 Latvian

0427 Lithuanian

042F Macedonian FYROM

043E Malay

044C Malayalam

043A Maltese

0458 Manipuri

044E Marathi

0450 Mongolian

0461 Nepali

0414 Norwegian Bokmal

0814 Norwegian Nynorsk

0448 Oriya

0472 Oromo

0463 Pashto

0429 Persian

0415 Polish

0416 Portuguese (Brazil)

0816 Portuguese (Portugal)

0446 Punjabi

0418 Romanian

0419 Russian

044F Sanskrit

0C1A Serbian (Cyrillic)

081A Serbian (Latin)

0459 Sindhi

045B Sinhalese

041B Slovak

0424 Slovenian

0477 Somali

0C0A Spanish

0441 Swahili

041D Swedish

045A Syriac

0428 Tajik

045F Tamazight (Arabic)

085F Tamazight (Latin)

0449 Tamil

0444 Tatar

044A Telugu

041E Thai

0873 Tigrigna (Eritrea)

0473 Tigrigna (Ethiopia)

041F Turkish

0442 Turkmen

0422 Ukrainian

0420 Urdu

0843 Uzbek (Cyrillic)

0443 Uzbek (Latin)

042A Vietnamese

0478 Yi

043D Yiddish

046A Yoruba

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334

Xem chi tiết

Công thức Excel tìm ngày đầu và cuối của tuần, tháng, quý và năm

 Trong nhiều tình huống đưa vào điều kiện trong tính toán, thống kê, người dùng cần tìm ra ngày bắt đầu và ngày cuối cùng theo tuần, theo tháng, theo quý và theo năm

CÔNG THỨC TÌM NGÀY ĐẦU TUẦN VÀ NGÀY CUỐI TUẦN

Tình huống: Từ 1 ngày bất kỳ, giả sử ô A1 là ngày 09/09/2022 và tuần được tính từ Thứ 2 đến hết Chủ nhật

Công thức tính ngày đầu tuần: =A1-WEEKDAY(A1,2)+1

Công thức tính ngày cuối tuần: =A1-WEEKDAY(A1,2)+7

Trường hợp nếu coi tuần được tính từ Chủ nhật đến hết Thứ 7 thì:

Công thức tính ngày đầu tuần: =A1-WEEKDAY(A1)+1

Công thức tính ngày cuối tuần: =A1-WEEKDAY(A1)+7

CÔNG THỨC TÌM NGÀY ĐẦU THÁNG VÀ NGÀY CUỐI THÁNG

Tình huống: Từ 1 ngày bất kỳ, giả sử ô A1 là ngày 09/09/2022 thì:

Công thức tính ngày đầu tháng: =EOMONTH(A1,-1)+1

Công thức tính ngày cuối tháng: =EOMONTH(A1,0)

Công thức đếm số ngày trong tháng có bao nhiêu ngày: =DAY(EOMONTH(A1,0))

(Lưu ý: Công thức chạy chính xác cho cả năm nhuận)

CÔNG THỨC TÌM NGÀY ĐẦU QUÝ VÀ NGÀY CUỐI QUÝ

Tình huống: Từ 1 ngày bất kỳ, giả sử ô A1 là ngày 09/09/2022 thì:

Công thức tính ngày đầu quý là: =EOMONTH(A1,-MOD(MONTH(A1)-1,3)-1)+1

Công thức tính ngày cuối quý là: =EOMONTH(A1,2-MOD(MONTH(A1)-1,3))

Công thức đếm số ngày trong quý có bao nhiêu ngày: =EOMONTH(A1,2-MOD(MONTH(A1)-1,3))-EOMONTH(A1,-MOD(MONTH(A1)-1,3)-1)

CÔNG THỨC TÌM NGÀY ĐẦU NĂM VÀ CUỐI NĂM

Tình huống: Từ 1 ngày bất kỳ, giả sử ô A1 là ngày 09/09/2022 thì:

Công thức tính ngày đầu năm là: =DATE(YEAR(A1),1,1)

Công thức tính ngày cuối năm là: =DATE(YEAR(A1)+1,1,)

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334

Xem chi tiết

Chia sẻ công thức tính số tuần trong Excel

 Như đã biết thì 1 năm có khoảng 52-53 tuần, khi đó trong các bài toán quản trị thường xuyên có phát sinh nhu cầu tính toán thống kê theo tuần

Trường hợp 1: Tính tuần theo năm và ngày 01/01 luôn thuộc tuần 1

Sử dụng hàm WEEKNUM

Cấu trúc: =WEEKNUM(serial_number,return_type)

Trong đó: return_type nếu để là 1 thì ngày đầu tuần tính từ Chủ nhật đến hết Thứ 7, nếu để là 2 thì ngày đầu tuần tính từ Thứ 2 đến hết Chủ nhật

Ví dụ: ô A1 nhập giá trị là 08/08/2022 thì công thức tại ô B1 =WEEKNUM(A1,2) sẽ trả về kết quả là 33. Tức ngày 08/08/2022 thuộc tuần thứ 33 của năm 2022 và tuần tính từ Thứ 2 đến hết Chủ nhật

Khi đó, nếu hàm WEEKNUM vào ngày cuối năm thì sẽ tính ra số tuần trong năm đó

Trường hợp 2: Tính tuần theo năm và xét tuần đầu tiên có bao nhiêu ngày

Sử dụng hàm ISOWEEKNUM

Cấu trúc: =ISOWEEKNUM(date)

Trong đó: Hàm ISOWEEKNUM luôn coi tuần được tính từ Chủ nhật đến hết Thứ 7

Hàm ISOWEEKNUM sẽ xét như sau: Nếu tuần đầu tiên <4 ngày thì sẽ được gộp vào tuần cuối cùng của năm trước đó, nếu >=4 ngày thì ngày 01/01 sẽ ở tuần thứ nhất

Ví dụ: ô A1 nhập giá trị là 01/01/2022 thì hàm ISOWEEKNUM  tại ô B1 là =ISOWEEKNUM(B1) thì trả về là 52. Do ngày 01/01/2022 rơi vào thứ 7 thì ngày này sẽ được tính thuộc tuần thứ 52 của năm 2021

Khi đó nếu ô A1 nhập là 08/08/2022 thì công thức tại ô B1 =ISOWEEKNUM(A1) sẽ trả về kết quả là 32

Trường hợp 3: Tính tuần theo tháng và tuần 1 của tháng luôn tính từ ngày mùng 1 đến hết chủ nhật đầu tiên

Sử dụng hàm WEEKNUM

Ví dụ: Ô A1 nhập ngày 08/08/2022 thì công thức tại ô B1 là:

=WEEKNUM(A1)-WEEKNUM(EOMONTH(A1,-1)+1)+1

Kết quả là =2 tức ngày 08/08/2022 thuộc tuần thứ 2 trong tháng 8/2022

Như vậy, nếu A1 là ngày cuối tháng thì sẽ xác định được có bao nhiêu tuần trong tháng

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334

Xem chi tiết

Một số lưu ý khi dùng hàm DATE trong Excel

 Hàm DATE là hàm rất phổ biến trong Excel để trả về giá trị ngày tháng đầy đủ từ các giá trị ngày, tháng, năm rời rạc

Cấu trúc: =DATE(year, month, day)

Giả sử: =DATE(2022,8,8) thì kết quả trả về là 08/08/2022

MỘT SỐ LƯU Ý ĐẶC BIỆT KHI DÙNG HÀM DATE

1. Các giá trị year, month, day là các con số nhưng ở dạng Text thì hàm DATE vẫn chạy bình thường

Ví dụ: =DATE("2022",8,8) => "2022" đưa vào nháy kép thì được hiểu là TEXT và hàm DATE vẫn chạy ra kết quả đúng

2. Giá trị day lớn hơn 31 ngày

Trường hợp này hàm DATE vẫn chạy bình thường

Ví dụ: =DATE(2022,8,40) thì kết quả trả về là: 09/09/2022. Tức tháng 8 có 31 ngày, số ngày còn lại là 40-31=9 ngày. Tức số ngày thừa được tính sang tháng sau đó

3. Giá trị month lớn hơn 12 tháng

Trường hợp này hàm DATE vẫn chay bình thường

Ví dụ: =DATE(2022,15,3) thì kết quả trả về là 03/03/2023. 

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334

Xem chi tiết

Chia sẻ Công thức Excel tính số Quý và ngày bắt đầu kết thúc Quý

 Như đã biết thì trong Excel có các hàm DAY, MONTH, YEAR để lấy các giá trị ngày, tháng, năm và WEEKDAY để lấy giá trị thứ. Tuy nhiên lại không có hàm lấy ra số Quý.

CÔNG THỨC TÍNH SỐ QUÝ TRONG EXCEL

Giả sử ô A2 nhập giá trị ngày tháng bất kỳ, công thức tính số Quý như sau:

=INT((MONTH(A2)-1)/3)

CÔNG THỨC TÍNH NGÀY BẮT ĐẦU VÀ KẾT THÚC QUÝ TỪ SỐ QUÝ

Giả sử ô B1 nhập số năm, ô B2 nhập số Quý thì 

Công thức tính ngày đầu Quý là:

=DATE(B1,B2*3-2,1)

Và công thức tính ngày cuối Quý là:

=DATE(B1,B2*3+1,0)

CÔNG THỨC TÌM NGÀY ĐẦU VÀ CUỐI QUÝ TỪ NGÀY BẤT KỲ

Thì công thức tìm ngày đầu Quý là:

=DATE(YEAR(A2),FLOOR(MONTH(A2)-1,3)+1,1)

Và công thức tìm ngày cuối Quý là:

=DATE(YEAR(A2),CEILING(MONTH(A2),3)+1,0)

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334

Xem chi tiết

Sự khác nhau giữa hàm WEEKNUM và hàm ISOWEEKNUM

 Như đã biết thì một năm có 365 ngày (không nhuận) và 366 ngày (nhuận), tương ứng với đó là 1 năm sẽ có 52-53 tuần. Khi đó, Excel sẽ có 2 hàm trả về số tuần trong năm là WEEKNUM và ISOWEEKNUM

Ví dụ 1: Giả sử ô A1 gõ ngày 10/10/2020 và ô B1, C1 dùng lần lượt hàm WEEKNUM và ISOWEEKNUM thì kết quả đều trả về là 41 => Tức ngày 10/10/2020 thuộc tuần thứ 41 trong năm 2022

Nhưng nếu nhập ô A1 là ngày 10/10/2022 thì kết quả ô B1 dùng hàm WEEKNUM sẽ trả về 42 còn ô C1 dùng hàm ISOWEEKNUM trả về 41

ĐẶC ĐIỂM KHÁC BIỆT GIỮA HÀM WEEKNUM VÀ ISOWEEKNUM

(Xét cho trường hợp thứ 2 là ngày đầu tuần)

- Nếu ngày 01/01 của năm đó rơi vào thứ 6,7 hoặc Chủ nhật thì hàm ISOWEEKNUM sẽ coi ngày 01/01 thuộc về tuần cuối cùng của năm trước
- Nếu ngày 01/01 của năm đó rơi vào thứ 2, 3, 4 hoặc thứ 5 thì hàm ISOWEEKNUM sẽ coi ngày 01/01 thuộc về tuần đầu tiên của năm đó

- Còn hàm WEEKNUM thì luôn coi ngày 01/01 luôn thuộc tuần đầu tiên là Tuần số 1

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334

Xem chi tiết

Hướng dẫn phép tính cộng trừ thời gian trong Excel

 Trong nhiều trường hợp về chấm công hoặc quản lý công việc theo thời gian (giờ, phút, giây) thì người dùng cần sử dụng đến các phép tính cộng, trừ thời gian

HƯỚNG DẪN MỘT SỐ PHÉP TÍNH CỘNG TRỪ THỜI GIAN

Trường hợp 1: Cộng trừ thêm bao nhiêu giờ, bao nhiêu phút

Ví dụ: Thời gian bắt đầu công việc và yêu cầu hoàn thành sau bao nhiêu phút

- Giả sử sau 90 phút thì công thức là: =A2+TIME(0,90,0)

- Giả sử sau 4 tiếng thì công thức là: =A2+TIME(4,0,0)

- Giả sử sau 2 tiếng 50 phút thì công thức là: =A2+TIME(2,50,0)

- Thực hiện tương tự đối với trừ đi bao nhiêu giờ, bao nhiêu phút

Trường hợp 2: Trừ 2 khoảng thời gian cho nhau

Ví dụ: Thời gian kết thúc trừ thời gian bắt đầu

- Giả sử tính ra bao nhiêu giờ: =(B2-A2)*24 => Format ô kết quả về General

- Giả sử tính ra bao nhiêu phút: =(B2-A2)*24*60 => Format ô kết quả về General

- Giả sử tính ra số giờ và số phút: =TEXT(B2-A2,"[hh]:mm")

Trường hợp 3: Cộng trừ thêm bao nhiêu giờ và bao nhiêu phút (có giá trị ngày tháng đi cùng)

Trường hợp này công thức cũng tương tự như trường hợp 1

Nếu cộng thêm bao nhiêu ngày, bao nhiêu giờ: Giả sử sau 1 ngày 2 tiếng thì công thức là

=A2+1+TIME(2,0,0)

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334

Xem chi tiết

Chia sẻ công thức Excel đếm số ngày nghỉ trong năm

 Trong quản lý nhân sự hoặc nhiều công việc khác cần tính số ngày nghỉ trong năm hoặc theo một khoảng thời gian bất kỳ gồm: Nghỉ cuối tuần, Nghỉ lễ. Trong đó:

- Nghỉ cuối tuần: Có doanh nghiệp áp dụng nghỉ Thứ 7 và Chủ nhật, có doanh nghiệp chỉ nghỉ Chủ nhật, có doanh nghiệp nghỉ chiều Thứ 7 và Chủ nhật,...

- Nghỉ lễ: Phần này phụ thuộc vào quy định của nhà nước về các ngày nghỉ lễ trong năm và nghỉ bù. Do vậy phần này sẽ điền tay danh sách ngày nghỉ

Công thức tính số ngày Chủ nhật trong năm

=NETWORKDAYS.INTL(DATE(2022,1,1),DATE(2022,12,31),"1111110")

Công thức tính số ngày Thứ bảy và Chủ nhật trong năm

=NETWORKDAYS.INTL(DATE(2022,1,1),DATE(2022,12,31),"1111100")

Công thức tính nửa ngày Thứ bảy và cả ngày Chủ nhật trong năm

=SUMPRODUCT(NETWORKDAYS.INTL(DATE(2022,1,1),DATE(2022,12,31),"11111"&{"01","10"})*{0.5,1})

Cách dùng:

- Để tính cho cả năm thì thay số năm 2022 bằng số năm tương ứng

- Để tính cho một khoảng bất kỳ thay hàm DATE. Ví dụ: DATE(2022,2,1) và DATE(2022,2,28) => Để tính số ngày nghỉ cho Tháng 2/2022

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt+Zalo} - 038 696 1334

Xem chi tiết