Perhitungan dengan tanggal dan waktu di excel. Memformat angka sebagai nilai tanggal dan waktu

untuk mengkonsolidasikan dalam praktik pekerjaan dengan wizard fungsi;
belajar menggunakan fungsi Tanggal dan Waktu.

Perangkat Lunak: Ms Office: Ms Excel 2007

Selama kelas

1. Bagian organisasi

1.1. Periksa mereka yang hadir di pelajaran;
1.2. Periksa kesiapan siswa untuk pelajaran.

2. Pengantar pengantar

2.1. Menguji pengetahuan materi yang disampaikan (survei frontal);

Apa itu fungsi?
Berapa banyak fitur yang disertakan dalam Excel, dan kategori apa yang dikategorikan?
Apa cara untuk memanggil jendela "Function Wizards"?
Fungsi agregat dan sintaksnya?

Dalam proses pengulangan, layar menampilkan presentasi dari pelajaran sebelumnya sambil mempelajari topik “Fungsi statistik”.

2.2. Laporkan topik dan tujuan pelajaran baru;
2.3. Penjelasan materi baru;

2.3.1. Jelaskan dan tunjukkan dengan proyektor sintaks fungsi Tanggal dan Waktu;

2.4. Pengulangan persyaratan perlindungan tenaga kerja.

3. Pengarahan saat ini.

3.1. Bagikan selebaran;
3.2. Bypass yang ditargetkan;
3.3. Memeriksa organisasi tempat kerja yang benar;
3.4. Memeriksa kebenaran teknik;
3.5. Bekerja dengan lamban;
3.6. Kontrol pengetahuan.

4. Pengarahan akhir.

4.1. Analisis tugas yang telah diselesaikan;
4.2. Mengurai kesalahan;
4.3. Laporan perkiraan.

Ringkasan pengantar pengantar

Gunakan fungsi tanggal dan waktu untuk mengonversi format tanggal dan waktu, menghitung rentang waktu, dan memasukkan nilai tanggal dan waktu yang diperbarui secara otomatis ke dalam lembar kerja. Ada total 20 fungsi dalam kategori Tanggal dan Waktu.

Excel menyimpan tanggal sebagai angka dari 1 hingga 2.958.465 (hingga 65.380 dalam versi yang lebih lama dari Excel 97). Angka-angka ini adalah "nomor berurutan" pada hari yang ditentukan, dihitung mulai 1 Januari 1900. Nomor urut 1 sesuai dengan 1 Januari 1900, nomor 2 adalah 2 Januari 1900, dst. Tanggal penulisan ini adalah 20 Oktober 2012. memiliki nomor seri 41202.

Sistem ini memungkinkan Anda untuk menggunakan tanggal dalam rumus. Misalnya, ini dapat digunakan untuk menghitung jumlah hari antara dua tanggal dengan mudah. Anehnya, tapi di program excel ada tanggal dengan nomor seri sama dengan nol - 01 Januari 1900. Tanggal tidak ada ini digunakan untuk mewakili data sementara yang tidak terkait dengan tanggal tertentu.

Untuk menampilkan nilai numerik sebagai tanggal, cukup terapkan format ke sel tertentu tanggal.

Tanggal maksimum yang tersedia adalah 31 Desember 9999 (dalam versi sebelum Excel 97, 31 Desember 2078).

Excel menyimpan waktu juga dalam bentuk angka, lebih tepatnya, dalam bentuk pecahan desimal dari angka tanggal yang sesuai. Artinya, bagian pecahan yang sesuai dengan bagian hari ditambahkan ke nomor seri tanggal. Misalnya, 06 Maret 2011 memiliki nomor seri 40608, dan siang pada 06 Maret 2011 (12:00) akan sesuai dengan nomor 40608.5 karena 12 jam adalah 0,5 dari 24.

Nilai waktu maksimum yang dapat dimasukkan dalam sel (tanpa nilai tanggal) adalah 9999:59:59 (10 ribu jam kurang satu detik).

Daftar fungsi tanggal dan waktu

