Excel hari dalam seminggu dari nomor. Contoh penggunaan fungsi day from dayweek dan days di excel. Menemukan hari terakhir bulan ini

03.04.2020 Pemulihan data

Misalkan Anda perlu menentukan hari mana dalam seminggu yang sesuai dengan tanggal antara 1900 dan 9999. Cukup mudah untuk melakukan ini di Excel, tetapi Anda akan mendapatkan nomor seri hari dalam seminggu sebagai tanggapan. Selain itu, tergantung pada pengaturan, nomor tersebut dapat dijawab pada hari yang berbeda. Tentu saja, ini sangat tidak nyaman. Oleh karena itu, mari kita buat modul yang akan menentukan hari dalam seminggu berdasarkan tanggal dan mengembalikan bukan nomor urutnya, tetapi namanya.

Hari dalam seminggu memungkinkan Anda untuk menentukan fungsi "WEEKDAY", yang dapat dimasukkan dengan memanggil wizard fungsi dan memilih dalam daftar Kategori elemen Tanggal dan waktu (Gbr. 6.1). Sintaks fungsinya adalah sebagai berikut:

Ini mengembalikan hari dalam seminggu yang sesuai dengan argumen pertama date_in_number_format. Hari dalam seminggu akan direpresentasikan sebagai bilangan bulat antara 1 (Minggu) dan 7 (Sabtu).

Argumen date_number_format adalah kode tanggal-waktu yang digunakan Excel untuk beroperasi pada tanggal. Argumen ini juga dapat ditentukan dalam bentuk teks dengan melampirkan string dalam tanda kutip. Jenisnya adalah angka yang menentukan penomoran hari dalam seminggu. Argumen Type dapat mengambil nilai berikut:

1 (atau dihilangkan) - angka dari 1 (Minggu) hingga 7 (Sabtu);
- 2 - angka dari 1 (Senin) hingga 7 (Minggu);
- 3 - angka dari 0 (Senin) hingga 6 (Minggu).

Jadi, tergantung pada jenis yang ditentukan dalam fungsi WEEKDAY, hari ke-5 dalam seminggu bisa Kamis, Jumat atau Sabtu. Agar tidak menetapkan argumen kedua, kita akan menganggap bahwa hari ke-5 dalam seminggu adalah Kamis.

Ketik di sel A1 tanggal apa pun, seperti 01/06/2002. Untuk kejelasan, atur format tanggal untuk sel ini. Pergi ke sel A2 dan panggil panel fungsi "WEEKDAY" (Gbr. 6.2). di lapangan tanggal_as_number masukkan alamat sel A1.

Artikel ini berisi beberapa rumus berguna yang mengembalikan tanggal tertentu.

Menentukan hari dalam setahun

1 Januari adalah hari pertama tahun ini, dan 31 Desember adalah yang terakhir. Bagaimana dengan sisa hari di antaranya? Rumus berikut mengembalikan hari dalam setahun untuk tanggal yang disimpan di sel A1: =A1-DATE(YEAR(A1);1;0) . Misalnya, jika sel A1 berisi tanggal 16 Februari 2010, rumus mengembalikan 47 karena tanggal tersebut adalah hari ke-47 dalam setahun.

Rumus berikut mengembalikan jumlah hari yang tersisa dalam setahun sejak tanggal tertentu (dengan asumsi itu ada di sel A1): =DATE(YEAR(A1),12,31) .

Menentukan hari dalam seminggu

Jika Anda perlu menentukan hari dalam seminggu untuk kencan, fungsi DAYWEEK mengatasi tugas ini. Fungsi mengambil tanggal sebagai argumen dan mengembalikan bilangan bulat dari 1 hingga 7 yang sesuai dengan hari dalam seminggu. Rumus berikut, misalnya, mengembalikan 6 karena hari pertama tahun 2010 adalah hari Jumat: =WEEKDAY(DATE(2010,1,1)) .

Fungsi DAYWEEK juga mengambil argumen kedua opsional yang menentukan sistem penomoran hari untuk hasilnya. Jika Anda menetapkan 2 sebagai argumen kedua, maka fungsi akan mengembalikan 1 untuk Senin, 2 untuk Selasa, dan seterusnya. Jika Anda menetapkan 3 sebagai argumen kedua, fungsi akan mengembalikan 0 untuk Senin, 1 untuk Selasa, dan seterusnya . .

