Loncat ke daftar isi utama

Bagaimana cara membulatkan tanggal ke hari kerja tertentu sebelumnya atau berikutnya di Excel?

doc-round-to-before-sunday-1
Terkadang, Anda mungkin perlu mendapatkan hari kerja tertentu sebelumnya atau berikutnya seperti hari Minggu berdasarkan tanggal seperti gambar kiri yang ditampilkan. Di sini tutorial ini menyediakan dua formula untuk menangani pekerjaan ini dengan mudah di Excel.
Tanggal bulat ke hari kerja tertentu berikutnya
Tanggal bulat ke hari kerja tertentu sebelumnya

panah gelembung kanan biru Tanggal bulat ke hari kerja tertentu berikutnya

Misalnya, di sini untuk mendapatkan hari Minggu berikutnya dari tanggal di kolom A

1. Pilih sel yang ingin Anda tempatkan pada hari Minggu berikutnya, lalu tempel atau masukkan rumus di bawah ini:

=IF(MOD(A2-1,7)>7,A2+7-MOD(A2-1,7)+7,A2+7-MOD(A2-1,7))

2. Kemudian tekan Enter kunci untuk mendapatkan Minggu pertama berikutnya, yang ditampilkan sebagai angka 5 digit, lalu seret isi otomatis ke bawah untuk mendapatkan semua hasil.

doc-round-to-before-sunday-1

3. Kemudian simpan sel rumus yang dipilih, tekan Ctrl + 1 tombol untuk menampilkan Format Cells dialog, lalu di bawah Jumlah tab, pilih Tanggal dan pilih satu jenis tanggal dari daftar yang tepat sesuai kebutuhan. Klik OK.

doc-round-to-before-sunday-1

Sekarang hasil rumus telah ditampilkan dalam format tanggal.

doc-round-to-before-sunday-1

Untuk mendapatkan hari kerja berikutnya, silakan gunakan rumus di bawah ini:

Hari kerja Rumus
Minggu =IF(MOD(A2-1,7)>7,A2+7-MOD(A2-1,7)+7,A2+7-MOD(A2-1,7))
Sabtu =IF(MOD(A2-1,7)>6,A2+6-MOD(A2-1,7)+7,A2+6-MOD(A2-1,7))
Jumat =IF(MOD(A2-1,7)>5,A2+5-MOD(A2-1,7)+7,A2+5-MOD(A2-1,7))
Kamis =IF(MOD(A2-1,7)>4,A2+4-MOD(A2-1,7)+7,A2+4-MOD(A2-1,7))
Rabu =IF(MOD(A1-1,7)>3,A1+3-MOD(A1-1,7)+7,A1+3-MOD(A1-1,7))
;Selasa =IF(MOD(A1-1,7)>2,A1+2-MOD(A1-1,7)+7,A1+2-MOD(A1-1,7))
Senin =IF(MOD(A1-1,7)>1,A1+1-MOD(A1-1,7)+7,A1+1-MOD(A1-1,7))

panah gelembung kanan biru Tanggal bulat ke hari kerja tertentu sebelumnya

Misalnya, di sini untuk mendapatkan hari Minggu sebelumnya dari tanggal di kolom A

1. Pilih sel yang ingin Anda tempatkan pada hari Minggu berikutnya, lalu tempel atau masukkan rumus di bawah ini:

=A2-HARI KERJA(A2,2)

2. Kemudian tekan Enter kunci untuk mendapatkan Minggu depan pertama, lalu seret isi otomatis ke bawah untuk mendapatkan semua hasil.

doc-round-to-before-sunday-1

Jika Anda ingin mengubah format tanggal, pilih sel rumus, tekan Ctrl + 1 tombol untuk menampilkan Format Cells dialog, lalu di bawah Jumlah tab, pilih Tanggal dan pilih satu jenis tanggal dari daftar yang tepat sesuai kebutuhan. Klik OK.

doc-round-to-before-sunday-1

Sekarang hasil rumus telah ditampilkan dalam format tanggal.

doc-round-to-before-sunday-1

Untuk mendapatkan hari kerja lainnya sebelumnya, silakan gunakan rumus di bawah ini:

Hari kerja Rumus
Minggu =A2-HARI KERJA(A2,2)
Sabtu =IF(WEEKDAY(A2,2)>6,A2-WEEKDAY(A2,1),A2-WEEKDAY(A2,2)-1)
Jumat =IF(WEEKDAY(A2,2)>5,A2-WEEKDAY(A2,2)+5,A2-WEEKDAY(A2,2)-2)
Kamis =IF(WEEKDAY(A2,2)>4,A2-WEEKDAY(A2,2)+4,A2-WEEKDAY(A2,2)-3)
Rabu =IF(WEEKDAY(A2,2)>3,A2-WEEKDAY(A2,2)+3,A2-WEEKDAY(A2,2)-4)
;Selasa =IF(WEEKDAY(A2,2)>2,A2-WEEKDAY(A2,2)+2,A2-WEEKDAY(A2,2)-5)
Senin =IF(WEEKDAY(A2,2)>1,A2-WEEKDAY(A2,2)+1,A2-WEEKDAY(A2,2)-6)

Pembantu Tanggal & Waktu yang Kuat
Grafik Pembantu Tanggal & Waktu fitur dari Kutools untuk Excel, mendukung dengan mudah menambah / mengurangi waktu tanggal, menghitung perbedaan antara dua tanggal, dan menghitung usia berdasarkan ulang tahun.  Klik untuk uji coba gratis!
doc-round-to-before-sunday-1
 
Kutools for Excel: dengan lebih dari 200 add-in Excel yang praktis, gratis untuk dicoba tanpa batasan.

Alat Produktivitas Kantor Terbaik

馃 Kutools AI Ajudan: Merevolusi analisis data berdasarkan: Eksekusi Cerdas   |  Hasilkan Kode  |  Buat Rumus Khusus  |  Analisis Data dan Hasilkan Grafik  |  Aktifkan Fungsi Kutools...
Fitur Populer: Temukan, Sorot, atau Identifikasi Duplikat   |  Hapus Baris Kosong   |  Gabungkan Kolom atau Sel tanpa Kehilangan Data   |   Putaran tanpa Formula ...
Pencarian Super: VLookup Beberapa Kriteria  |   VLookup Nilai Berganda  |   VLookup di Beberapa Lembar   |   Pencarian Fuzzy ....
Daftar Drop-down Lanjutan: Buat Daftar Drop Down dengan Cepat   |  Daftar Drop Down yang Bergantung   |  Multi-pilih Drop Down List ....
Manajer Kolom: Tambahkan Jumlah Kolom Tertentu  |  Pindahkan Kolom  |  Alihkan Status Visibilitas Kolom Tersembunyi  |  Bandingkan Rentang & Kolom ...
Fitur Unggulan: Fokus Kisi   |  Tampilan Desain   |   Bar Formula Besar   |  Manajer Buku Kerja & Lembar   |  Perpustakaan Sumberdaya (Teks otomatis)   |  Pemetik tanggal   |  Gabungkan Lembar Kerja   |  Enkripsi/Dekripsi Sel   |  Kirim Email berdasarkan Daftar   |  Filter Super   |   Filter Khusus (filter tebal/miring/coret...) ...
15 Perangkat Teratas:  12 Teks Tools (Tambahkan Teks, Hapus Karakter, ...)   |   50 + Grafik jenis (Gantt Chart, ...)   |   40+ Praktis Rumus (Hitung usia berdasarkan ulang tahun, ...)   |   19 Insersi Tools (Masukkan Kode QR, Sisipkan Gambar dari Jalur, ...)   |   12 Konversi Tools (Angka ke Kata, Konversi Mata Uang, ...)   |   7 Gabungkan & Pisahkan Tools (Lanjutan Gabungkan Baris, Pisahkan Sel, ...)   |   ... dan banyak lagi

Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu.  Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...

Deskripsi Produk

Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah

  • Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
  • Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
  • Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
Comments (16)
No ratings yet. Be the first to rate!
Mor
Mor
This comment was minimized by the moderator on the site
I have tested this but partially incorrect result was out. Why ?
I wanted to get next Friday.

=IF(A2="","",A2+5-Mod(A2-1,7))

I have entered 11/19/2022
The result was past day 11/18/2022
Reply
0
0
Sunny
Sunny     Mor
This comment was minimized by the moderator on the site
Thanks for your reminder, the original formula is not enough rigorous indeed. I have updated the formulas and rewrited the tutorial, hope the new formulas can help you.
Reply
Report
0
0
James
James
This comment was minimized by the moderator on the site
Im trying to work out how to use a date, less an amount of days but ensure when subtracting the days it lands on a weekday not a weekend.
Any formulas??
Reply
0
0
Bellynne
Bellynne
This comment was minimized by the moderator on the site
Hi Everyone,
I use the formula below to round to the next Thursday where A1 is the cell with your original date and 14 is for Thursday. You can use other numbers for other days if required. 11=Monday, 12=Tuesday, 13=Wednesday, etc...

