Bagaimana cara menambahkan minggu ke tanggal dalam lembaran?

Cara mendapatkan nomor minggu dari tanggal

Untuk mendapatkan nomor minggu ISO (1-53) dari tanggal di sel A1, gunakan =ISOWEEKNUM(A1)

Untuk mendapatkan tahun yang sesuai, gunakan =YEAR(A1-WEEKDAY(A1, 2)+4)

Baca selengkapnya tentang =ISOWEEKNUM() dan WEEKDAY() di Pusat Bantuan Google Dokumen

Cara mendapatkan tanggal dari nomor minggu

Untuk mendapatkan tanggal hari Senin dalam seminggu, gunakan =DATE(A1, 1, -3 + 7 * B1 - WEEKDAY(DATE(A1, 1, 4), 2) + 1)

Sel A1 berisi tahun empat digit (mis. g. 2023), dan sel B2 berisi nomor minggu (1-53)

Cara mendapatkan jumlah minggu dalam setahun

Untuk mendapatkan minggu ISO dalam setahun (mis. e. nomor minggu lalu), gunakan =ISOWEEKNUM(DATE(A1, 12, 28))

Sel A1 berisi tahun empat digit (mis. g. 2012)

Weeknum (lihat panduan fungsi saya) adalah fungsi 'hanya' untuk mengidentifikasi minggu kalender. Tapi itu mengembalikan angka minggu, bukan rentang minggu

Yang saya inginkan adalah tanggal mulai minggu hingga tanggal akhir seperti 04-Feb-2019 hingga 10-Feb-2019 (Senin-Minggu), bukan minggu nomor 6

Inilah pentingnya formula minggu kalender saya di Google Sheets

Catatan. Saya telah menggunakan perhitungan angka minggu tipe 2 di mana hari dalam seminggu dimulai pada hari Senin dan berakhir pada hari Minggu

Jika saya menggunakan tipe 1, maka rentang minggu kalender adalah 03-Feb-2019 hingga 09-Feb-2019 (Minggu-Sabtu)

Saya akan memberi Anda info lebih lanjut tentang ini di bagian rumus yang bersangkutan di bawah ini. Di sini saya akan menggunakan fungsi Weekday, bukan Weeknum

Rumus minggu kalender saya di Google Sheets akan mengembalikan tanggal awal minggu dan tanggal akhir dalam bentuk gabungan dari daftar tanggal. Selanjutnya, rumus tersebut memiliki kompatibilitas untuk digunakan dalam rentang tanggal karena rentang tanggal tahun yang berbeda

Lihat output yang dikembalikan oleh rumus minggu kalender saya terlebih dahulu, lalu saya akan memberi tahu Anda di mana kita dapat menggunakannya

Anda dapat dengan mudah menemukan tanggal mulai minggu kalender dan tanggal akhir dari tanggal apa pun menggunakan rumus sederhana. Saya sudah menjelaskannya di sini – Formula untuk Menemukan Tanggal Mulai Minggu dan Tanggal Berakhir di Google Sheets

Yang akan saya gunakan dalam bentuk array untuk mencapai hasil kami. Sebenarnya, saya memiliki dua jenis opsi formula. Keduanya akan saya jelaskan dan berikan kepada Anda. Anda dapat memilih yang cocok untuk Anda

Tujuan Formula Minggu Kalender di Google Sheets

Tujuan penggunaan rumus minggu kalender saya sederhana yaitu untuk menggabungkan tanggal awal minggu dengan tanggal akhir. Tapi tujuan sebenarnya berbeda. Dengan rumus tersebut kita dapat membuat laporan rangkuman mingguan seperti;

• Rata-rata penjualan mingguan selama periode 01/01/2018 hingga 16-02-2019 (data harian ke rata-rata mingguan)
• Jumlah penjualan mingguan yang berlangsung selama beberapa tahun (data harian hingga jumlah mingguan)

Dan akhirnya;

• Hitungan penerimaan barang mingguan (jumlah muatan truk) untuk periode tertentu (data harian hingga hitungan mingguan)