Fungsi	Keterangan
NILAI WAKTU	Mengonversi waktu dari format teks ke nomor yang mewakili waktu di Excel.
WAKTU	Mengonversi jam, menit, dan detik, yang ditentukan sebagai angka, menjadi angka sebagai kode Excel.
TAHUN	Mengembalikan tahun yang sesuai dengan tanggal yang ditentukan dari 1900 hingga 9999.
TANGGAL	Mengembalikan bilangan bulat yang mewakili tanggal tertentu.
NILAI TANGGAL	Mengonversi tanggal, yang disimpan sebagai teks, ke nomor seri, yaitu Microsoft Excel menganggapnya sebagai tanggal.
TANGGAL	Mengembalikan ordinal tanggal yang merupakan jumlah bulan tertentu ke depan atau ke belakang dari tanggal yang ditentukan.
HARI	Mengembalikan hari dari tanggal, ditentukan dalam format numerik.
HARI MINGGU	Mengembalikan hari dalam seminggu yang sesuai dengan tanggal.
HARI 360	Mengembalikan jumlah hari antara dua tanggal berdasarkan tahun 360 hari (dua belas bulan dari 30 hari).
PANJANG	Mengembalikan pecahan tahun, yang merupakan jumlah hari antara dua tanggal (awal dan akhir).
DARI EONTHS	Mengembalikan nomor urut hari terakhir bulan yang merupakan jumlah bulan yang ditentukan dari tanggal yang ditentukan dalam argumen tanggal_mulai.
BULAN	Mengembalikan bulan untuk tanggal numerik.
MENIT	Mengembalikan menit yang sesuai dengan argumen nama_waktu.
JUMLAH MINGGU	Mengembalikan nomor minggu untuk tanggal tertentu.
HARI KERJA	Mengembalikan angka yang mewakili tanggal yang merupakan jumlah tertentu hari kerja maju atau mundur dari tanggal mulai.
HARI INI	Mengembalikan tanggal saat ini dalam format numerik.
DETIK	Mengembalikan detik sesuai dengan time_in_numeric_format.
data	Mengembalikan tanggal dan waktu saat ini dalam format numerik.
JAM	Mengembalikan jam yang sesuai dengan waktu yang ditentukan.
HARI PEMBERSIHAN	Mengembalikan jumlah hari kerja antara tanggal_mulai dan tanggal_akhir. Hari libur dan akhir pekan tidak termasuk dalam nomor ini.

Untuk mengkonsolidasikan materi baru, siswa diundang untuk menyelesaikan tugas-tugas praktis di komputer.

Pengarahan saat ini

1. = TIMEVALUE (time_as_text) , mengonversi waktu dari format teks ke angka. Setelah memasukkan rumus, atur format "Waktu" untuk sel;

2. =WAKTU(jam; menit; detik), fungsi mengubah jam, menit dan detik yang diberikan dalam bentuk angka menjadi angka dalam bentuk kode Excel;

3. = TAHUN (date_in_numeric_format), mengembalikan tahun yang sesuai dengan tanggal yang ditentukan dari 1900 hingga 9999;

4. = TANGGAL (tahun; bulan; hari), mengembalikan bilangan bulat yang mewakili tanggal tertentu;

5. = DATEVALUE (date_as_text), fungsi mengembalikan format numerik tanggal yang direpresentasikan sebagai teks. Fungsi DATEVALUE digunakan untuk mengonversi tanggal dari representasi tekstual ke format angka;

6. = TANGGAL EEC (tanggal_mulai; jumlah bulan), fungsi mengembalikan nomor urut tanggal, yang merupakan jumlah bulan yang ditentukan ke depan atau ke belakang dari tanggal yang ditentukan;

7. = HARI (tanggal_as_angka), - mengonversi tanggal dalam format numerik menjadi hari dalam sebulan (dari 1 hingga 31);

8. = WEEKDAY (date_num_format; type), fungsi mengembalikan angka dari 1 hingga 7, sesuai dengan jumlah hari dalam seminggu untuk tanggal yang ditentukan. Dalam contoh ini, pilih "Tipe" 2, mis. Senin adalah nomor 1;

9. = DAYS360 (tanggal_mulai; tanggal_akhir; metode ), fungsi menghitung jumlah hari antara dua tanggal berdasarkan 360 hari dalam setahun (12 bulan x 30 hari).

Metode adalah nilai boolean yang menentukan metode mana, Eropa atau Amerika, yang harus digunakan dalam perhitungan. Dalam contoh ini, tidak ada metode yang dipilih;

