Cara menggunakan DATEDIF() dalam pembelajaran fungsi Excel

Artikel ini akan memperkenalkan anda kepada fungsi DATEDIF! Fungsi DATEDIF bukan sahaja boleh digunakan untuk mengira umur, tempoh perkhidmatan, tempoh gaji perkhidmatan dan kitaran projek, tetapi juga boleh digunakan untuk membuat peringatan kira detik hari lahir, peringatan kira detik tarikh siap projek dan sebagainya. Dengan itu, anda tidak akan terlepas lagi hari-hari penting itu, sama ada hari lahir orang tersayang, hari penyiapan projek atau hari tamat pengajian anak lelaki atau perempuan anda.

Fungsi DATEDIF berbeza daripada fungsi yang biasa kita lihat. Seperti yang kita sedia maklum, secara amnya kita hanya perlu memasukkan beberapa huruf pertama sesuatu fungsi dalam EXCEL, dan EXCEL akan secara automatik memaparkan fungsi tersebut Namun, selepas semua huruf fungsi tersebut telah dimasukkan, EXCEL masih tidak mempunyai sebarang gesaan . Sesetengah rakan mungkin tertanya-tanya sama ada terdapat fungsi sedemikian. Sebenarnya, fungsi DATEDIF ialah fungsi tersembunyi dalam EXCEL Ia tidak tersedia dalam formula bantuan dan sisipan, dan hanya boleh dimasukkan secara manual.

Tiada gesaan untuk input fungsi bukan tersembunyi

Tiada gesaan untuk input fungsi tersembunyi

Fungsi DATEDIF boleh bukan sahaja digunakan untuk mengira umur, Tempoh perkhidmatan, gaji kekananan, kitaran projek, dan juga boleh digunakan sebagai peringatan kira detik hari lahir, peringatan kira detik tarikh siap projek, dll. Jom kenali di bawah.

1 Pengenalan pertama kepada DATEDIF

Fungsi DATEDIF digunakan untuk mengira perbezaan antara dua tarikh dan mengembalikan bilangan tahun, bulan dan hari antara dua tarikh

Struktur fungsi: DATEDIF (tarikh mula, tarikh tamat, jenis kembali)

1. Penjelasan parameter

1) Tarikh mula dan Tarikh tamat

Tarikh mula dan tarikh tamat ialah dua tarikh yang perlu mengira perbezaannya.

Kaedah input bagi kedua-dua tarikh ini adalah seperti berikut:

① Anda boleh terus memasukkan tarikh dengan tanda petikan, seperti "2017/10/16". Ambil perhatian bahawa tarikh mula tidak boleh lebih awal daripada 1900, dan tarikh tamat mestilah lebih besar daripada tarikh mula.

② Anda juga boleh terus merujuk tarikh dalam sel

③ Anda juga boleh menggunakan fungsi lain untuk mendapatkan ia, seperti HARI INI () (Nota: Tarikh sampel ialah 15 Februari 2019)

2) Jenis pemulangan

jenis pulangan digunakan Tetapkan jenis hasil penyelesaian. Jenis pulangan ialah teks, dan petikan berganda diperlukan semasa memasukkan.

y: Mengembalikan bilangan tahun penuh antara dua tarikh (tidak termasuk yang kurang daripada satu tahun)

m: Mengembalikan bilangan bulan keseluruhan yang berbeza antara dua tarikh (tidak termasuk yang kurang daripada satu bulan)

d: Mengembalikan perbezaan bilangan hari antara dua tarikh

ym: Mengira perbezaan dalam bulan keseluruhan antara dua tarikh selepas meninggalkan perbezaan sepanjang tahun. Sebagai contoh, jika kedua-dua tarikh (2017-4-20, 2019-2-20) berbeza sebanyak 1 tahun dan Oktober, dan sepanjang tahun ditinggalkan dan perbezaannya ialah 1 tahun, keputusan ym ialah Oktober. Sebagai contoh lain, jika dua tarikh (2018-4-20, 2019-2-20) adalah 10 bulan, keputusan ym ialah Oktober.

yd: Kira perbezaan hari antara dua tarikh selepas meninggalkan perbezaan sepanjang tahun. Contohnya, jika perbezaan antara dua tarikh (2017-4-20, 2019-2-20) ialah 1 tahun dan 306 hari, dan perbezaan sepanjang tahun diabaikan, hasil ym ialah 306 hari.