Anda juga dapat menentukan hari dalam seminggu untuk sel yang berisi tanggal menggunakan format angka kustom. Sel yang menggunakan format berikut menampilkan hari dalam seminggu: DDDD. Perlu diingat bahwa sel memang berisi tanggal lengkap, bukan hanya nomor hari.

Menentukan tanggal hari Minggu terakhir

Rumus di bagian ini mengembalikan hari yang ditentukan terakhir. Anda dapat menggunakan rumus berikut untuk mendapatkan tanggal hari Minggu lalu. Jika hari ini adalah hari Minggu, maka rumus mengembalikan tanggal saat ini. Hasilnya akan nomor seri tanggal (Anda perlu memformat sel untuk menampilkan tanggal yang dapat dibaca): =TODAY()-RESUM(TODAY()-1;7) .

Untuk mengubah rumus ini untuk menemukan tanggal pada hari yang berbeda dari hari Minggu, ubah 1 menjadi angka antara 2 (Senin) dan 7 (Sabtu).

Menentukan hari dalam seminggu setelah kencan

Rumus berikut mengembalikan hari tertentu dalam seminggu setelah tanggal tertentu. Misalnya, Anda dapat menggunakan rumus ini untuk menentukan tanggal Jumat pertama setelah 4 Juli 2010. Rumus mengasumsikan bahwa sel A1 berisi tanggal dan sel A2 berisi angka antara 1 dan 7 (1 adalah Minggu, 2 adalah Senin, dan seterusnya): =A1+A2-WEEKDAY(A1)+(A2

Jika sel A1 berisi 4 Juli. 2010, dan sel A2 berisi b (yang merupakan singkatan dari Friday), rumus kembali pada 9 Juli 2010. Ini adalah hari Jumat pertama setelah 4 Juli 2010 (hari yang jatuh pada hari Minggu).

Menemukan hari spesifik ke-n dalam seminggu dalam sebulan

Anda mungkin memerlukan rumus untuk menemukan tanggal hari tertentu dalam seminggu. Misalkan hari gajian perusahaan Anda jatuh pada hari Jumat kedua setiap bulan dan Anda perlu menentukan ini
hari pembayaran untuk setiap bulan dalam setahun. Rumus berikut akan melakukan perhitungan yang diperlukan:
=DATE(A1,A2,1)+A3-WEEKDAY(DATE(A1,A2,1))+(A4-(A3>=WEEKDAY(DATE(A1,A2,1))))*7

Rumus ini mengasumsikan bahwa:

sel A1 berisi tahun;
sel A2 berisi bulan;
sel A3 berisi nomor hari (1 - Minggu, 2 - Senin, dst.);
sel A4 berisi angka, seperti 2, yang menunjukkan kemunculan kedua hari dalam seminggu yang ditentukan dalam sel A3.

Bila menggunakan rumus ini untuk menentukan tanggal Jumat kedua bulan Juni 2010, hasilnya adalah 11 Juni 2010.

Menemukan hari terakhir bulan ini

Untuk menentukan tanggal yang sesuai dengan hari terakhir bulan itu, Anda dapat menggunakan fungsi TANGGAL. Namun, Anda perlu menambah bulan dengan 1 dan menetapkan 0 sebagai nilai hari Dengan kata lain, hari "0" bulan berikutnya adalah hari terakhir bulan ini.

Rumus berikut mengasumsikan bahwa tanggal disimpan di sel A1. Rumus mengembalikan tanggal yang sesuai dengan hari terakhir bulan itu: =DATE(YEAR(A1);MONTH(A1)+1;0) .

Anda dapat mengubah rumus ini untuk menentukan jumlah hari dalam satu bulan. Rumus berikut mengembalikan bilangan bulat yang sesuai dengan jumlah hari dalam bulan untuk tanggal di sel A1 (pastikan Anda memformat sel sebagai angka dan bukan sebagai tanggal): =DAY(DATE(YEAR(A1),MONTH (A1)+1; 0))

Menentukan Kuartal Tanggal

Untuk laporan keuangan, mungkin berguna untuk menyajikan informasi per kuartal. Rumus berikut mengembalikan bilangan bulat dari 1 hingga 4 yang sesuai dengan kuartal kalender untuk tanggal di sel A1: =ROUNDUP(MONTH(A1)/3,0) . Rumus ini membagi angka bulan dengan 3 dan kemudian membulatkan hasilnya.

Fungsi DAY mengembalikan hari yang sesuai dengan tanggal tertentu. Hasil dari fungsi tersebut adalah bilangan dari rentang 1 hingga 31.

Fungsi WEEKDAY mengembalikan hari dalam seminggu yang sesuai dengan tanggal tertentu. Hasil dari fungsi ini adalah angka dari rentang 1 hingga 7 (satu untuk hari Minggu, dan tujuh untuk hari Sabtu).

Fungsi DAYS mengembalikan nilai numerik yang mewakili perbedaan hari antara dua tanggal yang diberikan.

Fitur perekaman sintaksis fungsi DAY, DAYWEEK dan DAYS di Excel

Fungsi DAY memiliki sintaks berikut:

Hari tanggal)

