Rencana tugas telah dibuat pada lembar kerja Excel. Satu kolom menunjukkan tanggal jatuh tempo untuk setiap tugas penyelesaian. Untuk menyorot tenggat waktu yang terlambat untuk tugas yang diberikan dengan warna, kita memerlukan perbedaan yang tepat antara tanggal di Excel. Untuk melakukan ini, kita akan menggunakan pemformatan bersyarat dengan rumus RAZNDAT.

Cara menghitung selisih tanggal di excel

Penting untuk menyorot dengan warna nama-nama tugas yang tenggat waktunya berakhir dalam 7 hari. Contoh tabel rencana tugas:

Efek akhir dari menyoroti akhir tenggat waktu setelah 7 hari:

Semua tugas yang harus diselesaikan dalam 7 hari disorot dengan warna hijau. Jika Anda mengubah nilai di sel D2, tugas lain akan disorot.

Saran yang bermanfaat! Di sel D2, Anda bisa menggunakan fungsi untuk mendapatkan tanggal hari ini: =HARI INI().

Rumus perbedaan tanggal di excel

Rumusnya mengembalikan perbedaan tanggal antara hari ini dan tanggal target dalam hari. Untuk mengatasi masalah ini, gunakan fungsi RAZNDAT di Excel: di mana saya bisa menemukan rumus ini?

Anda tidak akan menemukan fungsi ini di wizard fungsi atau bahkan di panel FORMULAS. Itu harus selalu dimasukkan secara manual. Argumen pertama pada fungsi harus selalu berupa tanggal terbaru, dan argumen kedua harus selalu berupa tanggal tertinggi. Argumen ketiga dari fungsi tersebut menentukan unit pengukuran kuantitas yang dikembalikan oleh fungsi =RAZNDAT(). Dalam hal ini, itu adalah simbol “d” – hari. Artinya fungsi tersebut mengembalikan jumlah hari. Berikutnya adalah operator<7. То есть формула проверяет, если функция возвращает число меньше чем 7, то формула возвращает значение ИСТИНА и к текущей ячейке применяется условное форматирование. Ссылки на ячейки в первом аргумент абсолютная (значение неизменяемое), а во втором аргументе – относительная, так как проверятся будут несколько ячеек в столбце C.

Jika perlu, Anda dapat menambahkan aturan pemformatan bersyarat baru ke rentang ini, yang akan memperingatkan kami tentang akhir tenggat waktu 2 minggu sebelumnya. Untuk melakukannya, Anda perlu menambahkan aturan pemformatan bersyarat baru untuk rentang A2:A7 dan sedikit mengubah rumus di aturan baru:

Warna pemformatan untuk aturan kedua dapat diatur menjadi kuning. Harus ada 2 aturan pemformatan bersyarat yang diterapkan pada rentang yang sama. Untuk memeriksanya, pilih alat: "HOME" - "Styles" - "Conditional Formatting" - "Manage Rules". Karena kita menjalankan aturan teratas terlebih dahulu, kita harus mengubah urutannya di jendela yang muncul: “Conditional Formatting Rules Manager”. Jika tidak, semua tugas yang dipilih akan diisi sel berwarna kuning. Cukup sorot aturan pertama dan tekan tombol bawah (CTRL+panah bawah), seperti yang ditunjukkan pada gambar:

Akibatnya, rencana tersebut memperingatkan kita pada dua minggu pertama, dan kemudian seminggu sebelum tugas berakhir:

Saran yang bermanfaat! Jika ada banyak aturan yang ditetapkan ke rentang yang sama, ikuti hierarki prioritas urutan eksekusinya di Manajer Manajemen Aturan. Semakin tinggi aturannya, semakin tinggi prioritas pelaksanaannya dibandingkan dengan aturan lain yang berada di bawahnya.

Untuk melakukan tugas tertentu di Excel, Anda perlu menentukan berapa hari telah berlalu antara tanggal tertentu. Untungnya, program ini memiliki alat yang dapat mengatasi masalah ini. Mari cari tahu bagaimana Anda bisa menghitung perbedaan tanggal di Excel.

Sebelum Anda mulai bekerja dengan tanggal, Anda perlu memformat sel agar sesuai dengan format ini. Dalam kebanyakan kasus, saat Anda memasukkan sekumpulan karakter yang mirip dengan tanggal, sel itu sendiri akan diformat ulang. Namun lebih baik melakukannya secara manual untuk melindungi diri Anda dari kejutan.