10. = PANJANG (tanggal_mulai; tanggal_akhir; basis), fungsi mengembalikan pecahan tahun, yang merupakan jumlah hari di antara dua tanggal. Kami menerapkan format angka ke hasil;

11. = EONMONTHS (tanggal_mulai; angka_bulan), fungsi mengembalikan nomor urut hari terakhir bulan tersebut, yang merupakan jumlah bulan yang ditentukan dari tanggal yang ditentukan dalam argumen "tanggal_mulai";

12. = BULAN ( date_in_numeric_format ), fungsi mengembalikan bulan - angka dari 1 (Januari) hingga 12 (Desember);

13. = MENIT (time_in_numeric_format), mengembalikan menit sebagai angka antara 0 dan 59;

14. = NOMOR MINGGU (angka_tanggal; tipe_pengembalian), fungsi mengembalikan nomor minggu untuk tanggal tertentu;

15. = HARI KERJA (tanggal_mulai; hari_angka; hari libur), fungsi mengembalikan angka yang mewakili tanggal yang merupakan jumlah hari kerja yang ditentukan ke depan atau ke belakang dari tanggal mulai;

16. = HARI INI, fungsi mengembalikan tanggal dalam format numerik (fungsi ini tidak memiliki argumen);

17. = DETIK (waktu_in_numerik_format), fungsi mengembalikan detik sebagai angka dari 0 hingga 59;

18. = ITU, fungsi mengembalikan tanggal dan waktu saat ini dalam format numerik (fungsi ini tidak memiliki argumen);

19. = JAM (format_waktu_in_numerik), fungsi mengembalikan jam sebagai angka dari 0 hingga 23;

20. = NETWORKDAYS (tanggal_mulai; tanggal_akhir; hari libur), fungsi mengembalikan jumlah hari kerja penuh antara dua tanggal;

Kerja Praktek 1

Dalam sel yang dipilih hijau buat perhitungan yang sesuai menggunakan fungsi "Tanggal dan Waktu" (HARI KERJA, HARI KERJA, HARI KERJA);

Kerja Praktek 2

Di sel yang disorot dengan warna biru, lakukan perhitungan yang sesuai menggunakan fungsi "Tanggal dan Waktu" (TAHUN, HARI INI);

Kerja praktek 3

Di sel yang disorot dalam warna abu-abu, lakukan perhitungan yang sesuai menggunakan fungsi "Tanggal dan Waktu" .

Pengarahan terakhir

Analisis tugas yang telah diselesaikan dan kendalikan pengetahuan;
Mengurai kesalahan;
Laporan perkiraan.

Fungsi TANGGAL mengembalikan ordinal dari tanggal tertentu.

Sintaksis: TANGGAL (tahun; bulan; hari)

Argumen untuk fungsi DATE dijelaskan di bawah ini.

Tahun- argumen yang diperlukan. Nilai argumen tahun dapat berisi satu sampai empat digit. Excel menafsirkan argumen tahun sesuai dengan sistem tanggal yang digunakan pada komputer Anda. Secara default, Microsoft Excel untuk Windows menggunakan sistem tanggal 1900, yang berarti 1 Januari 1900 dianggap sebagai tanggal pertama.

Nasihat: Untuk menghindari hasil yang tidak diharapkan, gunakan sebagai nilai argumen tahun empat digit. Misalnya, nilai "07" dapat diartikan sebagai "1907" atau "2007". Penunjukan tahun empat digit mencegah kebingungan.

Jika argumen tahun berada dalam kisaran dari 0 (nol) hingga 1899 (inklusif), Excel menambahkan nilai ini ke 1900 untuk menghitung tahun. Misalnya, fungsi DATE (108; 1; 2) mengembalikan 2 Januari 2008 (1900 + 108).

Jika argumen tahun berada dalam kisaran 1900 hingga 9999 (inklusif), Excel menggunakan nilai ini sebagai tahun. Misalnya, fungsi DATE (2008; 1; 2) mengembalikan 2 Januari 2008.

Jika nilai argumen tahun kurang dari 0 atau lebih besar dari 9999, Microsoft Excel mengembalikan nilai kesalahan #NUM!.