Dibutuhkan sebagai input parameter tanggal tunggal yang sesuai dengan tanggal hari yang ingin Anda tentukan.

Catatan:

Parameter input biasanya sel dari tabel excel A berisi data dalam format tanggal.
Juga, hasil perhitungan fungsi DATE dan fungsi lain yang mengembalikan nilai dalam format Tanggal diterima sebagai input.
Saat memasukkan data secara langsung di antara tanda kurung fungsi (misalnya, =DAY("24/06/2018")), tanda kutip harus digunakan, dengan demikian menunjukkan bahwa data yang dikirimkan ke input bertipe Teks. Selanjutnya, Excel secara otomatis mengonversi data ke format Tanggal.
Kesalahan #VALUE! Ini akan dihasilkan jika data dari data yang tidak didukung diteruskan ke input fungsi DAY. tipe excel, misalnya: DAY("24 Juni 2018") atau DAY("24 06 2018").
Parameter tanggal dapat direpresentasikan sebagai angka dalam kode waktu Excel.

Fungsi WEEKDAY memiliki notasi sintaks berikut:

WEEKDAY(tanggal, [jenis])

Deskripsi argumen fungsi:

Tanggal adalah parameter wajib yang sesuai dengan tanggal yang hari dalam seminggu ingin Anda tentukan. Parameter ini memiliki fitur yang dijelaskan dalam catatan untuk fungsi sebelumnya.
Ketik - angka dalam kisaran dari 1 hingga 3, yang menunjukkan hari mana dalam seminggu yang dianggap sebagai yang pertama (di beberapa negara hari pertama dalam seminggu adalah hari Minggu, di negara kita adalah Senin). Jika type=1, hari pertama dalam seminggu adalah hari Minggu, type=2 - Senin. Jika tipe=3, hasil dari fungsi WEEKDAY akan berupa angka dalam rentang 0 hingga 6, di mana 0 sesuai dengan Senin.

Catatan: dalam beberapa kasus, alih-alih angka 1,…7, output dari nama hari dalam seminggu (Senin, Selasa…Minggu) diperlukan. Untuk melakukan ini, Anda dapat membuat tabel seperti ini:

Dan kemudian menampilkan nilai teks yang sesuai dengan jumlah hari dalam seminggu:

Namun, fungsi berikut dapat digunakan untuk tugas ini: =TEXT(A2;"dddd").

Hasil dari fungsi ini adalah teks "Minggu". Jika catatan singkat hari dalam seminggu (Sab, Sen, Rab) diperlukan, masukkan "dddd" sebagai parameter kedua.

Fungsi DAYS digunakan untuk menghitung jumlah hari antara dua tanggal yang ditentukan dan memiliki sintaks berikut: =DAYS(tanggal_akhir, tanggal_mulai).

Deskripsi argumen fungsi ini:

End_date adalah parameter wajib yang mencirikan tanggal akhir suatu peristiwa.
Start_date adalah parameter wajib yang mencirikan tanggal mulai dari peristiwa tertentu untuk melakukan perhitungan.

Catatan:

Saat menghitung jumlah hari di antara tanggal, data dikonversi ke angka dalam kode waktu Excel. Ini berarti bahwa entri “=DAYS(“06/24/2018”;”06/13/2018”)” dan “=DATEVALUE(“06/24/2018”)-DATEVALUE(“06/13/2018”) ” akan mengembalikan nilai yang sama.
Jika salah satu parameter fungsi mewakili tanggal yang ditulis sebagai teks, maka itu akan diproses oleh fungsi DATEVALUE, yang akan mengembalikan tanggal integer.
Jika parameter fungsi adalah angka yang nilainya di luar batas yang diizinkan dalam kode waktu Excel, kesalahan #VALUE! akan dihasilkan.
Jika fungsi DAYS menggunakan parameter data yang tidak dapat direpresentasikan sebagai tanggal di Excel, kesalahan #VALUE! akan ditampilkan.