Sekarang program akan mengenali semua data yang ada di sel yang dipilih sebagai tanggal.

Metode 1: perhitungan sederhana

Cara termudah untuk menghitung selisih hari antar tanggal adalah dengan menggunakan rumus biasa.

Metode 2: Fungsi RAZNDAT

Anda juga dapat menggunakan fungsi khusus untuk menghitung selisih tanggal RAZNDAT. Soalnya rumusnya tidak ada di daftar Function Wizard, jadi harus memasukkan rumusnya secara manual. Sintaksnya terlihat seperti ini:

RAZNDAT(tanggal_mulai,tanggal_akhir, satuan)

"Satuan"— ini adalah format di mana hasilnya akan ditampilkan di sel yang dipilih. Unit yang totalnya akan dikembalikan bergantung pada karakter mana yang dimasukkan ke dalam parameter ini:

• "y" - setahun penuh;
• "m" - bulan penuh;
• "d" - hari;
• "YM" - perbedaan bulan;
• “MD” adalah selisih hari (bulan dan tahun tidak diperhitungkan);
• “YD” adalah selisih hari (tahun tidak diperhitungkan).

Anda juga harus memperhatikan bahwa, tidak seperti metode rumus sederhana yang dijelaskan di atas, saat menggunakan fungsi ini, tanggal mulai harus berada di urutan pertama, dan tanggal akhir harus berada di urutan kedua. Kalau tidak, perhitungannya akan salah.

Metode 3: menghitung jumlah hari kerja

Di Excel juga dimungkinkan untuk menghitung hari kerja antara dua tanggal, tidak termasuk akhir pekan dan hari libur. Untuk melakukan ini, gunakan fungsinya CHISTRABNI. Berbeda dengan operator sebelumnya, operator ini hadir dalam daftar Function Wizard. Sintaks untuk fungsi ini adalah sebagai berikut:

HARI JARINGAN(tanggal_mulai,tanggal_akhir, [hari libur])

Dalam fungsi ini argumen utama sama dengan argumen operator RAZNDAT– tanggal mulai dan berakhir. Ada juga argumen opsional "Liburan".

Sebaliknya, Anda harus mengganti tanggal hari libur non-kerja, jika ada, dengan periode yang dicakup. Fungsi ini menghitung semua hari dalam rentang yang ditentukan, tidak termasuk hari Sabtu, Minggu, serta hari-hari yang ditambahkan oleh pengguna ke argumen "Liburan".

Setelah manipulasi di atas, jumlah hari kerja untuk periode yang ditentukan akan ditampilkan di sel yang telah dipilih sebelumnya.

Seperti yang Anda lihat, Excel memberi penggunanya alat yang cukup nyaman untuk menghitung jumlah hari antara dua tanggal. Pada saat yang sama, jika Anda hanya perlu menghitung selisih hari, maka pilihan terbaik adalah menggunakan rumus pengurangan sederhana daripada menggunakan fungsi. RAZNDAT. Namun jika Anda perlu, misalnya, menghitung jumlah hari kerja, maka fungsi tersebut akan membantu HARI JARINGAN. Artinya, seperti biasa, pengguna harus memutuskan alat eksekusi setelah dia menetapkan tugas tertentu.

Kalkulator tanggal dirancang untuk menghitung jumlah hari antar tanggal, serta untuk menemukan tanggal dengan menambahkan atau mengurangi sejumlah hari tertentu ke tanggal yang diketahui.

Tambahkan hari hingga saat ini

Untuk mengetahui tanggal berapa dalam beberapa hari tertentu, gunakan opsi ini. Masukkan tanggal mulai dan jumlah hari untuk ditambahkan ke dalamnya. Untuk mengurangi, gunakan nilai minus. Kalkulator juga memiliki opsi untuk menambahkan hari kerja saja.

Menghitung jumlah hari antar tanggal

Metode perhitungan ini akan menjawab pertanyaan “berapa hari telah berlalu sejak tanggal tersebut”. Masukkan tanggal mulai dan tanggal akhir dan klik tombol "hitung". Kalkulator akan menunjukkan berapa hari antara tanggal yang dimasukkan. Secara terpisah, kalkulator akan menunjukkan jumlah hari kerja.

