Konversi rumus hari ke minggu excel

Saya sering mendapati diri saya mendapatkan data dari satu sistem atau lainnya (atau beberapa sistem, dan kemudian menggabungkannya) sebagai data "harian" — serangkaian metrik per hari untuk urutan hari. Kadang-kadang, saya bekerja dengan data itu pada tingkat harian, tetapi, sering kali, saya ingin menggulung data per minggu, per bulan, atau periode waktu lainnya

Misalnya, jika saya ingin melihat data mingguan, saya akan menggunakan hari terakhir dalam seminggu atau hari pertama dalam seminggu dan kemudian menggunakan rumus di kolom baru untuk mengonversi setiap hari sebenarnya menjadi "minggu"

Pada contoh di atas, 15/1/2013 adalah hari Selasa yang jatuh pada pekan yang berakhir pada Sabtu, 19/1/2013. Hal yang sama berlaku untuk Rabu (16/1), Kamis (17/1), Jumat (18/1), dan Sabtu (19/1). Segera setelah 1/20/2013 (Minggu), saya berada di minggu baru - minggu yang berakhir pada 26/1/2013. Masuk akal?

Dengan menambahkan kolom ini, saya dapat membuat tabel pivot yang dapat dengan mudah menghasilkan data mingguan untuk metrik apa pun yang ada di spreadsheet

Pendekatan ini berfungsi untuk sejumlah cara berbeda yang mungkin Anda perlukan untuk mengumpulkan data harian, jadi menurut saya postingan yang membahas beberapa yang lebih umum dan rumus untuk melakukan setiap konversi sudah beres. Saya telah memasukkan semua contoh dalam postingan ini ke dalam spreadsheet yang dapat diunduh yang dapat Anda lihat dan mainkan

Hari dalam seminggu

Fungsi WEEKDAY() mengembalikan angka — 1, 2, 3, 4, 5, 6, atau 7. Tapi, bagaimana jika Anda benar-benar menginginkan hari dalam seminggu dalam bahasa Inggris sederhana?

Anda dapat menggunakan fungsi CHOOSE() dan nilai hard code. Atau, Anda dapat membuat tabel terpisah yang memetakan angka ke setiap hari kerja dan menggunakan VLOOKUP untuk mengisi nilainya. Saya tidak akan membahas salah satu dari pendekatan tersebut...karena pendekatan pilihan saya adalah menggunakan fungsi TEXT()

Untuk hari kerja yang ditulis sepenuhnya (“$A3” adalah sel dengan 15/1/2013 di dalamnya — Anda hanya perlu menyeret rumus ini ke bawah, atau, jika Anda menggunakan Tabel Excel, rumus ini akan terisi otomatis)

=TEXT($A3,”dddd”)

Untuk hari kerja 3 huruf

=TEXT($A3,”ddd”)

Mudah-peasy, bukan?