Contoh perhitungan di Excel menggunakan fungsi DAY, DAYWEEK dan DAYS

Tabel berisi beberapa tanggal yang ditulis dalam format tanggal excel di mana karyawan dari beberapa perusahaan harus dibayar upah. Sehubungan dengan perubahan undang-undang yang diadopsi, perhitungan gaji karyawan harus dilakukan pada hari pertama setiap bulan. Perlu untuk mengoreksi tanggal dari 15/07/2018 menjadi 01/07/2018. Bagian dari tabel terlihat seperti ini:

Untuk menyelesaikannya, gunakan rumus berikut: =B3-DAY(B3)+1.

Isi kolom lainnya dengan cara yang sama. Hasilnya, kami mendapatkan kolom baru dengan tanggal penggajian yang benar:

Penentuan hari kerja dan akhir pekan menggunakan rumus WEEKDAY

Contoh 2 Perangkat lunak membaca data dari spreadsheet Excel dan menggunakannya dalam perhitungannya. Salah satu sel berisi data dalam format Tanggal yang sesuai dengan tanggal paket diterima. Penting untuk menentukan apakah tanggal yang ditentukan sesuai dengan hari kerja dalam seminggu (dari Senin hingga Jumat) dan mengembalikan nilai boolean "benar" atau "salah" (jika hari itu hari libur) untuk diproses lebih lanjut oleh program.

Tabel aslinya terlihat seperti ini:

Untuk menentukan hari dalam seminggu, kita akan menggunakan WEEKDAY, argumen pertama adalah data dari sel "Tanggal kedatangan", dan argumen kedua adalah 2 (jumlah hari dalam seminggu dimulai dari satu, yang sesuai dengan Senin). Kami menggunakan fungsi berikut untuk menyelesaikan:

Seperti yang Anda lihat, semua tanggal adalah hari kerja dalam seminggu.

Mengapa Anda memerlukan fungsi DAYS di Excel?

Contoh 3. Diketahui bahwa Perang Dunia Kedua dimulai pada tanggal 22 Juni 1941 dan berakhir pada tanggal 9 Mei 1945. Penting untuk menentukan berapa hari permusuhan terjadi.

Mari kita masukkan data awal dalam tabel:

Untuk perhitungannya, kami menggunakan rumus berikut: =DAYS(B3;A3).

Kami mendapatkan hasil berikut:

Artinya, permusuhan berlangsung selama 1417 hari.

Catatan! Setiap tanggal di Excel adalah jumlah hari yang telah berlalu sejak tanggal 01/01/1900. Ini dijelaskan secara lebih rinci dalam artikel: Bagaimana cara menghitung tanggal di Excel? Oleh karena itu, Anda dapat dengan bebas menggunakan rumus sederhana ini tanpa fungsi: =B3-A3.

Seperti yang Anda lihat pada gambar, hasilnya serupa. Padahal, fungsi DAYS di Excel tidak diperlukan sama sekali.

Saat bekerja di program excel terkadang tugas diatur sehingga setelah memasukkan tanggal tertentu dalam sel, hari dalam seminggu yang terkait dengannya ditampilkan. Secara alami, untuk menyelesaikan masalah ini dengan cara yang begitu kuat prosesor spreadsheet, seperti Excel, mungkin dalam beberapa cara. Mari kita lihat opsi apa yang ada untuk melakukan operasi ini.

Ada beberapa cara untuk menampilkan hari dalam seminggu untuk tanggal yang dimasukkan, mulai dari pemformatan sel hingga penerapan fungsi. Mari kita lihat semua opsi yang ada untuk melakukan operasi ini di Excel sehingga pengguna dapat memilih yang terbaik untuk situasi tertentu.

Metode 1: Terapkan pemformatan

Pertama-tama, mari kita lihat bagaimana, menggunakan pemformatan sel, Anda dapat menampilkan hari dalam seminggu untuk tanggal yang dimasukkan. Opsi ini melibatkan mengonversi tanggal ke nilai yang ditentukan, daripada mempertahankan kedua tipe data ini ditampilkan di lembar kerja.

di lapangan "Jenis" memformat windows alih-alih nilai "DDDD" Anda juga dapat memasukkan ekspresi:

Dalam hal ini, nama singkat dari hari dalam seminggu akan ditampilkan pada lembar.