Dengan menggunakan opsi ini, Anda dapat menghitung berapa hari tersisa hingga acara tertentu, misalnya ulang tahun atau hari libur. Untuk melakukannya, masukkan tanggal hari ini di kolom tanggal mulai, dan tanggal acara di kolom tanggal akhir.

Liburan

Kalkulator dapat menghitung, menambah dan mengurangi hari kalender dan hari kerja. Hari libur resmi non-kerja adalah:

• 1,2,3,4,5,6,8 Januari - Liburan Tahun Baru
• 7 Januari - Natal Ortodoks
• 23 Februari - Hari Pembela Tanah Air
• 8 Maret - Hari Perempuan Internasional
• 1 Mei - Musim Semi dan Hari Buruh
• 9 Mei - Hari Kemenangan
• 12 Juni - Hari Rusia
• 4 November - Hari Persatuan Nasional

Apabila hari libur jatuh pada hari Sabtu atau Minggu, maka dipindahkan ke hari kerja berikutnya. Namun terkadang akhir pekan dipindahkan ke tempat yang sama sekali berbeda di kalender. Misalnya, hari Sabtu dan Minggu yang jatuh pada libur Tahun Baru dapat dipindah ke bulan Mei untuk memperpanjang libur bulan Mei.

Jadi, pada tahun 2019 situasinya sebagai berikut...

Penundaan liburan di tahun 2019

Selain tanggal libur resmi, pada tahun 2019 ini juga terdapat akhir pekan pada tanggal 2, 3, dan 10 Mei karena adanya penundaan akhir pekan dari libur Tahun Baru.

Saat menghitung hari, kalkulator kami memperhitungkan tanggal hari libur resmi dan semua transfer.

Untuk menghitung durasi interval waktu, paling mudah menggunakan fungsi RAZNDAT( ) , DATEDIF() versi bahasa Inggris.

Fungsi RAZNDAT() tidak ada dalam bantuan EXCEL2007 dan di Penyihir Fungsi (MENGGESER+ F3 ), tetapi berhasil, meski bukan tanpa kekurangan.

Sintaks fungsi:

TANGGAL(tanggal_mulai; tanggal_akhir; metode_pengukuran)

Argumen mulai tanggal harus didahulukan sebelum argumen tanggal terakhir.

Argumen Metode Pengukuran menentukan bagaimana dan dalam satuan apa interval antara tanggal mulai dan tanggal berakhir akan diukur. Argumen ini dapat mempunyai arti sebagai berikut:

Di bawah ini adalah penjelasan rinci tentang keenam nilai argumen Metode Pengukuran, serta rumus alternatif (fungsi RAZNDAT() dapat diganti dengan rumus lain (walaupun cukup rumit). Hal ini dilakukan pada file contoh).

Dalam file contoh, nilai argumennya adalah mulai tanggal ditempatkan dalam sebuah sel A2 , dan nilai argumennya tanggal terakhir– di dalam sel PADA 2 .

1. Selisih hari (“d”)

Rumus =DATEDAT(A2;B2,"d") akan mengembalikan selisih hari sederhana antara dua tanggal.

Contoh 1:mulai tanggal 25.02.2007, tanggal terakhir 26.02.2007
Hasil: 1 hari).

Contoh ini menunjukkan bahwa saat menghitung lama layanan, fungsi RAZNDAT() harus digunakan dengan hati-hati. Jelasnya, jika seorang karyawan bekerja pada tanggal 25 dan 26 Februari, maka ia bekerja 2 hari, bukan 1 hari. Begitu pula dengan perhitungan bulan penuh (lihat di bawah).

Contoh2:mulai tanggal 01.02.2007, tanggal terakhir 01.03.2007
Hasil: 28 (hari)

Contoh3:mulai tanggal 28.02.2008, tanggal terakhir 01.03.2008
Hasil: 2 (hari), karena Tahun 2008 merupakan tahun kabisat

Catatan: Jika anda hanya tertarik pada hari kerja, maka k antara dua tanggal dapat dihitung dengan rumus = NETWORKDAYS(B2;A2)

2. Selisih bulan penuh (“m”)

Rumus =DATE(A2;B2;"m") akan mengembalikan jumlah bulan penuh antara dua tanggal.

Contoh 1:mulai tanggal 01.02.2007, tanggal terakhir 01.03.2007
Hasil: 1 bulan)

