Bagaimana cara mengubah seperempat menjadi sebulan di excel?

Jika Anda bekerja di bidang keuangan dan sebagian besar bidang lainnya, mungkin sering terjadi bahwa Anda perlu mengetahui apa yang termasuk dalam kalender kuartal tanggal. Sayangnya tidak ada fungsi Excel yang tersedia untuk mengembalikan informasi ini berdasarkan tanggal tertentu. Dengan menggunakan rumus ini, kami dapat mengonversi tanggal apa pun menjadi kuartal kalender

Kuartal untuk setiap tanggal di kolom A ditunjukkan di kolom B

Contoh 2. Mengkonversi Tanggal ke Kuartal dan Tahun

Kita dapat menggunakan rumus berikut untuk mengonversi tanggal menjadi seperempat dan tahun

="Q" &INT((MONTH(A2)+2)/3) & "-" & YEAR(A2)

Kita dapat mengetikkan rumus ini ke dalam sel B2 dan menyeret rumus tersebut ke bawah ke setiap sel yang tersisa di kolom B

Kuartal dan tahun untuk setiap tanggal di kolom A ditunjukkan di kolom B

Contoh 3. Ubah Tanggal menjadi Nama dan Tahun Seperempat Penuh

Kita dapat menggunakan rumus berikut untuk mengonversi tanggal menjadi nama dan tahun kuartal penuh

="Quarter " &INT((MONTH(A2)+2)/3) & " " & YEAR(A2)

Kita dapat mengetikkan rumus ini ke dalam sel B2 dan menyeret rumus tersebut ke bawah ke setiap sel yang tersisa di kolom B

Kuartal dan tahun untuk setiap tanggal di kolom A ditunjukkan di kolom B

Sumber daya tambahan

Tutorial berikut menjelaskan cara melakukan tugas umum lainnya di Excel

Cara Membandingkan Tanggal Tanpa Waktu di Excel
Cara Menggunakan SUMIFS dengan Rentang Tanggal di Excel
Cara Menyaring Tanggal menurut Bulan di Excel

Excel memiliki fungsi bawaan untuk mengembalikan tahun, nomor bulan, nama bulan, nomor minggu, hari kerja, atau hari dari sel mana pun yang berisi tanggal. Tapi tidak ada cara bawaan untuk mengembalikan angka seperempat. Kuartal lebih sulit karena definisi kuartal bervariasi tergantung pada tanggal akhir tahun fiskal

UNTUK TAHUN KALENDER

Jika tahun keuangan Anda berakhir pada tanggal 31 Desember, maka Anda dapat menggunakan rumus singkat. (Gambar 1 memperlihatkan fungsi Excel yang dapat Anda gunakan untuk mengonversi tanggal menjadi tahun, bulan, minggu, atau hari kerja. ) Pertama, gunakan fungsi =MONTH() untuk mengubah tanggal menjadi angka bulan. Bagi hasilnya dengan 3. Tanggal di bulan Januari akan mengembalikan 0. 33, 0 Februari. 67, 1 Maret. 00, 1 April. 33, dan seterusnya. Dengan menggunakan fungsi =ROUNDUP(), Anda akan memiliki 1 untuk Januari, Februari, dan Maret;

Untuk menampilkan kuartal sebagai “Q1” atau “Qtr 1”, gabungkan teks yang sesuai sebelum hasilnya. Pada Gambar 2, sel A7 menggunakan rumus =“Q”&ROUNDUP(MONTH(A2)/3,0)&“-”&YEAR(A2) to return “Q1-2017.”

UNTUK TAHUN BUKU LAINNYA

Tahun fiskal yang berakhir 31 Januari adalah hal biasa di perusahaan ritel. Kontraktor pemerintah mungkin menggunakan akhir tahun fiskal pada tanggal 30 September. Untuk menghitung kuartal fiskal, pertama-tama ubah tanggal menjadi angka bulan menggunakan =MONTH(). Hasil ini terbatas pada 12 bilangan bulat dari 1 sampai 12

Anda dapat menggunakan rumus VLOOKUP untuk mengonversi angka bulan menjadi angka kuartal. Tapi rumusnya =VLOOKUP(MONTH(A2),{1,4;2,1;5,2;8,3;11,4},2) is difficult to remember and type. The array constant inside the curly braces holds a 5-row by 2-column lookup table.