Metode 2: Menggunakan Fungsi TEXT

Tetapi metode yang disajikan di atas melibatkan pengubahan tanggal menjadi hari dalam seminggu. Apakah ada opsi untuk menampilkan kedua nilai ini di lembar? Artinya, jika kita memasukkan tanggal di satu sel, maka hari dalam seminggu harus ditampilkan di sel lain. Ya, ada opsi seperti itu. Itu bisa dilakukan dengan menggunakan rumus TEKS. Dalam hal ini, nilai yang kita butuhkan akan ditampilkan dalam sel yang ditentukan dalam format teks.

Selain itu, jika Anda mengubah nilai tanggal dalam sel, hari dalam seminggu akan berubah secara otomatis. Jadi, dengan mengubah tanggal, Anda dapat mengetahui hari apa dalam seminggu itu akan jatuh.

Metode 3: Menggunakan fungsi WEEKDAY

Ada operator lain yang dapat menampilkan hari dalam seminggu untuk tanggal tertentu. Ini adalah fungsi DAYWEEK. Benar, ini tidak menampilkan nama hari dalam seminggu, tetapi nomornya. Dalam hal ini, pengguna dapat mengatur dari hari mana (dari Minggu atau Senin) penomoran akan dihitung.

Seperti fungsi sebelumnya, mengubah tanggal secara otomatis mengubah jumlah hari dalam seminggu di sel tempat operator diatur.

Seperti yang Anda lihat, di Excel ada tiga opsi utama untuk mewakili tanggal sebagai hari dalam seminggu. Semuanya relatif sederhana dan tidak mengharuskan pengguna untuk memiliki keterampilan khusus. Salah satunya adalah menggunakan format khusus, dan dua lainnya menggunakan fungsi bawaan untuk mencapai tujuan ini. Mengingat bahwa mekanisme dan metode menampilkan data dalam setiap kasus yang dijelaskan berbeda secara signifikan, pengguna harus memilih opsi mana yang ditunjukkan dalam situasi tertentu yang paling cocok untuknya.

Dalam pelajaran ini, Anda akan mempelajari berbagai rumus yang berguna untuk menambah dan mengurangi tanggal di Excel. Misalnya, Anda akan belajar cara mengurangi tanggal lain dari satu tanggal, cara menambahkan beberapa hari, bulan, atau tahun ke tanggal, dan seterusnya.

Jika Anda telah mengambil pelajaran tentang bekerja dengan tanggal di Excel (pelajaran kita atau pelajaran lainnya), maka Anda harus mengetahui rumus untuk menghitung satuan waktu, seperti hari, minggu, bulan, tahun.

Saat menganalisis tanggal dalam data apa pun, Anda sering kali perlu melakukan operasi aritmatika pada tanggal ini. Artikel ini akan menjelaskan beberapa rumus untuk menambah dan mengurangi tanggal yang mungkin berguna bagi Anda.

Cara mengurangi tanggal di Excel

Mari kita asumsikan itu di sel Anda A2 dan B2 berisi tanggal, dan Anda perlu mengurangi satu tanggal dari yang lain untuk mengetahui berapa hari ada di antara mereka. Seperti yang sering terjadi di Excel, hasil ini dapat diperoleh dengan beberapa cara.

Contoh 1: Langsung mengurangkan satu tanggal dari tanggal lainnya

Saya pikir Anda tahu bahwa Excel menyimpan tanggal sebagai bilangan bulat mulai dari 1, yaitu 1 Januari 1900. Jadi, Anda cukup mengurangi satu angka dari angka lain secara aritmatika:

Contoh 2. Pengurangan tanggal menggunakan fungsi RAZDAT

Jika rumus sebelumnya tampak terlalu sederhana untuk Anda, hasil yang sama dapat diperoleh dengan cara yang lebih canggih menggunakan fungsi RAZDAT(DATEDIF).

RAZNDAT(A2,B2,"d")
=DATEDIF(A2,B2,"d")

Gambar berikut menunjukkan bahwa kedua rumus mengembalikan hasil yang sama, kecuali untuk baris 4, di mana fungsi RAZDAT(DATEDIF) mengembalikan kesalahan #NOMOR!(#NUM!). Mari kita lihat mengapa ini terjadi.

Saat Anda mengurangi tanggal yang lebih baru (6 Mei 2015) dari tanggal yang lebih awal (1 Mei 2015), operasi pengurangan mengembalikan angka negatif. Namun, sintaks fungsi RAZDAT(DATEDIF) tidak mengizinkan mulai tanggal lebih tanggal akhir dan, tentu saja, mengembalikan kesalahan.