md: Kira perbezaan hari antara dua tarikh selepas meninggalkan perbezaan dalam keseluruhan tahun dan bulan keseluruhan. Sebagai contoh, jika perbezaan antara dua tarikh (2017-4-20, 2019-2-25) ialah 1 tahun, 10 bulan dan 5 hari, jika perbezaan 1 tahun dan 10 bulan ditiadakan, hasil md ialah 5 hari.

2. Berangan kecil

Beri saya buah berangan

DATEDIF("2017/2/15","2019/2/15","y"), pengiraan "2017/2 / Terdapat perbezaan beberapa tahun penuh antara 15" dan "2019/2/15". Perbezaan di sini ialah dua tahun lengkap, jadi ia sama dengan 2.

DATEDIF("2017/1/6","2019/2/15","d"), hitung "2017/1/6" dan "2019/ Bilangan hari antara 2/15" bersamaan dengan 770.

DATEDIF("2017/1/6","2019/2/15","ym"), mengira bilangan bulan antara dua tarikh tidak termasuk tahun penuh. Perbezaan sebenar antara kedua-dua tarikh ialah 25 bulan, termasuk 2 tahun keseluruhan (24 bulan), jadi nilai pulangan jenis ym ialah 25-24=1.

DATEDIF("2017/1/6","2019/2/15","yd"), mengira bilangan hari antara dua tarikh tidak termasuk sepanjang tahun. Perbezaan sebenar antara kedua-dua tarikh ialah 770 hari, termasuk 2 tahun keseluruhan (730 hari), jadi nilai pulangan jenis yd ialah 770-730=40.

3. Perkara utama penggunaan

1) Petikan berganda

kepada di sini, saya percaya bahawa rakan-rakan saya sudah mempunyai pemahaman awal tentang fungsi DATEDIF, dan anda boleh menulis beberapa formula untuk diamalkan. Sila beri perhatian kepada penggunaan tanda petikan berganda semasa menulis formula.

(1) Jika parameter pertama dan kedua perlu memasukkan tarikh secara langsung, tarikh mesti disertakan dalam petikan berganda.

(2) Parameter ketiga ialah teks, jadi pastikan anda memasukkan petikan berganda.

2) Jenis ralat

Jika ralat berlaku dalam fungsi DATEDIF, biasanya terdapat tiga kategori:

错误代码	错误原因
#NUM!	①函数第三参数返回类型输入值有误  ②第一参数比第二参数大
#VALUE!	开始或结束日期所引用的单元格格式不是日期格式
#NAME?	①函数输入有误  ②文本类型的数据没带双引号

Kod ralat

Sebab ralat

#NUM!

①Nilai input jenis pulangan parameter ketiga fungsi adalah salah ②Parameter pertama lebih besar daripada parameter kedua

#VALUE!

Format sel yang dirujuk oleh tarikh mula atau tamat bukanlah format tarikh

#NAME?

①Input fungsi salah ②Data jenis teks tidak mempunyai petikan berganda

2. Contoh aplikasi praktikal fungsi DATEDIF

1. pekerja berikut, Cari umur mereka tahun ini.

Jangan mengintip jawapan~

Formula:

=DATEDIF(D2,TODAY(),"y")

Fungsi TODAY() memperoleh tarikh semasa sistem Contoh yang disenaraikan ialah hasil pengiraan pada 2019/2/15, yang mungkin tidak semestinya sepadan dengan keputusan yang diperolehi oleh rakan~

Bagaimana pula? Bukankah ia sangat mudah?

2. Kira umur berdasarkan nombor ID

Tarikh lahir sudah tersedia dalam contoh sebelumnya, jadi gunakan Fungsi DATEDIF terus untuk menggunakannya Fungsi TODAY boleh mengira umur. Jika anda hanya mempunyai nombor ID, untuk mengira umur, anda perlu mengeluarkan tarikh lahir daripada nombor ID dan kemudian mengiranya. Formulanya adalah seperti berikut:                              🎜 >① Gunakan fungsi MID untuk mengekstrak tarikh lahir 8 digit dalam nombor kad pengenalan.

②Gunakan fungsi TEKS untuk memaparkan nombor 8 digit ini dalam format "0-00-00" untuk mendapatkan teks dalam format tarikh, dan kemudian tambahkannya sebelum fungsi TEXT Operasi negatif-ke-positif yang menukar teks kepada tarikh.