Contoh2:mulai tanggal 01.03.2007, tanggal terakhir 31.03.2007
Hasil: 0

Dalam menghitung masa kerja, pegawai yang bekerja sepanjang hari dalam sebulan dianggap telah bekerja 1 bulan penuh. Fungsi RAZNDAT() tidak berpikir demikian!

Contoh3:mulai tanggal 01.02.2007, tanggal terakhir 01.03.2009
Hasil: 25 bulan

=12*(TAHUN(B2)-TAHUN(A2))-(BULAN(A2)-BULAN(B2))-(HARI(B2)<ДЕНЬ(A2))

Perhatian: Pada bantuan MS EXCEL (lihat bagian Menghitung umur) terdapat rumus melengkung untuk menghitung jumlah bulan antara 2 tanggal:

=(TAHUN(TDATE())-TAHUN(A3))*12+BULAN(TDATE())-BULAN(A3)

Jika alih-alih fungsi TDATE() - tanggal sekarang, gunakan tanggal 31/10/1961, dan masukkan 01/11/1962 di A3, maka rumus akan menghasilkan 13, meskipun sebenarnya 12 bulan dan 1 hari telah berlalu (November dan Desember tahun 1961 + 10 bulan tahun 1962).

3. Selisih tahun penuh (“y”)

Rumus =DATE(A2;B2;"y") akan mengembalikan jumlah tahun penuh antara dua tanggal.

Contoh 1:mulai tanggal 01.02.2007, tanggal terakhir 01.03.2009
Hasil: 2 tahun)

Contoh2:mulai tanggal 01.04.2007, tanggal terakhir 01.03.2009
Hasil: 1 tahun)

Rumusnya dapat diganti dengan ekspresi alternatif:
=IF(TANGGAL(TAHUN(B2),BULAN(A2),HARI(A2))<=B2;
TAHUN(B2)-TAHUN(A2);TAHUN(B2)-TAHUN(A2)-1)

4. Selisih bulan penuh tidak termasuk tahun (“ym”)

Rumus =DASDAT(A2;B2;"ym") akan mengembalikan jumlah bulan penuh antara dua tanggal, tidak termasuk tahun (lihat contoh di bawah).

Contoh 1:mulai tanggal 01.02.2007, tanggal terakhir 01.03.2009
Hasil: 1 (bulan), karena Tanggal akhir 01/03/2009 dan tanggal mulai yang diubah 01/02 dibandingkan. 2009 (tahun tanggal mulai diganti dengan tahun tanggal akhir, karena 01.02 kurang dari 01.03)

Contoh2:mulai tanggal 01.04.2007, tanggal terakhir 01.03.2009
Hasil: 11 (bulan), karena Tanggal akhir 01/03/2009 dan tanggal mulai yang diubah 01/04 dibandingkan. 2008 (tahun tanggal mulai diganti dengan tahun tanggal akhir dikurangi 1 tahun, Karena 01.04 lebih dari 01.03)

Rumusnya dapat diganti dengan ekspresi alternatif:
=RMAT(C7,12)
Di dalam sel C7 selisihnya harus dalam satu bulan penuh (lihat pasal 2).

5. Selisih hari tidak termasuk bulan dan tahun (“md”)

Rumus =DASDAT(A2;B2;"md") akan mengembalikan jumlah hari antara dua tanggal tanpa memperhitungkan bulan dan tahun. Tidak disarankan menggunakan fungsi RAZNDAT() dengan argumen ini (lihat contoh di bawah).

Contoh 1:mulai tanggal 01.02.2007, tanggal terakhir 06.03.2009
Hasil1: 5 (hari), karena Tanggal akhir 03/06/2009 dan tanggal mulai yang dimodifikasi 01 dibandingkan. 03 .2009 (tahun dan bulan tanggal mulai diganti dengan tahun dan bulan tanggal akhir, karena 01 kurang dari 06)

Contoh2:mulai tanggal 28.02.2007, tanggal terakhir 28.03.2009
Hasil2: 0, karena Tanggal akhir 28/03/2009 dan tanggal mulai yang dimodifikasi 28 dibandingkan. 03 .2009 (tahun dan bulan tanggal mulai diganti dengan tahun dan bulan tanggal akhir)