Jika Anda senang mendapatkan ringkasan berdasarkan angka minggu, maka segalanya akan mudah bagi Anda

Kita dapat menggunakan Query untuk ini dan Anda dapat melihat salah satu contohnya di tutorial saya sebelumnya di sini – Cara Membuat Laporan Ringkasan Mingguan di Google Sheets

Anda dapat meringkas rentang tanggal berdasarkan minggu kalender, bukan nomor minggu tetapi tanggal awal dan akhir minggu sebagai judul kolom, menggunakan rumus minggu kalender saya di Google Sheets

Dalam tutorial ini, saya tidak akan masuk ke bagian ringkasan. Di sini Anda dapat mempelajari cara mendapatkan tanggal mulai dan tanggal akhir minggu kalender sebagai tajuk kolom

Rangkuman bijak minggu kalender, yang dapat Anda pelajari di salah satu tutorial saya yang akan datang yang sudah ada di dalam saluran. Saya pasti akan memberi Anda tautannya. Maksud saya, saya akan memperbarui posting ini dengan tautan yang disertakan

Gabungkan Tanggal Awal dan Akhir Minggu Menggunakan Formula Minggu Kalender

Contoh data

Saya memiliki beberapa tanggal acak di kolom A seperti;

Baris A2. A29berisi tanggal dari 01/02/2019 hingga 28/02/2019 dan A30. A58berisi tanggal dari 01/02/2020 hingga 29-02-2020

Saya ingin menunjukkan kepada Anda kompatibilitas multi-tahun dari formula saya. Itu sebabnya saya memasukkan tanggal yang mencakup dua tahun

Selain itu, meskipun tanggalnya tidak disortir, itu tidak akan memengaruhi rumus saya. Jadi Anda aman dengan tanggal dalam urutan apa pun - urutan naik, turun, atau acak

Ada dua jenis formula yang saya tawarkan. Mari kita mulai dengan rumus 1. Ini adalah formula kombinasi jadi saya akan membawa Anda ke langkah demi langkah

Langkah 1

Cara Mengembalikan Tanggal Mulai Minggu dari Rentang Tanggal Harian yang Diberikan

Rumus #1 (berlaku sementara di sel B2)

=ArrayFormula(A2:A58-WEEKDAY(A2:A58,2)+1)

Rumus ini akan menempatkan tanggal mulai minggu di B2. B dari tanggal di semua baris di A2. A

Bungkus rumus di atas dengan fungsi Unik untuk menghapus tanggal awal minggu duplikat

Rumus #2 (modifikasi rumus di sel B2)

=unique(ArrayFormula(A2:A58-WEEKDAY(A2:A58,2)+1))

Sekarang kita harus mengurutkan keluaran rumus ini. Bungkus saja rumus di atas dengan fungsi SORT. Anda dapat menghapus ArrayFormula saat menggunakan SORT

Rumus #3 (dimodifikasi di sel B2)

=sort(unique(A2:A58-WEEKDAY(A2:A58,2)+1))

Inilah hasilnya

Lihat kolom B2. Yang menunjukkan tanggal mulai minggu dimulai hari Senin. Untuk memulai dari hari Minggu, cukup ubah tipe hari kerja 2 pada rumus di atas menjadi 1

Untuk mempelajari lebih lanjut tentang jenis hari kerja, silakan periksa panduan lengkap fungsi tanggal saya untuk Hari Kerja

Langkah 2

Cara Mengembalikan Tanggal Akhir Pekan dari Rentang Tanggal Harian yang Diberikan

Di sini saya tidak akan membawa Anda melalui semua langkah di atas. Rumusnya sama kecuali +6

Formula (berlaku sementara di sel C2)

=sort(unique(A2:A58-WEEKDAY(A2:A58,2)+1+6))

Rumus ini hanya menambahkan 6 hari ke tanggal awal minggu untuk mendapatkan tanggal akhir minggu. Logika sederhana, bukan?

Lihat tanggal akhir pekan adalah hari Minggu. Untuk merubahnya menjadi Saturday, seperti yang sudah saya sebutkan di atas, ubah tipe 2 di Weekday menjadi 1

