Excel ngày trong quý

Trong Excel, không có chức năng trực tiếp để lấy một phần tư từ một ngày. Nhưng bạn có thể tạo một công thức cho điều này

Có, bạn có thể sử dụng công thức để tính một phần tư từ một ngày. Thậm chí có nhiều hơn một phương pháp mà bạn có thể sử dụng. Được rồi, như bạn biết, có tổng cộng bốn phần tư trong một năm nhưng vấn đề là bạn có thể nhận được một phần tư từ một ngày theo nhiều cách khác nhau

Đầu tiên là, giả sử một năm trên cơ sở lịch thông thường hoặc năm thứ hai trên cơ sở lịch tài chính. Và trong bài đăng hôm nay, tôi sẽ chỉ cho bạn các phương pháp lấy số quý bằng cách sử dụng cả hai lịch và tôi cũng sẽ chia sẻ với bạn nhiều hơn một phương pháp cho việc này

Vậy hãy bắt đầu

1. Nhận Quý bằng cách sử dụng Hàm ROUNDUP và MONTH

Sử dụng kết hợp ROUNDUP và MONTH là cách tốt nhất để tìm phần tư của một ngày. Nó trả về một phần tư dưới dạng một số (như 1,2,3,4)

Đây là công thức

=ROUNDUP(MONTH(A1)/3,0)

Ở đây chúng tôi đang sử dụng ngày 26 tháng 5 năm 2018 làm ngày và công thức trả về 2 trong kết quả. Bạn có thể sử dụng công thức dưới đây nếu bạn muốn thêm Q trước số phần tư

=“Q-”&ROUNDUP(MONTH(A1)/3,0)

Cách thức hoạt động của công thức này

Công thức này hoạt động trong ba phần

Trong phần đầu tiên, chúng tôi đã sử dụng hàm MONTH và tham chiếu đến ngày trả về số tháng

Sau đó, trong phần thứ hai, chúng tôi chia số tháng đó cho 3, cho chúng tôi một số thập phân

Cuối cùng, trong phần thứ ba, chúng tôi đã sử dụng hàm ROUNDUP để làm tròn (số có số thập phân) cho chúng tôi phần tư thực tế

Đây là điều thực sự. Bạn biết có 4 quý và mười hai tháng. Đúng?

Và khi bạn làm tròn chúng, bạn sẽ luôn nhận được số quý trong tháng

2. Nhận quý bằng cách sử dụng Lịch tài chính

Trong ví dụ trên, chúng tôi đã sử dụng một công thức đơn giản để có một quý kể từ một ngày, nhưng đôi khi bạn cần sử dụng lịch tài chính

Năm tài chính là gì?

Năm tài chính (FY) là khoảng thời gian 12 tháng mà một công ty hoặc chính phủ sử dụng cho mục đích kế toán và lập báo cáo tài chính. Năm tài chính có thể không giống năm dương lịch…

…tìm hiểu thêm

Và để tính quý theo lịch tài chính, chúng ta cần sử dụng một công thức khác và trong công thức này, chúng ta cần kết hợp các hàm CHOOSE và MONTH

Đây là công thức

=CHOOSE(MONTH(A1),4,4,4,1,1,1,2,2,2,3,3,3)

(Ở đây tôi đã sử dụng năm tài chính của Ấn Độ bắt đầu vào ngày 1 tháng 4 và kết thúc vào ngày 31 tháng 3)

Nó trả về 1 trong kết quả là tháng 5 và năm của chúng ta bắt đầu từ tháng 4 và tháng 5 là tháng thứ hai trong năm nằm dưới quý đầu tiên

Cách thức hoạt động của công thức này

Ở đây chúng tôi đã kết hợp hai chức năng khác nhau và nó hoạt động theo hai phần

Trong phần đầu tiên, chúng ta đã sử dụng hàm MONTH để lấy số tháng từ ngày và ở đây nó trả về 5

Sau đó, trong phần 2, chúng ta đã sử dụng đến cách chọn hàm lấy số theo số tháng do hàm MONTH trả về

Bạn có thể tìm hiểu thêm về CHỌN từ đây

Nếu bạn để ý, trong hàm CHOOSE chúng ta đã chỉ định 12 số khác nhau (4,4,4,1,1,1,2,2,2,3,3,3) nên khi MONTH trả về một số thì CHOOSE trả về một số từ hàm này

Đây là thỏa thuận