Bulan- argumen yang diperlukan. Bilangan bulat positif atau negatif dalam rentang 1 (Januari) hingga 12 (Desember) yang mewakili bulan dalam setahun.

Jika nilai argumen bulan lebih besar dari 12, argumen bulan menambahkan jumlah bulan ke bulan pertama tahun tertentu. Misalnya, fungsi DATE (2008; 14; 2) mengembalikan angka yang sesuai dengan 2 Februari 2009.

Jika nilai argumen bulan kurang dari 1, argumen bulan Kurangi nilai jumlah bulan itu, bertambah 1, dari bulan pertama tahun yang ditentukan. Misalnya, fungsi DATE (2008; -3; 2) mengembalikan angka yang sesuai dengan 2 September 2007.

Hari- argumen yang diperlukan. Bilangan bulat positif atau negatif dalam rentang 1 hingga 31 yang mewakili hari dalam sebulan.

Jika nilai argumen hari lebih dari jumlah hari dalam bulan yang ditentukan, argumen hari menambahkan jumlah hari ini ke hari pertama bulan itu. Misalnya, fungsi DATE (2008; 1; 35) mengembalikan angka yang sesuai dengan 4 Februari 2008.

Jika nilai argumen hari kurang dari 1, argumen hari mengurangi nilai jumlah hari itu, bertambah 1, dari hari pertama bulan yang ditentukan. Misalnya, fungsi DATE (2008; 1; -15) mengembalikan angka yang sesuai dengan 16 Desember 2007.

Catatan: V tanggal excel disimpan sebagai nomor urut, yang memungkinkan mereka untuk digunakan dalam perhitungan. Tanggal 1 Januari 1900 sesuai dengan angka 1, dan 1 Januari 2008 - 39448, karena interval antara tanggal-tanggal ini adalah 39447 hari. Untuk menampilkan tanggal yang benar, Anda perlu mengubah format sel.

Sintaksis: TANGGAL (tahun; bulan; hari)

Sebagai contoh, = TANGGAL (C2; A2; B2) menggabungkan nilai tahun dari sel C2, bulan dari sel A2, dan hari dari sel B2 dan menempatkannya dalam satu sel sebagai tanggal. Contoh di bawah ini menunjukkan hasil di sel D2.

Perlu memasukkan tanggal tanpa menggunakan rumus? Tidak masalah! Anda dapat memasukkan tanggal saat ini atau yang diperbarui. Anda juga dapat secara otomatis mengisi kolom dengan tanggal.

Mengubah format tanggal

Fungsi DATE menghasilkan tanggal.

= TANGGAL (KIRI (C2,4); TENGAH (C2,5,2); KANAN (C2,2))

Fungsi LEFT menerima 4 karakter pertama dari kiri di sel C2. Ini menjadikan nilai "2014" sebagai tahun dari tanggal yang dikonversi di sel D2.

Fungsi MID mengacu pada sel C2. Dia mendapat 2 digit di sebelah kanan digit ke-5. Ini menjadikan nilai "03" sebagai bulan dari tanggal yang dikonversi di sel D2. "0" tidak disertakan dalam hasil karena formatnya tanggal ditentukan untuk sel D2.

Fungsi KANAN mendapatkan 2 karakter pertama di sel C2 dari tepi kanan dan menambahkan "14" ke sel D2 sebagai nilai hari.

Tambah atau kurangi tanggal dengan jumlah hari yang ditentukan

Untuk menambah atau mengurangi tanggal dengan jumlah hari tertentu, cukup tambahkan ke nilai atau referensi sel dengan tanggal, atau kurangi.

Pada contoh di bawah, sel A5 berisi tanggal yang perlu ditambah atau dikurangi sebesar 7 hari (nilai dalam sel C5).

Saat Anda memasukkan tanggal atau waktu dalam sel, format tanggal dan waktu default akan diterapkan pada mereka. Format ini ditentukan oleh pengaturan tanggal dan waktu regional di Panel Kontrol Windows dan berubah ketika pengaturan ini diubah. Anda dapat menggunakan format lain untuk menampilkan tanggal dan waktu, yang sebagian besar tidak terpengaruh oleh pengaturan panel kontrol.

Dalam artikel ini

Tampilkan nomor sebagai tanggal atau waktu