Also, you can use a 1 instead of an 8 to get the Thursday before your original date in A1. If your original date is already a Thursday the formula will produce the same date as in A1.

I hope this helps!

=A1+(0-WEEKDAY(A1,14)+8)
Reply
Report
0
0
Erik
Erik     Bellynne
This comment was minimized by the moderator on the site
Thank you! This worked :)
Reply
0
0
kisho_H
kisho_H
This comment was minimized by the moderator on the site
I put this into google sheets and to make the dates become next thursday but I have not been able to get it to work especially for the dates right after thursdays. =IF(L2="","",L2+4-MOD(L2-1,4)) is the function i used to make it thursday but not all dates were thursday.
Reply
Report
0
0
kisho_H
kisho_H     kisho_H
This comment was minimized by the moderator on the site
to clarify, all dates become thursday, but friday and saturday will not convert to the next weeks thursday instead they will go back a day or two to the previous thursday instead going to next weeks thursday, is there any way to fix this in the function =IF(L4="","",L4+4-MOD(L4-1,7))
Reply
Report
0
0
Bellynne
Bellynne     kisho_H
This comment was minimized by the moderator on the site
Hi Kisho,

I use the formula below to round to the next Thursday where A1 is the cell with your original date and 14 is for Thursday. You can use other numbers for other days if required. 11=Monday, 12=Tuesday, 13=Wednesday, etc...

Also, you can use a 1 instead of an 8 to get the Thursday before your original date in A1. If your original date is already a Thursday the formula will produce the same date as in A1.

I hope this helps!

=A1+(0-WEEKDAY(A1,14)+8)
Reply
Report
0
0
Miki
Miki     kisho_H
This comment was minimized by the moderator on the site
Hi Kisho,
I use the formula below to round to the next Thursday where A1 is the cell with your original date and 14 is for Thursday. You can use other numbers for other days if required. 11=Monday, 12=Tuesday, 13=Wednesday, etc...
Also, you can use a 1 instead of an 8 to get the Thursday before your original date in A1. If your original date is already a Thursday the formula will produce the same date as in A1.
I hope this helps!
=A1+(0-WEEKDAY(A1,14)+8)
Reply
0
0
Kisho
Kisho
This comment was minimized by the moderator on the site
I put this into google sheets and to make the dates become next thursday but I have not been able to get it to work especially for the dates right after thursdays. =IF(L2="","",L2+4-MOD(L2-1,4)) is the function i used to make it thursday but not all dates were thursday.
Reply
0
0
Lilian
Lilian
This comment was minimized by the moderator on the site
Si quiero la fecha del pr贸ximo viernes c贸mo debo modificar las variables? Gracias
Reply
0
0
Sunny
Sunny     Lilian
This comment was minimized by the moderator on the site
Sorry, I cannot understand, could you speak in English?
Reply
Report
0
0
Gerry Mc Guire
Gerry Mc Guire
This comment was minimized by the moderator on the site
hi guys,

I used the formula below, I hope it works for you.
IF(WEEKDAY(H2,16)<>7,(7-WEEKDAY(H2,16))+H2,H2) where H2 is the date that you want to round up to the next day of the week (in my case for Friday) and the 16 is the format to start the date with Saturday as #1 and Friday as #7.
Reply
0
0
Doug Ward
Doug Ward
This comment was minimized by the moderator on the site
Your round to previous Sunday is flawed. Even shows as much on your sheet.
Reply
0
0
Lisa Kazempour
Lisa Kazempour     Doug Ward
This comment was minimized by the moderator on the site
So, what would be the correct formula to first check to see if a cell has a date, else leave it blank and make the date the fall on a Friday before if the date falls on a Saturday or Sunday?
Reply
0
0
Lisa
Lisa     Lisa Kazempour
This comment was minimized by the moderator on the site
I found a workaround for to the previous Friday. You can change the switch "results" as needed to round to different days.=H3-SWITCH(TEXT(H3, "DDD"), "Mon", 3, "Tue", 4, "Wed", 5, "Thu", 6, "Fri", 0, "Sat", 1, "Sun", 2)
=[date]-SWITCH(TEXT([date], "DDD"), "Mon", 3, "Tue", 4, "Wed", 5, "Thu", 6, "Fri", 0, "Sat", 1, "Sun", 2)
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
脳
Rate this post:
Reset
0   Characters
Suggested Locations