Contoh 3: Kurangi tanggal dari tanggal saat ini

Untuk mengurangi tanggal tertentu dari tanggal saat ini, Anda bisa menggunakan salah satu rumus yang dijelaskan sebelumnya. Cukup gunakan fungsinya alih-alih tanggal hari ini HARI INI(HARI INI):

HARI INI ()-A2
=HARI INI()-A2

RAZDAT(A2;HARI INI();"d")
=DATEDIF(A2,HARI INI(),"d")

Seperti pada contoh sebelumnya, rumus berfungsi dengan baik saat tanggal saat ini lebih besar dari tanggal yang dikurangi. Jika tidak, fungsi RAZDAT(DATEDIF) mengembalikan kesalahan.

Contoh 4: Pengurangan tanggal menggunakan fungsi DATE

Jika Anda lebih suka memasukkan tanggal langsung ke dalam rumus, gunakan fungsi TANGGAL(TANGGAL) lalu kurangi satu tanggal dari tanggal lainnya.

Fungsi TANGGAL memiliki sintaks berikut: TANGGAL( tahun; bulan; hari) .

Misalnya, rumus berikut mengurangkan 15 Mei 2015 dari 20 Mei 2015 dan mengembalikan selisihnya - 5 hari.

TANGGAL(2015;5;20)-TANGGAL(2015;5;15)
=DATE(2015,5,20)-DATE(2015,5,15)

Jika diperlukan hitung jumlah bulan atau tahun antara dua tanggal, maka fungsi RAZDAT(DATEDIF) - satu-satunya Kemungkinan Solusi. Dalam kelanjutan artikel, Anda akan menemukan beberapa contoh rumus yang mengungkapkan fungsi ini secara rinci.

Sekarang setelah Anda mengetahui cara mengurangi satu tanggal dari tanggal lainnya, mari kita lihat bagaimana Anda dapat menambah atau mengurangi jumlah hari, bulan, atau tahun tertentu dari sebuah tanggal. Untuk ini, ada beberapa fungsi excel. Yang mana yang harus dipilih tergantung pada unit waktu mana yang ingin Anda tambahkan atau kurangi.

Bagaimana cara menambahkan (mengurangi) hari ke tanggal di Excel

Jika Anda memiliki tanggal dalam sel atau daftar tanggal dalam kolom, Anda dapat menambahkan (atau mengurangi) jumlah hari tertentu menggunakan operasi aritmatika yang sesuai.

Contoh 1: Tambahkan hari ke tanggal di Excel

Rumus umum untuk menambahkan jumlah hari tertentu ke tanggal terlihat seperti ini:

= tanggal + N hari

Tanggal dapat diatur dengan beberapa cara:

Referensi sel:
Memanggil fungsi TANGGAL(TANGGAL):
TANGGAL(2015;5;6)+10
=TANGGAL(2015,5,6)+10
Memanggil fungsi lain. Misalnya, untuk menambahkan beberapa hari ke tanggal saat ini, gunakan fungsi HARI INI(HARI INI):
HARI INI()+10
=HARI INI()+10

Gambar berikut menunjukkan efek dari formula ini. Pada saat penulisan, tanggal saat ini adalah 6 Mei 2015.

Catatan: Hasil dari rumus ini adalah bilangan bulat yang mewakili tanggal. Untuk menampilkannya sebagai tanggal, Anda harus memilih sel (atau sel) dan klik ctrl+1. Sebuah kotak dialog akan terbuka Format Sel(Memformat Sel). Di tab Nomor(Nomor) dalam daftar format angka Pilih tanggal(Tanggal) lalu tentukan format yang Anda butuhkan. Lagi Detil Deskripsi Anda akan menemukan di artikel.

Contoh 2: Kurangi hari dari tanggal di Excel

Untuk mengurangi jumlah hari tertentu dari tanggal, Anda perlu menggunakan operasi aritmatika normal lagi. Satu-satunya perbedaan dari contoh sebelumnya adalah minus, bukan plus

= tanggal - N hari

Berikut ini beberapa contoh rumus:

A2-10
=TANGGAL(2015,5,6)-10
=HARI INI()-10

Bagaimana menambahkan (mengurangi) beberapa minggu ke tanggal