Anda dapat memformat nilai tanggal dan waktu saat Anda mengetik. Misalnya, jika Anda memasukkan nilai dalam sel 2/2 Excel secara otomatis menafsirkannya sebagai tanggal dan menampilkan tanggal dalam sel. 02.Feb... Jika format ini tidak berfungsi (misalnya, Anda perlu menampilkan tanggal dalam sel sebagai 2 Februari 2009 atau 02.02.09 ), Anda dapat memilih format tanggal yang berbeda di kotak dialog Format sel seperti yang dijelaskan dalam prosedur di bawah ini. Demikian pula, jika Anda memasukkan nilai ke dalam sel 09:30 atau 09:30 Excel secara otomatis menafsirkannya sebagai waktu dan menampilkan waktu di dalam sel. 9:30 PAGI atau 21:30... Format tampilan waktu juga dapat dikonfigurasi dalam kotak dialog. Format sel.

Buat format tanggal atau waktu khusus

Jika "m" muncul tepat setelah kode "h" atau "hh", atau tepat sebelum kode "ss", Excel menampilkan menit, bukan bulan.

Jam, menit dan detik

Menampilkan	Gunakan kode
Jam sebagai angka dari 0 hingga 23
Jam sebagai angka dari 00 hingga 23
Menit sebagai angka dari 0 hingga 59
Menit sebagai angka dari 00 hingga 59
Detik sebagai angka dari 0 hingga 59
Detik sebagai angka dari 00 hingga 59
Jam dalam format "4 AM"
Waktu dalam format "16:36"
Waktu dalam format "4:36:03 P"	H: mm: ss A / P
Waktu yang berlalu dalam jam; misalnya 25:02
Waktu yang berlalu dalam hitungan menit; misalnya 63:46
Waktu yang berlalu dalam hitungan detik
Sepersekian detik

AM dan PM... Jika formatnya berisi AM atau PM, sistem 12 jam digunakan, di mana "AM" atau "A" menunjukkan waktu dari tengah malam hingga tengah hari, dan "PM" atau "P" menunjukkan waktu dari siang hingga tengah hari. Tengah malam. Jika tidak, sistem 24 jam digunakan. Kode “m” atau “mm” harus muncul tepat setelah kode “h” atau “hh”, atau tepat sebelum kode “ss”, jika tidak, Excel akan menampilkan bulan, bukan menit.

Membuat format angka kustom bisa tampak seperti tugas yang menakutkan bagi pengguna yang belum pernah melakukannya sebelumnya. Untuk informasi selengkapnya tentang membuat format angka kustom, lihat artikel membuat dan menghapus format angka kustom.

Untuk menerapkan format tanggal atau waktu default dengan cepat, klik sel yang berisi tanggal atau waktu dan tekan CTRL + SHIFT + # atau [dilindungi email]

Jika, setelah menerapkan format tanggal atau waktu ke sel, ini menampilkan karakter ##### sel mungkin tidak cukup lebar untuk menampilkan data. Untuk menambah lebar kolom yang berisi data sel, klik dua kali batas kanannya. Lebar kolom akan diubah ukurannya secara otomatis untuk mencerminkan isi sel. Anda juga dapat menyeret batas kolom kanan ke lebar yang diinginkan.

Jika Anda mencoba untuk membatalkan penerapan format tanggal atau waktu dengan memilih Umum dalam daftar Format angka, Excel menampilkan kode numerik. Jika Anda memasukkan tanggal atau waktu lagi, format tanggal atau waktu default akan digunakan. Untuk memasukkan tanggal atau waktu dalam format khusus seperti menit januari 2010, Anda dapat memformat nilai sebagai teks dengan memilih Teks dalam daftar Format angka.

Untuk memasukkan tanggal saat ini dengan cepat, pilih sel kosong di lembar kerja dan tekan CTRL +; (titik koma) dan kemudian, jika perlu, tekan Enter. Untuk menyisipkan tanggal yang akan diperbarui ke tanggal saat ini setiap kali Anda membuka lembar kerja atau menghitung ulang rumus, ketik = HARI INI () dalam sel kosong dan tekan Enter.

informasi tambahan

Anda selalu dapat mengajukan pertanyaan kepada Komunitas Teknologi Excel, meminta bantuan di komunitas Answers, atau menyarankan fitur atau peningkatan baru di situs web.