Biết cách sử dụng các giá trị ngày tháng trong Excel có thể giúp bạn tiết kiệm rất nhiều thời gian trong công việc bảng tính hàng ngày. Hãy để chúng tôi chuẩn bị cho buổi hẹn hò của bạn với tờ giấy sử dụng 10 mẹo hữu ích này.
Trước khi chuyển sang các mẹo, sẽ giúp bạn biết cách biểu diễn ngày và giờ của excel.
Microsoft Excel lưu trữ ngày tháng dưới dạng số tuần tự … Ngày 1 tháng 1 năm 1900 là số sê-ri 1 và ngày 28 tháng 7 năm 2021 là số sê-ri 44405 vì nó là 44.405 ngày sau ngày 1 tháng 1 năm 1900. Excel lưu trữ thời gian dưới dạng phân số thập phân vì thời gian được coi là một phần của một ngày.
Vì vậy, bạn thấy, Ngày và Giờ trên thực tế là các số trong Excel. Chỉ cần nhập ngày vào trang tính excel của bạn và định dạng nó dưới dạng số để xem giá trị số tương đương của nó. Nếu một ngày là 29-July-2021 và Excel đại diện cho nó là 44406.
Tương tự, 9 giờ chiều ngày 29 tháng 7 năm 2021 được biểu thị là 44406,875
Cách sử dụng công thức và giá trị ngày tháng trong Excel
Bây giờ bạn đã biết bí mật nhỏ đằng sau ngày / giờ, hãy chuyển sang 10 mẹo.
1. Kiểm tra xem một ngày là tương lai hay quá khứ
Bạn có thể tìm xem một ngày là quá khứ hay tương lai hoặc ngày hôm nay bằng cách sử dụng công thức if đơn giản như:=if(this_date=today(),"Today",if(this_date < today(),"Past","Future"))
today()là chức năng của bảng tính mà bạn có thể tìm thấy ngày hôm nay.
2. Tìm số ngày giữa hai ngày
Vì ngày tháng được biểu diễn dưới dạng số liên tiếp trong excel, nên để biết có bao nhiêu ngày giữa 2 ngày đã cho bất kỳ, chỉ cần trừ một ngày cho một ngày khác. Ví dụ. bạn có thể sử dụng =today()-date(1947,8,15)để tìm bao nhiêu ngày kể từ ngày độc lập của Ấn Độ (15 tháng 8 năm 1947).
3. Định dạng ngày tháng
Có ngày / giờ trong trang tính là không đủ nếu bạn không thể làm cho nó trông giống như cách bạn muốn. Ví dụ. bạn có thể muốn hiển thị ngày là “Thứ Tư, ngày 28 tháng 7 năm 2021”. Bạn có thể sử dụng định dạng ô để thực hiện việc này. Chỉ cần chọn ô có ngày tháng và nhấn ctrl + 1 và trong tab “Số”, chọn “Tùy chỉnh” làm danh mục và đề cập “dddd, dd mmmm, yyyy” dưới dạng chuỗi định dạng.
Hãy thử các định dạng ngày tháng khác.
Tìm hiểu thêm về định dạng ô tùy chỉnh .
4. Chỉ tự động điền vào các ngày trong tuần
Tất cả chúng ta đều biết rằng để điền vào một chuỗi ngày trong trang tính Excel, bạn chỉ cần nhập một vài ngày đầu tiên, sau đó chọn phạm vi và kéo để tự động điền vào lựa chọn với các ngày còn lại. Nhưng nếu bạn chỉ cần điền các ngày trong tuần thì sao?
Bạn có thể làm điều đó dễ dàng với tùy chọn Tự động điền – “chỉ các ngày trong tuần” như hình bên phải. tìm hiểu thêm .
5. Tìm ra ngày trong tuần từ một ngày nhất định
Tìm xem một ngày là cuối tuần hay ngày trong tuần sẽ hữu ích nếu bạn đang lập kế hoạch dự án hoặc bảng phân bổ nguồn lực. Bạn có thể làm điều này bằng cách sử dụng weekday()chức năng. Ví dụ. =weekday("07/28/2021")sẽ trả về 4 (Excel, theo mặc định bắt đầu tuần vào Chủ nhật, do đó thứ Tư được biểu thị là 4).
Nếu bạn muốn bắt đầu một tuần với Thứ Hai như hầu hết chúng ta, hãy sử dụng =weekday("07/28/2021",2).
6. Đánh dấu các ngày cuối tuần bằng cách sử dụng định dạng có điều kiện
Thông thường, khi bạn đang lập kế hoạch hoặc báo cáo dự án, sẽ hữu ích nếu những ngày cuối tuần hoặc sau giờ hành chính có thể bị xám đi. Bạn có thể làm điều này dễ dàng với định dạng có điều kiện như hình dưới đây:
Để làm điều này, chúng tôi có thể kiểm tra xem một ngày nhất định có phải là cuối tuần hay không ở định dạng có điều kiện bằng cách =WEEKDAY(this_date,2)>5như ngày trong tuần () trả về 6 và 7 cho Thứ Bảy và Chủ Nhật.
Bạn có thể sử dụng logic tương tự để đánh dấu sau giờ hành chính (trước 9 giờ sáng hoặc sau 5 giờ chiều) cho các giá trị thời gian. Tìm hiểu 5 mẹo sau để nắm vững định dạng có điều kiện .
7. Thêm / trừ ngày
Vì ngày tháng trong Excel không có gì khác ngoài số, bạn có thể tìm ra sự khác biệt giữa hai ngày đã cho bằng cách trừ một ngày cho một ngày khác. Ví dụ. =DATE(2021,7,31)-DATE(2021,7,1)sẽ trở lại30
Để thêm n số ngày vào một ngày cụ thể, bạn chỉ cần thêm số đó vào ngày cụ thể. Ví dụ. ="07/20/2021"+26sẽ trở lại08/15/2021
8. Đảm bảo ngày hoặc giờ hợp lệ được nhập vào ô
Khi chia sẻ trang tính của bạn với người khác để nhập một số dữ liệu, có thể hữu ích nếu bạn có thể hạn chế họ chỉ nhập các giá trị ngày hợp lệ trong các ô yêu cầu giá trị ngày. Bạn có thể làm điều đó bằng cách sử dụng tính năng xác thực dữ liệu ô trong excel. Chỉ cần chọn ô mà bạn muốn áp dụng xác thực ngày / giờ, đi tới ruy-băng dữ liệu> xác thực và đặt loại là “Ngày” hoặc “Thời gian” và chỉ định tiêu chí.
Ví dụ: bạn có thể chỉ định tiêu chí như tiêu chí ở trên để đảm bảo rằng ngày được nhập là trong năm 2018. Hơn nữa, bằng cách sử dụng tùy chọn thông báo của cài đặt xác thực dữ liệu, bạn thậm chí có thể hiển thị các thông báo như thế này:
9. Chèn ngày hôm nay, giờ hiện tại bằng phím tắt trên bàn phím
Chỉ cần chuyển đến ô mà bạn muốn chèn ngày tháng và nhấn ctrl +;
Để có thời gian hiện tại, hãy sử dụng ctrl + shift +; (do đó ctrl +:)
Btw, nếu bạn định lấy ngày hôm nay hoặc giờ hiện tại bằng cách sử dụng công thức, bạn có thể sử dụng today () và now (). Hãy cũng tìm hiểu 11 phím tắt rất hữu ích trong excel này .
10. Các chức năng Ngày hàng đầu dành cho bạn
Excel có nhiều chức năng Ngày & Giờ. Dưới đây là danh sách một số công cụ quan trọng nhất giúp bạn sử dụng giá trị ngày tháng trong Excel.
Công thức ngày và giờ
Đó là tất cả, với 10 mẹo này, tôi hy vọng tôi đã làm cho buổi hẹn hò của bạn với bảng tính đó trở nên thú vị một chút.
3 công thức ngày quan trọng cho dân tài chính và kế toán
Nếu bạn làm việc trong lĩnh vực tài chính hoặc kế toán, việc sử dụng ngày tháng là một phần quan trọng trong công việc của bạn. Ngoài tất cả các mẹo trên, bạn cũng cần học cách tính toán:
- Quý từ một ngày (cả lịch và tài chính)
- Ngày làm việc đầu tiên của tháng
- Ngày làm việc cuối cùng của tháng
Hãy xem video ngắn này để hiểu cách tính toán những thứ này ( xem nó trên kênh YouTube của tôi )
Các sự cố thường gặp khi làm việc với ngày tháng trong Excel
Khi sử dụng các giá trị hoặc công thức liên quan đến ngày và giờ trong Excel, bạn thường có thể nhận thấy một số vấn đề. Sử dụng danh sách kiểm tra này để khắc phục sự cố.
- Excel hiển thị ##### thay vì giá trị ngày hoặc giờ
Điều này có thể xảy ra nếu ô của bạn quá nhỏ để hiển thị giá trị. Thử điều chỉnh chiều rộng cột.
Điều này cũng có thể xảy ra nếu bạn sử dụng các giá trị không chính xác làm ngày và giờ. Ví dụ: nếu bạn cố gắng định dạng số âm là ngày, bạn sẽ thấy ##### - Excel không thể hiểu ngày tháng của tôi
Khi cố gắng chuyển đổi một ô hoặc giá trị thành ngày tháng, đôi khi Excel không thể hiểu thông tin đầu vào của bạn. Điều này là do Excel dựa vào cài đặt khu vực của bạn để hiểu ngày tháng. Vì vậy, nếu định dạng ngày thông thường của bạn là mm / dd / yyyy, thì Excel sẽ mong đợi các ô (hoặc giá trị) có cùng định dạng để chuyển đổi chúng thành ngày tháng. Nếu bạn có các giá trị dd / mm / yyyy, thì Excel không thể chuyển đổi ngày tháng. Để khắc phục sự cố, hãy đọc ngày trích xuất từ hướng dẫn văn bản