Saat Anda perlu menambahkan (mengurangi) beberapa minggu ke tanggal tertentu, Anda dapat menggunakan rumus yang sama seperti sebelumnya. Anda hanya perlu mengalikan jumlah minggu dengan 7:

Tambahkan N minggu sampai saat ini di excel:
A2+ N minggu * 7
Misalnya, untuk menambahkan 3 minggu ke tanggal di sel A2, gunakan rumus berikut:
Kurangi N minggu dari tanggal di excel:
A2 - N minggu * 7
Untuk mengurangi 2 minggu dari tanggal hari ini, gunakan rumus ini:

HARI INI()-2*7
=HARI INI()-2*7

Bagaimana menambahkan (mengurangi) beberapa bulan ke tanggal di Excel

Untuk menambah (atau mengurangi) beberapa bulan ke suatu tanggal, Anda perlu menggunakan fungsi TANGGAL(TANGGAL) atau DATA(EDATE) seperti yang ditunjukkan di bawah ini.

Contoh 1: Menambahkan beberapa bulan ke tanggal menggunakan fungsi DATE

Jika daftar tanggal, misalnya, dalam kolom SEBUAH, tunjukkan jumlah bulan yang ingin Anda tambahkan (angka positif) atau kurangi (angka negatif) di beberapa sel, katakanlah, di C2.

Ketik di sel B2 rumus di bawah ini, klik di sudut sel yang disorot dan seret ke bawah kolom dengan mouse B ke sel yang diisi terakhir di kolom SEBUAH. Rumus dari sel B2 akan disalin ke semua sel di kolom B.

TANGGAL(TAHUN(A2),BULAN(A2)+$C$2,HARI(A2))
=TANGGAL(TAHUN(A2),BULAN(A2)+$C$2,HARI(A2))

Mari kita lihat apa yang dilakukan rumus ini. Logika rumusnya jelas dan jelas. Fungsi TANGGAL( tahun; bulan; hari) menerima argumen berikut:

Tahun dari tanggal di sel A2;
Bulan dari tanggal di sel A2+ jumlah bulan yang ditentukan dalam sel C2;
Hari dari tanggal di sel A2;

Semuanya sederhana! Jika Anda masuk ke C2 angka negatif, rumusnya akan mengurangi bulan, bukan menambah.

Secara alami, tidak ada yang mencegah Anda memasukkan minus langsung ke dalam rumus untuk mengurangi bulan:

TANGGAL(TAHUN(A2),BULAN(A2)-$C$2,HARI(A2))
=DATE(YEAR(A2),MONTH(A2)-$C$2,DAY(A2))

Dan, tentu saja, Anda dapat menentukan jumlah bulan untuk menambah atau mengurangi secara langsung dalam rumus tanpa referensi sel. Rumus yang sudah selesai akan terlihat seperti ini:

tambahkan bulan saat ini:
TANGGAL(TAHUN(A2),BULAN(A2)+2,HARI(A2))
=TANGGAL(TAHUN(A2),BULAN(A2)+2,HARI(A2))
kurangi bulan dari tanggal:
TANGGAL(TAHUN(A2),BULAN(A2)-2,HARI(A2))
=TANGGAL(TAHUN(A2),BULAN(A2)-2,HARI(A2))

Contoh 2: Menambah atau mengurangi bulan dari tanggal menggunakan fungsi DATES

Excel menyediakan fungsi khusus, yang mengembalikan tanggal beberapa bulan yang lalu atau sebelum tanggal tertentu, adalah fungsi DATA(EDATE). Ini tersedia di versi terbaru Excel 2007, 2010, 2013 dan Excel baru 2016.

Menggunakan DATA(EDATE) Anda memberikan dua argumen berikut:

Mulai tanggal - tanggal dari mana jumlah bulan dihitung.
Bulan adalah jumlah bulan untuk menambah (angka positif) atau mengurangi (angka negatif).

Rumus ini akan memberikan hasil yang sama dengan rumus dengan fungsi TANGGAL(DATE) pada contoh sebelumnya:

Saat menggunakan fungsi DATA(EDATE) tanggal mulai dan jumlah bulan dapat ditentukan langsung dalam rumus. Tanggal dapat diatur menggunakan fungsi TANGGAL(DATE) atau sebagai hasil dari formula lain. Sebagai contoh:

Rumus ini menambahkan 10 bulan hingga 7 Mei 2015
TANGGAL(TANGGAL(2015,5,7),10)
=EDATE(DATE(2015,5,7),10)
Rumus ini mengurangi 10 bulan dari tanggal hari ini
TANGGAL(HARI INI();-10)
=EDATE(HARI INI(),-10)