Catatan. Jika Anda hanya ingin tanggal ditampilkan sebagai hari kerja, Anda tidak memerlukan rumus sama sekali — Anda cukup mengubah pemformatan sel menjadi format khusus "dddd" (untuk hari kerja penuh) atau "ddd" (untuk . Jika Anda melakukannya, tampilan data akan menjadi hari kerja, tetapi nilai yang mendasarinya akan tetap menjadi tanggal sebenarnya. Rumus ini benar-benar menjadikan nilai sebagai hari kerja. Bergantung pada apa kebutuhan Anda, satu pendekatan atau lainnya akan lebih masuk akal

Konversi ke "Minggu Dari"

Contoh saya memulai posting ini adalah mengubah setiap hari menjadi hari yang mengakhiri minggu atau hari yang memulai minggu. Untuk melakukan ini, Anda dapat menggunakan fungsi WEEKDAY(). Cara termudah untuk memahami cara kerjanya adalah dengan menulis (atau meletakkan di Excel) serangkaian tanggal dan kemudian menulis angka 1 sampai 7 saat Anda menurunkan tanggalnya. Semakin jauh Anda memasuki satu minggu, semakin besar nilai WEEKDAY() nya. Jadi, jika Anda mengurangkan nilai WEEKDAY() dari tanggal sebenarnya, Anda akan mendapatkan nilai yang sama selama 7 hari berturut-turut, di mana nilainya akan “melonjak” tujuh hari. Masuk akal (membingungkan… sampai tidak)?

Jadi, untuk mengonversi tanggal menjadi hari Sabtu dalam minggu di mana tanggal tersebut jatuh, gunakan rumus ini (“+7” hanya mempertahankan nilai yang dikonversi agar tidak menjadi hari Sabtu di minggu sebelumnya)

=$A3-HARI KERJA($A3)+7

Itu ide yang sama jika Anda ingin menggunakan hari pertama dalam seminggu, dengan minggu yang ditentukan dimulai pada hari Minggu

=$A3-HARI KERJA($A3)+1

Jelas, Anda dapat menggunakan rumus dasar ini untuk kriteria "minggu" apa pun yang Anda inginkan. Anda hanya perlu memikirkannya dengan sangat keras… atau bereksperimen sampai melakukan apa yang Anda inginkan

Ubah ke Tanggal Dua Mingguan

Terkadang, sebuah perusahaan beroperasi dalam siklus dua mingguan dalam beberapa hal. Misalnya, banyak perusahaan membayar karyawannya setiap dua minggu. WEEKDAY() tidak berfungsi untuk ini, karena tidak memberi tahu Anda yang mana dari dua minggu sehari

Dalam hal ini, saya menggunakan MOD(). Fungsi ini, pada dasarnya, adalah fungsi "sisa", dan penggunaan utamanya adalah untuk menghitung sisa saat satu angka dibagi dengan angka lainnya (misalnya, "=MOD(14,4)" mengembalikan "2" karena, saat Anda membagi

Nah, tanggal Excel, di balik layar, hanyalah angka. Anda tidak benar-benar perlu mengetahui dengan tepat angka tanggal itu (walaupun Anda dapat mengubah pemformatan sel menjadi “Angka” saat tanggal ditampilkan dan Anda akan melihat nomornya). Tapi, jika Anda memikirkannya, jika Anda membagi tanggal dengan 14, itu akan memiliki sisa antara 1 dan 13. Katakanlah sisanya adalah “2. ” Jadi, berapa sisanya jika Anda membagi hari berikutnya dengan 14? . Begitu seterusnya sampai menjadi 13, dimana pada titik tersebut keesokan harinya jika dibagi 14 akan bersisa 0. hmmm. Sepertinya kami telah membuat ulang fungsi WEEKDAY() yang digunakan di atas… tetapi dengan jangka waktu 14 hari, bukan 7 hari, bukan?

Jadi, jika kita ingin mengonversi tanggal menjadi hari Sabtu di akhir periode dua minggu, itu akan menjadi salah satu dari dua rumus ini (bergantung pada hari Sabtu mana yang merupakan batas waktu dan mana yang merupakan titik pertengahan periode)

=$A3-MOD($A3-1,14)+13

atau

=$A3-MOD($A3-8,14)+13

Jika Anda ingin menggunakan awal periode, dengan minggu dimulai pada hari Minggu, maka itu akan menjadi salah satu dari dua rumus ini

=$A3-MOD($A3-1,14)

atau

=$A3-MOD($A3-8,14)

Sekali lagi, diperlukan beberapa eksperimen jika Anda ingin menyesuaikan dengan tanggal lain, tetapi "14" tidak akan berubah selama Anda mengerjakan periode dua mingguan

Konversi ke Tanggal Dua Bulanan

Kadang-kadang (sekali lagi, periode pembayaran perusahaan adalah contoh yang baik), daripada menggunakan kalender dwi-mingguan, Anda ingin menggunakan kalender dwi-bulanan — setiap tanggal dari tanggal 1 sampai tanggal 14 harus diubah menjadi hari pertama bulan itu . Untuk melakukannya, kami menggunakan fungsi DATE() dengan pernyataan IF() untuk nilai hari

=TANGGAL(TAHUN($A3),BULAN($A3),JIKA(HARI($A3)<15,1,15))

Kita tahu tahun adalah YEAR() dari tanggal yang sedang dikonversi, dan kita tahu bulan adalah MONTH() dari tanggal yang sedang dikonversi. Tapi, kita perlu melihat hari dalam sebulan dan memeriksa apakah kurang dari 15. Jika ya, maka kami mengembalikan nilai hari "1", dan sebaliknya, kami mengembalikan nilai hari "15. ”

Konversi ke Tanggal Bulanan

Bulanan hampir sama umum, jika tidak lebih dari mingguan. Untuk mengonversi ke hari pertama setiap bulan adalah penggunaan langsung dari fungsi DATE(). Kami menarik tahun menggunakan fungsi YEAR() pada tanggal yang kami konversi, bulan menggunakan fungsi MONTH() pada tanggal yang kami konversi, dan kemudian cukup mengkodekan "hari" sebagai "1. ”

=TANGGAL(TAHUN($A3),BULAN($A3),1)

Namun, bagaimana jika kita ingin menggunakan hari terakhir di bulan tersebut? . Terkutuklah kamu, kalender Gregorian

Sebenarnya, ini juga tidak terlalu rumit. Mengapa? . Hah? . Begitulah cara kami mendapatkan hari terakhir bulan ini. kita menggunakan fungsi DATE() untuk mengetahui hari pertama bulan berikutnya (dengan menambahkan 1 ke nilai MONTH())… lalu kurangi 1

=TANGGAL(TAHUN($A3),BULAN($A3)+1,1)-1

Bagaimana Anda menyukai mereka apel?. [ref. idiom. ]

Menariknya, bisa dimaklumi jika formula ini pecah untuk bulan Desember. Dalam hal ini, Anda benar-benar memberi tahu Excel untuk menghitung tanggal di mana bulannya adalah “13. ” Untungnya, Excel mengetahui apa yang Anda maksud dan akhirnya mengembalikan 1 Januari tahun berikutnya (dari mana rumus kemudian mengurangi satu untuk mengembalikan 31 Desember)

Tapi, Bagaimana Dengan…

Saya baru saja menggores permukaan dengan kemungkinan konversi tanggal di pos ini. Mudah-mudahan, berbagai pendekatan yang saya jelaskan akan memicu satu atau dua ide untuk situasi spesifik Anda. Tapi, jika Anda punya satu yang membuat Anda bingung, tinggalkan komentar di sini dan saya akan mencobanya

Dan, semua contoh di sini disertakan dalam spreadsheet yang dapat diunduh ini. Anda dapat mengubah "tanggal mulai" di sel A3 dan semua tanggal berikutnya akan diperbarui secara otomatis. Konversi tanggal bahagia

Bisakah Anda mengubah tanggal menjadi minggu di Excel?

Bagaimana cara mengonversi data harian menjadi mingguan di Excel?

Manakah rumus yang benar untuk mengubah minggu menjadi hari?