③ Akhir sekali, gunakan tarikh yang diperolehi di atas sebagai tarikh mula fungsi DATEDIF, gunakan TODAY() sebagai tarikh tamat dan tetapkan jenis pulangan kepada "y" untuk mengira Bilangan tahun penuh antara dua tarikh - umur.

3. Kira tempoh perkhidmatan pekerja berdasarkan tarikh menyertai (dipaparkan dalam bentuk tahun, bulan dan hari)

Gunakan kes 1 kaedah pengiraan umur Jika masa pekerja masuk diketahui, iaitu Tempoh perkhidmatan pekerja dalam tahun penuh boleh dikira. Tetapi jika anda perlu mengira tempoh perkhidmatan pekerja terperinci, seperti berapa tahun, bulan dan hari, apakah yang perlu anda lakukan? Jawapannya adalah seperti berikut:

Walaupun formulanya panjang, ia amat mudah difahami. Mula-mula, gunakan tiga fungsi DATEDIF untuk mengira perbezaan dalam tahun, bulan dan hari antara dua tarikh, dan akhirnya gunakan penyambung teks "&" untuk menyambung untuk mendapatkan hasilnya.

4. Kira gaji kekananan

Mengikut peraturan gaji kekananan yang dikeluarkan oleh negara pada 2019, pekerja yang telah bekerja secara berterusan selama satu tahun ialah 50 yuan/bulan; pekerja yang telah bekerja secara berterusan selama dua tahun ialah 100 yuan/bulan/bulan selama tiga tahun berturut-turut selama empat tahun berturut-turut, dan seterusnya, dengan had terkumpul sepuluh tahun;

Adakah anda keliru? Tidak mengapa, mari kita pergi langkah demi langkah, mula-mula mengira tempoh perkhidmatan (dikira dalam tahun penuh).

Formula:

Kemudian, muncul sorotan kami, mengira gaji kekananan.

Di sini kita menggunakan fungsi IF dan fungsi MIN.

Mengikut peraturan gaji kekananan yang dikeluarkan negara pada 2019, gaji 1-3 tahun perkhidmatan naik 50 setiap tahun, dan gaji 4-10 tahun perkhidmatan naik 30 setiap tahun. Kita boleh menggunakan fungsi IF untuk menilai secara berasingan.

Mula-mula tentukan sama ada kekananan E2 kurang daripada 4. Jika kurang daripada 4, bermakna gaji kekananan pekerja dinaikkan sebanyak 50 setiap tahun, dan keputusan "

" dikembalikan; jika kekananan E2 tidak kurang daripada 4, gaji kekananan ialah 150 berdasarkan kenaikan sebanyak 30 setiap tahun, mengembalikan keputusan "

". =DATEDIF(C2,D2,"y")

Disebabkan gaji seniority hanya boleh kumpul sepuluh tahun, dan gaji senioriti lebih sepuluh tahun konsisten dengan gaji senioriti sepuluh tahun, jadi kita guna fungsi MIN untuk pulangkan nilai minimum antara 10 dan E2 sebagai kekananan.

5. Buat peringatan hari lahir pekerja

Berikut ialah jadual maklumat pekerja Kami ingin membuat peringatan hari lahir untuk mengingatkan pekerja 7 hari lebih awal bahawa hari lahirnya akan datang.

Petua: Gunakannya dalam kombinasi dengan fungsi IF, fikirkan~

① ② ③

Adakah anda rasa formula ini membara?

Kami biasanya mengira bilangan hari sehingga hari lahir kami dengan menolak tarikh hari ini daripada tarikh hari lahir yang akan datang. Formula ini berbeza daripada yang biasa kita kira dengan menolak tarikh lahir dari tarikh hari ini, dan juga mengurangkan tarikh lahir sebanyak 7 hari.

Mengapa anda melakukan ini?

Pertama, mari kita lihat bilangan hari antara tarikh semasa dan tarikh lahir di bawah jenis pulangan yd. Jadual berikut mengambil tarikh lahir 22 Februari 1999 sebagai contoh, menunjukkan bilangan hari dari semalam, hari ini, esok, lusa, dsb. hingga tarikh lahir.