Đối với ba số đầu tiên, chúng tôi có 4, nghĩa là nếu nó trả về 1,2 hoặc 3 (tháng 1-tháng 3), chúng tôi sẽ nhận được 4 và trong ba tháng tiếp theo, chúng tôi sẽ nhận được 1 (Đó là vì năm tài chính của chúng tôi bắt đầu từ tháng 4

3. Chuyển đổi Ngày thành Quý bằng Mã VBA

Ở đây chúng tôi đang sử dụng mã VBA để chuyển đổi một ngày thành số quý của nó một cách đơn giản. Đây là mã

Sub convert2Quarter()

Dim Rng As Range

For Each Rng In Selection
If IsDate(Rng.Value) = True Then
Rng.Value = DatePart(“q”, Rng.Value)
Rng.NumberFormat = “General”
End If
Next Rng

End Sub

Để sử dụng mã này, tất cả những gì bạn cần làm là thêm nó vào trình soạn thảo VB của bạn

Và sau đó chỉ cần chọn các ô mà bạn cần chuyển đổi ngày thành số phần tư và cuối cùng chạy mã này

Đây là cách mã này hoạt động…

Khi bạn chạy mã này, trước tiên nó sẽ kiểm tra xem ô có ngày hay không và nếu đó là ngày thì nó sẽ chuyển đổi nó thành số quý

Chúng tôi đã sử dụng hai chức năng VBA khác nhau ở đây

1. ISDATE. Để kiểm tra xem ô có ngày không
2. NGÀY. Để chuyển đổi một ngày thành một phần tư

Kiểm tra cái này. Top 100 mã macro hữu ích cho người mới sử dụng VBA. VBA trong excel là gì

4. Nhận ngày bắt đầu quý từ một ngày

Hãy quên số quý và sử dụng ngày bắt đầu của quý

Điều đó có nghĩa là nếu chúng ta có ngày 26-May-2018 thì thay vì có quý số 2, chúng ta có thể có 01-Apr-2018 là ngày bắt đầu của tháng đầu tiên của quý. Đây là công thức mà chúng ta cần sử dụng

=DATE(YEAR(A1),FLOOR.MATH(MONTH (A1)-1,3)+1,1)

Khi bạn nhập công thức này, nó sẽ trả về ngày bắt đầu của quý

Công thức này hoạt động như thế nào

Công thức này bao gồm bốn phần khác nhau cung cấp cho bạn ngày bắt đầu của một quý

Trong phần đầu tiên, chúng ta đã sử dụng hàm MONTH trả về số tháng cho ngày

Vì vậy, khi MONTH trả về 5 dưới dạng số tháng (sau đó trừ đi 1 từ số đó), trong phần thứ hai, FLOOR làm tròn số đó thành 3 và sau đó thêm 1 vào số đó để chúng ta có thể nhận được 4 là tháng đầu tiên (tháng 4) của cùng quý

Sau đó, trong phần thứ ba, chúng tôi đã sử dụng hàm NĂM để lấy năm kể từ ngày thực tế

Và trong phần thứ tư, chúng ta chỉ đơn giản là kết hợp hàm DATE để tạo ngày kết hợp các giá trị đến từ hai phần đầu tiên

5. Nhận ngày kết thúc quý từ một ngày

OK, vì vậy bạn cần ngày kết thúc của một quý thay vì ngày bắt đầu hoặc số và đối với điều này, công thức mà chúng tôi có thể sử dụng là

=DATE(YEAR(A2),((INT((MONTH(A2)-1)/3)+1)*3)+1,1)-1

Đối với 26-May-18, nó trả về vào 30-Jun-18, là ngày cuối cùng của quý

Công thức này hoạt động như thế nào

Trong công thức này, chúng ta có bốn phần khác nhau

Ở phần đầu, chúng ta đã kết hợp hàm INT và MONTH. MONTH trả về số tháng của ngày và sau đó chúng tôi đã chia nó cho 3. Sau đó INT làm tròn xuống số nguyên gần nhất

Cuối cùng, chúng tôi nhân nó với 3 để có được 6 (tháng 6) là tháng cuối cùng của quý và sau đó thêm 1 vào đó

Nói một cách đơn giản, phần này của công thức chỉ đơn giản là chuyển đổi tháng của ngày thực tế thành tháng đầu tiên của quý tiếp theo

Trong phần thứ hai, chúng tôi đã sử dụng hàm NĂM để lấy năm từ ngày thực tế

Sau đó, trong phần thứ ba, chỉ cần sử dụng 1 ngày đầy đủ với chức năng DATE

Xin lỗi. một điều nữa

Trong phần thứ tư, trừ 1 trong toàn bộ công thức, để chúng ta có ngày cuối cùng của quý thay vì ngày đầu tiên của quý tiếp theo

Tệp mẫu

➜ tải xuống tệp mẫu này

Phần kết luận

Chỉ bằng cách kết hợp các hàm, bạn có thể tạo một công thức đơn giản để tính một phần tư từ một ngày. Và trong bài đăng này, chúng tôi đã khám phá bốn công thức + mã VBA cho điều này

Thẳng thắn mà nói, #1 là cách được sử dụng nhiều nhất và đơn giản nhất để tìm một phần tư ngày nhưng bạn có thể sử dụng phần còn lại nếu muốn. Tôi hy vọng bạn thấy bài đăng này hữu ích, nhưng bây giờ, hãy cho tôi biết điều này

Bạn có biết bất kỳ phương pháp nào khác để tính số phần tư từ một ngày không?

Chia sẻ với tôi trong phần bình luận, tôi rất muốn nghe ý kiến ​​từ bạn và đừng quên chia sẻ mẹo này với bạn bè của bạn. Tôi chắc chắn rằng họ sẽ đánh giá cao nó