Catatan: Fungsi DATA(EDATE) mengembalikan hanya bilangan bulat. Untuk menyatakannya sebagai tanggal, Anda harus menerapkan format tanggal ke sel. Cara melakukan ini ditunjukkan dalam artikel. Bagaimana mengubah format tanggal di Excel.

Bagaimana cara menambahkan (mengurangi) tahun ke tanggal di Excel

Menambahkan tahun ke tanggal di Excel sama dengan menambahkan bulan. Anda perlu menggunakan fungsi itu lagi TANGGAL(TANGGAL), tetapi kali ini Anda perlu menentukan jumlah tahun yang ingin Anda tambahkan:

TANGGAL(TAHUN( tanggal) + N tahun; BULAN( tanggal); HARI( tanggal))
= TANGGAL(TAHUN( tanggal) + N tahun, BULAN( tanggal), HARI( tanggal))

pada lembar Excel, rumus mungkin terlihat seperti ini:

Tambahkan 5 tahun ke tanggal yang ditentukan dalam sel A2:
TANGGAL(TAHUN(A2)+5,BULAN(A2),HARI(A2))
=TANGGAL(TAHUN(A2)+5,BULAN(A2),HARI(A2))
Kurangi 5 tahun dari tanggal yang ditentukan dalam sel A2:
TANGGAL(TAHUN(A2)-5,BULAN(A2),HARI(A2))
=TANGGAL(TAHUN(A2)-5,BULAN(A2),HARI(A2))

Untuk mendapatkan rumus umum, Anda dapat memasukkan jumlah tahun dalam sel, lalu merujuk ke sel tersebut dalam rumus. Angka positif akan menambah tahun pada tanggal, sedangkan angka negatif akan mengurangi.

Menambahkan (mengurangi) hari, bulan dan tahun ke tanggal

Jika Anda membaca dengan cermat dua contoh sebelumnya, maka saya pikir Anda dapat menebak bagaimana cara menambahkan (atau mengurangi) tahun, bulan, dan hari pada suatu tanggal sekaligus dalam satu rumus. Ya, dengan bantuan fungsi lama yang bagus TANGGAL(DATA)!

Untuk tambahan X tahun, Y bulan dan Z hari:
TANGGAL(TAHUN( tanggal) + X tahun; BULAN( tanggal) + Y bulan; HARI( tanggal) + Z hari)
= TANGGAL(TAHUN( tanggal) + X tahun, BULAN( tanggal) + Y bulan, HARI( tanggal) + Z hari)
Untuk pengurangan X tahun, Y bulan dan Z hari:
TANGGAL(TAHUN( tanggal) - X tahun; BULAN( tanggal) - Y bulan; HARI( tanggal) - Z hari)
= TANGGAL(TAHUN( tanggal) - X tahun, BULAN( tanggal) - Y bulan, HARI( tanggal) - Z hari)

Misalnya, rumus berikut menambahkan 2 tahun dan 3 bulan, dan mengurangi 15 hari dari tanggal dalam sel: A2:

TANGGAL(TAHUN(A2)+2,BULAN(A2)+3,HARI(A2)-15)
=TANGGAL(TAHUN(A2)+2,BULAN(A2)+3,HARI(A2)-15)

Untuk kolom tanggal kami, rumusnya terlihat seperti ini:

TANGGAL(TAHUN(A2)+$C$2;BULAN(A2)+$H$2;HARI(A2)+$E$2)
=DATE(YEAR(A2)+$C$2,MONTH(A2)+$D$2,DAY(A2)+$E$2)

Cara Menambah dan Mengurangi Waktu di Excel

Di Microsoft Excel, Anda dapat menambah dan mengurangi waktu menggunakan fungsi WAKTU(WAKTU). Ini memungkinkan Anda untuk memperlakukan satuan waktu (jam, menit, dan detik) dengan cara yang sama seperti tahun, bulan, dan hari dalam fungsi TANGGAL(TANGGAL).

tambahkan waktu di excel:
A2 + WAKTU( jam tangan; menit; detik)
= A2 + WAKTU( jam tangan, menit, detik)
kurangi waktu di excel:
A2 - WAKTU( jam tangan; menit; detik)
= A2 - WAKTU( jam tangan, menit, detik)
di mana A2 adalah sel dengan waktu yang akan diubah.