Contoh3:mulai tanggal 28.02.2009, tanggal terakhir 01.03.2009
Hasil3: 4 (hari) - hasil yang sama sekali tidak dapat dipahami dan SALAH. Jawabannya seharusnya =1. Apalagi hasil perhitungannya tergantung pada versi EXCEL.

Versi EXCEL 2007 dengan SP3:

Hasil – 143 hari! Lebih dari hari dalam sebulan!

Versi EXCEL 2007:

Perbedaan antara 28/02/2009 dan 01/03/2009 adalah 4 hari!

Apalagi di EXCEL 2003 dengan SP3, rumusnya mengembalikan hasil yang benar 1 hari. Untuk nilai 31/12/2009 dan 01/02/2010 hasilnya umumnya negatif (-2 hari)!

Saya tidak menyarankan menggunakan rumus dengan nilai argumen di atas. Rumusnya dapat diganti dengan ekspresi alternatif:
=IF(HARI(A2)>HARI(B2);
HARI(BULAN(TANGGALBULAN(B2,-1),0))-HARI(A2)+HARI(B2);
HARI(B2)-HARI(A2))

Rumus ini hanya merupakan ekspresi yang setara (dalam banyak kasus) untuk RAZNDAT() dengan parameter md. Baca tentang kebenaran rumus ini di bagian “Sekali lagi tentang kelengkungan RAZNDAT()” di bawah.

6. Selisih hari tidak termasuk tahun (“yd”)

Rumus =DASDAT(A2;B2,"yd") akan mengembalikan jumlah hari antara dua tanggal, tidak termasuk tahun. Tidak disarankan untuk menggunakannya karena alasan yang disebutkan di paragraf sebelumnya.

Hasil yang dikembalikan oleh rumus =DATEDAT(A2;B2,"yd") bergantung pada versi EXCEL.

Rumusnya dapat diganti dengan ekspresi alternatif:
=IF(TANGGAL(TAHUN(B2),BULAN(A2),HARI(A2))>B2;
B2-TANGGAL(TAHUN(B2)-1,BULAN(A2),HARI(A2));
B2-TANGGAL(TAHUN(B2),BULAN(A2),HARI(A2)))

Sekali lagi tentang kelengkungan RAZNDAT()

Mari kita cari perbedaan antara tanggal 16/03/2015 dan 30/01/15. Fungsi RAZNDAT() dengan parameter md dan ym akan menghitung selisihnya adalah 1 bulan 14 hari. Benarkah?

Memiliki rumus yang setara dengan RAZNDAT(), Anda dapat memahami kemajuan penghitungan. Jelasnya, dalam kasus kita jumlah bulan penuh antara tanggal = 1, yaitu. sepanjang bulan Februari. Untuk menghitung hari, fungsi ini mencari jumlah hari di bulan sebelumnya relatif terhadap tanggal akhir, yaitu. 28 (tanggal akhir pada bulan Maret, bulan sebelumnya pada bulan Februari, dan pada tahun 2015 terdapat 28 hari pada bulan Februari). Setelah ini, kurangi hari awal dan tambahkan hari akhir = HARI(BULAN(TANGGALBULAN(B6,-1),0))-HARI(A6)+HARI(B6), yaitu. 28-30+16=14. Menurut kami, masih ada 1 bulan penuh antara tanggal dan seluruh hari di bulan Maret, yaitu 16 hari, bukan 14! Kesalahan ini terjadi ketika terdapat lebih sedikit hari pada bulan sebelumnya dibandingkan tanggal akhir dibandingkan hari pada tanggal mulai. Bagaimana cara keluar dari situasi ini?

Mari kita ubah rumus menghitung selisih hari tanpa memperhitungkan bulan dan tahun:

=IF(DAY(A18)>DAY(B18);IF((DAY(MONTH(DATEMONTH(B18,-1),0))-DAY(A18))<0;ДЕНЬ(B18);ДЕНЬ(КОНМЕСЯЦА(ДАТАМЕС(B18;-1);0))-ДЕНЬ(A18)+ДЕНЬ(B18));ДЕНЬ(B18)-ДЕНЬ(A18))

Saat menerapkan fungsi baru, perlu diingat bahwa perbedaan hari akan sama untuk beberapa tanggal mulai (lihat gambar di atas, tanggal 28-31/01/2015). Dalam kasus lain, rumusnya setara. Rumus apa yang harus saya gunakan? Ini terserah pengguna untuk memutuskan tergantung pada kondisi tugas.