Formula sel N16 = DATEDIF($J$13,N15,"yd"), $J$13 mewakili tarikh lahir dan N15 mewakili tarikh semasa yang berbeza.

Jelas sekali, selang pada hari lahir ialah 0; jika ia kurang daripada tarikh hari lahir, semakin dekat tarikh itu dengan hari lahir, semakin besar selangnya, semakin hampir kepada 365; lebih besar daripada tarikh hari lahir, semakin dekat tarikh itu dengan hari lahir, semakin kecil selangnya menghampiri 0.

Kedua, dalam kes ini, gunakan terus fungsi IF untuk memberikan formula peringatan hari lahir berdasarkan sama ada selang kurang daripada atau sama dengan 7 =IF(DATEDIF($J$13,N15,"yd") Ia adalah hari lahir tidak lama lagi La","") tidak dapat menyedari peringatan itu 7 hari lebih awal. Sebaliknya, ia hanya boleh merealisasikan peringatan pada hari lahir dan 7 hari selepas hari lahir, seperti berikut:

Akhirnya, ada dua cara untuk mengingatkan anda 7 hari lebih awal, cuba buat selang 0-7 muncul 7 hari lebih awal hari ($J$13-7), atau tukar tarikh tamat Tambah 7 hari (N15+7), seperti berikut:

Bilangan hari selepas tarikh mula. dikurangkan sebanyak 7 hari

Peringatan hari lahir selepas tarikh mula dikurangkan sebanyak 7 hari

Kaedah kedua ialah mengubah suai syarat penghakiman kepada >=358 seperti berikut:

Selepas mengubah suai syarat penghakiman, tidak akan ada peringatan pada hari lahir

Ok, sekarang saya percaya semua orang faham formula sebelumnya. Atas dasar ini, kami boleh mengubah suai formula untuk menjadikan peringatan lebih mesra pengguna:

=IF(DATEDIF(D3-7,TODAY(),"yd")and"&7-DATEDIF(D3-7 ,TODAY(),"yd")&"It's my birthday","")

Beberapa perkataan lagi: Jika kita menggunakan pendekatan biasa untuk mengira bilangan hari sehingga hari lahir dengan menolak tarikh semasa daripada tarikh hari lahir yang akan datang, bagaimanakah kita harus menulis formula peringatan hari lahir? Jawapannya adalah seperti berikut:

=IF(DATEDIF(TODAY(),IF(TEXT(D3,"M month DD day")

month DD day"),YEAR(TODAY()+365 ), TAHUN (HARI INI()))&"tahun"&TEKS(D3,"M bulan DD hari"),"yd")Hari lahir anda tidak lama lagi","")(hari ini(),"m

Ini adalah formula yang sangat panjang!

hari DD bulan"), YEAR(TODAY()+365), YEAR(TODAY()))&"Year"&TEXT(D3,"M month DD day") gunakan untuk mendapatkan tarikh hari lahir yang akan datang. Maksud: Jika bilangan bulan dan hari dalam tarikh lahir kurang daripada bilangan bulan dan hari hari ini, bermakna hari lahir tahun ini telah berlalu, dan tarikh hari lahir baharu hendaklah YEAR(TODAY()+365)& "Tahun"&TEXT(D3,"M bulan DD hari"; Sebaliknya, ini bermakna hari lahir tahun ini belum berlalu dan tarikh hari lahir hendaklah YEAR(TODAY())&"年"&TEXT(D3," M bulan DD hari".

(hari ini(),"m

YEAR(TODAY()) mengekstrak tahun ini dan menambah 365 untuk mendapatkan tahun depan.

TEKS(D3,"m bulan dd hari") mengeluarkan bulan dan nombor dalam tarikh lahir.

Pada ketika ini, pengenalan fungsi DATEDIF telah selesai. Sama ada mengira umur, tempoh perkhidmatan, gaji kekananan atau memberi peringatan hari lahir, anda boleh menggunakan DATEDIF. Sudah tentu, DATEDIF juga boleh digunakan untuk mengira masa projek, bilangan hari sehingga siap, dan memberikan peringatan kira detik untuk siap. Jika anda melakukan pengurusan kakitangan, gaji atau projek, mula berlatih sekarang!

Cadangan pembelajaran berkaitan: tutorial cemerlang

Atas ialah kandungan terperinci Cara menggunakan DATEDIF() dalam pembelajaran fungsi Excel. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!