Pertanyaan lain dari seminar Atlanta Power Excel saya:
Bagaimana Anda dapat membulatkan waktu yang dapat ditagih hingga seperempat jam berikutnya?
Katakanlah Anda menyimpan catatan waktu yang dapat ditagih. Anda memiliki waktu mulai dan waktu akhir. Rumus seperti itu =E2-D2akan menghitung waktu yang telah berlalu.
Hasil di F2 adalah 55 menit. Tetapi format default menunjukkan 12:55. Pilih seluruh kolom. Tekan Ctrl + 1 untuk Memformat Sel. Pilih kategori Waktu. Manakah dari berikut ini yang harus Anda gunakan? Untuk saat ini, pilih 13:30:55, tapi nanti Anda akan melihat masalah dengan pilihan ini.
Kebijakan perusahaan Anda adalah membulatkan semua tagihan hingga seperempat jam berikutnya. Lakukan perhitungan ini: Ada 24 jam dalam sehari. Ada 4 jam seperempat dalam satu jam. Kalikan 24 * 4 untuk mendapatkan 96. Itu berarti seperempat jam sama dengan 1/96 hari.
Gunakan rumus =CEILING(F2,1/96)untuk membulatkan waktu yang telah berlalu di F hingga seperempat jam berikutnya. Dalam metode Excel untuk menyimpan waktu, Siang adalah 0,5. 6AM adalah 0,25. 12:15 adalah 1/96 atau 0,010417. Lebih mudah mengingat 1/96 dari 0,010417.
Hasilnya ditunjukkan di bawah ini. Perhatikan bagaimana 35 menit dibulatkan menjadi 45 menit. Ini sepertinya rumus yang digunakan pengacaraku… haha, itu hanya lelucon Esquire Dewey! Jangan menuntut saya! Saya tahu mereka menggunakan =CEILING(F2,4)…
Jeda di sini… artikel ini membulatkan ke seperempat jam berikutnya karena itulah metode yang digunakan Larry dalam seminar saya. Ada kemungkinan besar bahwa perusahaan Anda melakukan sesuatu yang lain. Gambar di bawah menunjukkan cara membulatkan menjadi lima menit atau 1 menit. Anda dapat menggunakan logika serupa untuk membulatkan ke 6 menit (1/240) atau 12 menit (1/120) terdekat.
Juga - saran lembut yang menunjukkan perbedaan antara FLOOR, MROUND, dan CEILING. Fungsi FLOOR di B9: B22 selalu membulatkan ke bawah. MROUND di C9: C22 membulatkan ke terdekat. CEILING di D9: D22 dibulatkan.
Catatan
Tooltip Excel akan menyarankan agar Anda menggunakan FLOOR.MATH atau CEILING.MATH, bukan FLOOR atau CEILING. Versi baru ".Math" dari fungsi ini menangani bilangan negatif secara berbeda. Karena waktu di Excel tidak akan pernah negatif, tidak masalah menggunakan LANTAI atau PLAFON biasa.
Ubah pecahan dari 1/96 menjadi 1/288 menjadi pembulatan menjadi kenaikan lima menit.
Mengubah Waktu ke Jam
Saran lain dari Larry: Ubah waktu menjadi jam sebenarnya. Dengan begitu, saat Anda mengalikan tarif tagihan per jam, perhitungannya akan berhasil. Gunakan waktu yang telah dibulatkan dan kalikan dengan 24 untuk mendapatkan angka desimal dari jam. Pastikan untuk memformat hasil sebagai angka dengan dua tempat desimal dan bukan waktu.
Format Waktu Mana yang Digunakan?
Saatnya berbicara tentang pilihan pemformatan waktu tersebut: Anda dapat memilih 13:30, 13:30:55, atau 37:30:55. Pada tangkapan layar di atas, saya telah menggunakan 13:30:55. Masalahnya dengan ini muncul di akhir minggu. Saat Anda menjumlahkan jam desimal di kolom H, Anda memiliki 53 jam yang dapat ditagih. Saat Anda menjumlahkan waktu dalam G, Anda mendapatkan lima jam. Apa yang sedang terjadi?
Keduanya benar, jika Anda memahami format angka yang Anda pilih. Katakanlah Anda masuk ke =NOW()dalam sel. Anda dapat memformat sel untuk menunjukkan tanggal & waktu, hanya tanggal, atau hanya waktu. Excel tidak memiliki masalah untuk menghentikan bagian 29 Mei dari 29 Mei 08:15:34 jika Anda menyuruhnya melakukan itu. Jawaban di sel G57 adalah "2 hari lima jam". Tetapi ketika Anda memilih 13:37:55 sebagai format waktu, Anda mengatakan pada Excel untuk menghilangkan jumlah hari dan hanya menampilkan jam, menit, dan detik.
Jika Anda mengonversi kolom G untuk menggunakan format 37:30:55, maka Anda mendapatkan jumlah jam yang benar:
Keterangan di atas gambar tersebut menangkap masalah kecil dengan format 37:30:55: ini menunjukkan detik, yang tampaknya tidak perlu karena kita membulatkan ke lima belas menit terdekat. Setelah memilih 37:30:55, klik pada kategori Custom. Anda dapat melihat kode format angka (h):mm:ss;@yang digunakan. Tanda kurung siku di sekitar H adalah bagian yang penting.
Untuk menunjukkan waktu Anda dalam jam dan menit, edit format angka untuk menghapus: ss.
Menonton video
Video hari ini berisi tip bonus di bagian akhir untuk memformat desimal sebagai pecahan.
Transkrip Video
Pelajari Excel Dari Podcast, Episode 2210: Round Up to the Next Quarter Hour.
Hai, selamat datang kembali di netcast, saya Bill Jelen. Hari ini, pertanyaan lain dari seminar Atlanta Power Excel saya. Seseorang sedang membuat spreadsheet penagihan, dan mereka ingin membulatkannya ke seperempat jam berikutnya, bukan? Jadi, untuk setiap tugas di sini, mereka memiliki Klien, Waktu Mulai, dan Waktu Berakhir, dan untuk menghitung Waktu yang Berlalu-- itu sederhana, jika itu waktu nyata, atau jika hanya = E2-D2. Jadi, itu berarti 55 menit, tapi itu mengatakannya dengan cara yang sangat aneh-- "12:55." Kami ingin melanjutkan dan memformat ini. Pilih Waktu, dan lihat, ada beberapa pilihan berbeda di sini: 13:30; 13:30:55, yang menunjukkan detik-detik; dan kemudian 37:30:55. Sekarang, untuk saat ini, saya akan memilih pukul 13:30, tapi kita akan memutar kembali nanti dan memeriksa kembali pilihan itu. Jadi, saya memilih OK dan klik dua kali untuk menyalinnya,dan Anda melihat bahwa itu hanya menghitung - jadi di sini, 25 menit. Tetapi, orang yang mengajukan pertanyaan ini - perusahaan tempat mereka bekerja - ingin mereka kumpulkan hingga seperempat jam berikutnya.
Baiklah, jadi, seperempat jam. Mari pikirkan tentang waktu di Excel. Siang adalah 0,5. Jika saya memformatnya sebagai waktu-- 12:00:00. Dan jika saya melakukan 0,25, itu akan menjadi 6:00:00. Baiklah, cara kerjanya adalah satu hari penuh adalah angka 1.0, jadi, jika kita ingin menghitung 15 menit, itu akan menjadi-- ada 24 jam x 4 seperempat jam, jadi kita harus meletakkan = 1/96 di sana, dan itu memberi kita waktu 15 menit. Sekarang, 1/96 - Saya bisa melakukan matematika itu di kepala saya: 24x4; 1/96 - tentu saja lebih mudah mengingat 1/96 daripada mengingat desimal ini: 0,01041666667. Baiklah, jadi lebih mudah menggunakan 1/96 di sana, jadi kita akan menggunakan fungsi yang disebut CEILING.
PLAFON. Lihat, kita punya pilihan antara CEILING dan CEILING.MATH untuk bilangan positif yang identik. Ini lebih pendek untuk mengetik PLAFON, jadi saya akan menggunakan PLAFON. Dan, dibulatkan, ada 1/96 hari-- yaitu 15 menit-- dan itu jawaban yang benar, dalam format yang salah. Kita akan menyalin format ini ke-- Ctrl + C, lalu Alt + E + S + T untuk menempelkan format khusus-- dan 55 menit membulatkan hingga satu jam berikutnya. Klik dua kali untuk menyalinnya, dan lihat, 35 menit membulatkan ke 45 berikutnya; 25 putaran hingga 30 berikutnya. Tampaknya berhasil, meskipun orang yang menanyakan pertanyaan ini-- mereka membulatkan ke 15 berikutnya. Mungkin bahkan perusahaan Anda menggunakan sesuatu yang berbeda.
Baiklah, jadi untuk membulatkan ke seperempat jam terdekat, kita gunakan 1/96. Tetapi untuk membulatkan ke 5 menit terdekat-- ada 24 jam, ada 12 periode 5 menit dalam setiap jam, jadi 12x4 = 288. Gunakan 1/288 untuk membulatkan ke 5 menit terdekat. Jika Anda membulatkan ke 6 menit terdekat-- ada 10 periode 6 menit x 24 jam; Anda membulatkan ke 1/240 terdekat. Untuk membulatkan ke menit terdekat, pilih ke 1/1440 terdekat.
Baiklah, jadi Anda bisa melakukan ini, dan kami juga punya pilihan. PLAFON selalu akan membulatkan, jadi 16 menit-- mari kita pergi ke 1/96 di sana-- 16 menit akan menjadi 30, dan, sementara saya yakin pengacara saya berpikir itu cara yang bagus untuk menagih, itu tidak adil bagi saya-- klien. Baiklah, jadi Anda tahu PLAFON akan dibulatkan. Pengacara saya tidak akan pernah menggunakan ini: LANTAI ini akan selalu membulat. Tapi, mungkin hal yang paling adil di sini adalah menggunakan MROUND. Jadi, = MROUND dari Waktu ke 1/96 terdekat, dan itu akan membulatkan semuanya menjadi sekitar 22,5 menit; dan kemudian, setelah itu, akan dibulatkan. Baiklah, jadi itu membulatkan ke apapun yang paling dekat dengannya. Jadi, Anda harus memilih apakah Anda akan menggunakan FLOOR, MROUND, atau CEILING, lalu pembagi Anda akan menjadi apa-- apakah itu 's akan menjadi 5 menit terdekat, menit terdekat, atau, Anda tahu, apapun. Baiklah, jadi di sana kami mengumpulkan.
Nah, tip bonus di sini-- ini adalah Larry dalam seminar itu-- Larry berkata, "Hei, lihat, tidak terlalu baik untuk menunjukkan waktu yang dapat ditagih di saat-saat seperti itu; kami benar-benar ingin mengubah jam karena Anda benar-benar pergi untuk menagih dengan tarif tertentu-- Anda tahu, $ 35 / jam, $ 100 / jam, $ 500 / jam, berapa pun tarif Anda-- dan kami harus dapat mengalikan jam x tarifnya. Jadi, alangkah baiknya dapat mengubah jam tersebut menjadi waktu desimal. Jadi, kami mengambil waktu itu dalam G2 dan mengalikannya dengan 24, dan itu akan memberi kami angka desimal dari jam, tetapi formatnya salah. Mereka akan memilih format waktu dalam kasus ini, yang seharusnya tidak dalam format waktu sama sekali; seharusnya hanya dalam format Angka. Dan pastikan untuk menyertakan setidaknya 2 tempat desimal. Klik dua kali untuk menyalinnya, dan kita mendapatkan waktu kita. "
Baiklah, sekarang, kembali ke pertanyaan kita di sini tentang "Format angka mana yang harus kita gunakan?", Ini adalah tempat yang bagus untuk membicarakan "Mengapa?". Jika kita memilih semua angka ini, di sini, kita akan melihat bahwa jumlahnya adalah 53 jam, dan jika saya datang ke sini dan memasukkan fungsi SUM, kita mendapatkan 53 jam. Itu sempurna. Tapi, jika saya menggunakan fungsi SUM di bawah kolom waktu, dan saya melakukan Alt + = atau AUTOSUM, ini memberi saya 5 jam… 5 jam. Mana yang benar-- 53 atau 5?
Ternyata dalam arti yang aneh, keduanya benar. Tapi, inilah mengapa mereka berdua benar. Saya akan memasukkan fungsi yang disebut = SEKARANG. Anda akan melihat saya sebenarnya merekam ini kembali pada tanggal 9 Mei, jadi, 20 hari yang lalu, dan sekarang jam 7:08. Dan dengan = SEKARANG, terkadang saya ingin menunjukkan tanggal dan waktu, tetapi di lain waktu, saya hanya ingin untuk menunjukkan tanggal. Jadi, saya bisa membuat = SEKARANG beri saya tanggal sekarang, atau, saya bisa membuat = SEKARANG beri saya waktu saat ini. Jadi, fungsi Angka dengan senang hati dapat memotong waktu, atau menghapus tanggal. Dan yang ini yang kami pilih di sini, 13:30, mengatakan, "Hei, aku tidak ingin melihat Jumlah hari; tunjukkan saja waktunya." Ya, itulah masalahnya-- karena ini sebenarnya 2 hari dan 5 jam-- 2 hari adalah 48 jam-- 2 hari dan 5 jam. Dan kami mengatakannya-- kami berkata pada Excel, "Hei, jangan repot-repot menunjukkan hari-harinya. "Yah, kami tidak tahu kami mengatakan itu pada Excel, tapi itulah yang kami katakan pada Excel saat kami memilih yang ini atau yang ini. Baiklah, jadi Anda terpaksa turun ke sini ke 37:30:55. Itu yang akan menunjukkan kepada kita jumlah jam penuh, dan kita mendapatkan jawaban yang benar untuk 53 jam.
Tapi, tampaknya sangat konyol untuk menunjukkan detik-detik ekstra itu ketika kita membulatkannya menjadi 15 menit. Jadi, mari kita kembali ke hal itu. Setelah Anda memilih 37:30:55 untuk mempelajari format Angka di balik ini, klik Kustom. Anda akan melihat bahwa hal yang membuatnya bekerja adalah tanda kurung siku sekitar satu jam. Tapi, kami tidak membutuhkan: ss. Jadi, sekarang kita bisa mengedit format kustom itu, jadi kita punya jam dan menit. Hari ini kita tidak perlu memahami apa artinya; @. Dikatakan bahwa, untuk teks, cukup tunjukkan teksnya; tetapi, biarkan saja bagian itu di sana, bahkan jika Anda tidak memahaminya. Klik OK, dan sekarang kita punya waktu dalam jam dan menit, menyisihkan detik. Tapi, totalnya berhasil. Sungguh, sangat halus, itu 13:30 dan 13:30:55 itu. Anda tahu, itu tidak mengatakan, "Hei, Anda memberi tahu Excel untuk mengabaikan tanggal… hari penuh." Tapi,kamu adalah.
Hal-hal seperti Mengambil Waktu Matematika ada dalam buku ini: KEHIDUPAN: 54 Kiat Excel Terbaik Sepanjang Masa. Klik "Saya" di pojok kanan atas untuk memeriksanya.
Ini terdengar seperti pertanyaan yang mudah; Aku tidak percaya kita sudah 7 menit. Tetapi, sebuah perusahaan ingin menagih dalam seperempat jam, dan mereka selalu ingin membulatkannya ke seperempat jam berikutnya. Jadi, 24 jam sehari, 24x4 = 96; 15 menit = 1/96. Jadi, gunakan CEILING (A2,1 / 96) untuk membulatkan ke kuartal berikutnya yang lebih tinggi. Gunakan MROUND (A2,1 / 96) untuk membulatkan ke seperempat jam terdekat, lalu gunakan FLOOR (A2,1 / 96) untuk membulatkan ke seperempat jam yang lebih rendah-- selalu kembali. Ada 24 jam dalam sehari, jadi Anda dapat melakukan 1/288 untuk mendapatkan periode 5 menit, 1/240 untuk mendapatkan periode 6 menit, 1/1440 untuk membulatkan ke menit terdekat. Dan kemudian, tip bonus ada di bagian akhir (format Angka mana) - Anda selalu ingin kembali ke 37:30:55 itu jika Anda akan menunjukkan jam lebih dari 24 jam.
Untuk mengunduh workbook dari video hari ini, kunjungi URL itu di bawah deskripsi Youtube.
Saya ingin mengucapkan terima kasih karena telah mampir; sampai jumpa di lain waktu untuk netcast lain dari.
Hei! Terima kasih sudah menunggu. Tapi, hanya satu tip bonus lagi di sini (yang bahkan tidak akan saya sebutkan). Apa yang saya mulai di sini-- mari kita bersihkan semuanya-- rumus kecil yang muncul dan menunjuk ke salah satu desimal ini-- jadi, seperempat jam terdekat. Tapi, saya ingin menunjukkan itu bukan sebagai 0,010417. Ini luar biasa-- dalam Pemformatan Angka, sebenarnya ada sesuatu yang disebut Pecahan, dan jika saya memilih "Hingga dua digit," saya akan mendapatkan 1/96; tapi, saya perlu menangani 1/288 itu, jadi saya memilih "Hingga tiga digit," dan masih menampilkan 1/96; tetapi, kemudian ketika saya mengubahnya menjadi yang satu ini - 1/288 - itu akan menampilkannya dengan benar. Sekarang, saya kira saya harus melakukan "empat digit" untuk menangani 1/1440. Apa yang akan dilakukannya? Itu akan memberiku … Tapi,jika saya masuk ke Format Angka, dan pergi ke Kustom, dan hanya menambahkan tanda tanya tambahan di sana, maka kita akan sampai ke 1/1440 pecahan terdekat. Format Angka-- keren, keren.
Unduh File Excel
Untuk mengunduh file excel: round-to-quarter-hour.xlsx
Pemikiran Excel Hari Ini
Saya telah meminta saran dari teman-teman Master Excel saya tentang Excel. Hari ini pemikiran untuk direnungkan:
"Teman tidak mengizinkan teman menggunakan TIDAK LANGSUNG"
Jordan Goldmeier