Alih-alih, fungsi =CHOOSE() akan memungkinkan Anda menentukan 12 angka kuartal yang sesuai dengan bulan 1 hingga 12. Jika tahun fiskal Anda berakhir pada bulan Januari, Anda akan menggunakan =CHOOSE(MONTH(A2),4,1,1,1,2,2,2,3,3,3,4, . Untuk tahun fiskal yang berakhir pada bulan Maret, gunakan . For a fiscal year ending in March, use =CHOOSE(MONTH(A2),4,4,4,1,1,1,2,2,2,3,3,3) . .

Jika Anda lebih suka menampilkan kuartal sebagai Q1 daripada 1, Anda dapat menggunakan ampersand untuk menggabungkan "Q" sebelum fungsi CHOOSE. Hasilnya ditampilkan di sel A13 dan A20 dari Gambar 2, dengan rumus yang ditampilkan di D13 dan D20

MENAMBAHKAN TAHUN FISKAL KE TRIWULAN

Di beberapa perusahaan, tahun fiskal ditentukan oleh tahun kalender di mana bulan ke-12 tahun itu terjadi. Misalnya, tahun fiskal yang berlangsung dari Februari 2017 hingga Januari 2018 akan memiliki kuartal yang disebut Q1-2018 hingga Q4-2018. Di perusahaan lain, tahun fiskal mungkin disebut sebagai sesuatu seperti “17-18. ”

Untuk kasus di mana tahun fiskal adalah tahun akhir bulan, gunakan fungsi IF() untuk menguji apakah nomor bulan kurang dari bulan awal. Misalnya, dengan tahun fiskal yang berakhir pada bulan Januari, tahun fiskal akan menjadi =YEAR(A2) jika tanggalnya sebelum Februari dan =YEAR(A2)+1 in February and later. Thus, to include the fiscal year with the quarter, you could use =IF(MONTH(A2).

Untuk satu tahun yang berakhir pada bulan Maret, Anda akan mengubah tes <2 menjadi <4. =JIKA(BULAN(A2)<4,0,1)+TAHUN(A2). Selama satu tahun yang berakhir pada bulan September, Anda akan menguji <10. =JIKA(BULAN(A2)<10,0,1)+TAHUN(A2). =IF(MONTH(A2)<4,0,1)+YEAR(A2). For a year ending in September, you would test for <10: =IF(MONTH(A2)<10,0,1)+YEAR(A2).

Sel A21 pada Gambar 2 menunjukkan cara menggabungkan kuartal dan tahun fiskal menggunakan rumus panjang ini. =“Q”&PILIH(BULAN(A17),4,1,1,1,2,2,2,3,3,3,4,4)&“-”&JIKA( . Itu akan mengembalikan kuartal dalam format “Q4-2017. ”. That would return the quarter in a format of “Q4-2017.”

Jika, sebagai gantinya, Anda membutuhkannya untuk mengatakan 17-18, rumus panjang menjadi lebih panjang. Hanya untuk mendapatkan bagian tahun dari rumus, Anda akan menggunakan. =JIKA(BULAN(A2).

KEMBALI PERIODE BUKAN SEBULAN

Baru-baru ini saya mengadakan seminar untuk IMA® di Nashville. Karena pemerintah negara bagian Tennessee memiliki tahun fiskal yang berakhir pada bulan Juni, mereka lebih suka melaporkan menggunakan nomor periode, bukan bulan. Juli adalah periode 1, Desember adalah periode 6, Januari adalah periode 7, dan seterusnya. Dalam hal ini, banyak rekan kerja menggunakan tabel VLOOKUP di sisi kanan data. Tetapi mereka juga dapat menggunakan =CHOOSE(MONTH(A2),7,8,9,10,11,12,1,2,3,4,5,6) to achieve the same result.

TANGGAL TIDAK BISA TEKS

Peringatan untuk semua rumus ini adalah Anda menyimpan tanggal Anda sebagai tanggal sebenarnya dan bukan sebagai teks. Cara cepat untuk memeriksa. Tekan Ctrl dan tombol Grave Accent (`) untuk beralih ke mode Show Formulas. Tanggal asli akan diubah menjadi nomor seri seperti 42783. Jika Anda masih melihat tanggal teks, rumus tidak akan berfungsi. Tekan Ctrl+` lagi untuk keluar dari Show Formulas

Saya baru-baru ini menemukan lembar kerja di mana manajer ingin tanggal ditampilkan sebagai 1 Februari 2017, 2 Februari 2017, 3 Februari 2017, 4 Februari 2017. Sufiks setelah tanggal ("st", "nd", "rd", dan "th") mengubah bilangan kardinal menjadi bilangan urut. Sementara Pegangan Isi Excel akan memungkinkan Anda membuat kolom angka kardinal dengan mudah, rumus dalam artikel ini menjadi jauh lebih kompleks. Rumus 41 karakter berkembang menjadi 2.438 karakter untuk menangani tanggal teks (lihat Gambar 3)

SF KATAKAN

Banyak rumus yang mereferensikan tanggal akan mengubah format sel menjadi tanggal. Berencana memformat sel formula bulan ini sebagai numerik

Bagaimana Anda mengubah kuartal menjadi bulan di Excel?

Bagaimana Anda mengekstrak kuartal dalam sebulan?

Bagaimana cara mengubah seperempat di Excel?

Bagaimana cara memfilter tanggal dari seperempat di Excel?