Output di atas dapat Anda gunakan dalam rumus seperti Sumif, Countifs, Averageifs untuk menjumlahkan, menghitung, rata-rata berdasarkan rentang tanggal di Google Sheets

Langkah 3

Gabungkan Tanggal Awal dan Akhir Minggu untuk Membuat Kolom untuk Minggu Kalender

Anda dapat menggabungkan tanggal mulai minggu dan tanggal akhir dan mengubah urutan ke kolom seperti yang ditunjukkan pada tangkapan layar # 1 di awal. Di sana lihat data di rentang C1. L1

Berikut adalah petunjuk langkah demi langkah. Coba saja pahami rumus-rumus di bawah ini

=ArrayFormula(B2:B11&" - "& C2:C11)

Rumus ini dapat menggabungkan kolom yang berisi tanggal mulai minggu (B2. B) dan tanggal akhir (C2. C) lalu tempatkan tanda hubung di antaranya. Tetapi saya tidak menyarankan Anda untuk menggunakan rumus ini karena hal-hal berikut

1. Ini akan menyebabkan hilangnya format tanggal
2. Ini menggunakan kolom pembantu B dan C

Saya sebelumnya telah memposting tutorial Google Sheets, yang juga menjelaskan cara menggabungkan tanggal dengan teks, atau tanggal dengan tanggal mempertahankan format tanggal

Harus Periksa. Gabungkan Teks dan Tanggal di Google Doc Spreadsheet Menggunakan Formula

Saya akan menggunakan metode itu di sini. Kemudian saya akan menunjukkan cara menghapus kolom pembantu. Perhatikan penggunaan fungsi Teks

=ArrayFormula(text(B2:B11,"DD/MMM/YY")&" - "&text(C2:C11,"DD/MMM/YY"))

Sekarang ganti B2. B11 dengan rumus di B2 dan C2. C11 dengan rumus di C2. Formula itu akan menjadi sebagai berikut

=ArrayFormula(text(sort(unique(A2:A58-WEEKDAY(A2:A58,2)+1)),"DD/MMM/YY")&" - "&text(sort(unique(A2:A58-WEEKDAY(A2:A58,2)+1+6)),"DD/MMM/YY"))

Jika saya sudah memberikan rumus ini di awal, pasti sebagian dari Anda mungkin ada yang terlanjur membaca karena mengira rumus ini cukup rumit bukan?

Terakhir, bungkus rumus dengan Transpose untuk memindahkannya ke kolom. Itu akan menjadi Formula Minggu Kalender tersebut di Google Sheets

=transpose(ARRAYFORMULA(text(sort(unique(A2:A58-WEEKDAY(A2:A58,2)+1)),"DD/MMM/YY")&" - "&text(sort(unique(A2:A58-WEEKDAY(A2:A58,2)+1+6)),"DD/MMM/YY")))

Silakan merujuk ke tangkapan layar pertama untuk keluaran rumus

Pilihan 2

Saya telah berjanji kepada Anda bahwa ada satu formula lagi dengan saya. Anda dapat mengganti rumus yang saya gunakan di sel B2 (langkah 1) dengan;

=sortn(A2:A58-WEEKDAY(A2:A58)+1,9^9,2,1,true)

dan C2 (langkah 2) dengan;

=sortn(A2:A58-WEEKDAY(A2:A58)+1+6,9^9,2,1,true)

Anda dapat menggabungkan rumus ini sesuai petunjuk yang diberikan pada langkah 3. SORTN dalam dua rumus di atas menggantikan Unique dan Sort. Ini hanya untuk informasi Anda

Kesimpulan

Anda dapat segera mengharapkan tutorial di mana saya akan menggunakan rumus minggu kalender di atas di Google Sheets. Ini tentang ringkasan mingguan berdasarkan minggu kalender

Bagaimana cara menambahkan satu minggu ke lembar Google?

Bagaimana cara menambahkan tanggal mingguan secara otomatis di Google Sheets?

Apa rumus untuk tanggal mingguan di Google Sheets?