| Referensi | : |
[XLS-PMG-01]: Schedule Proyek dengan Excel |
| Platform | : | Minimum Excel 2003 |
| Lokasi File | : | download |
Posting [XLS-PMG-01]: Schedule Proyek dengan Excel telah di uraikan langkah-langkah membuat schedule secara otomatis di excel dengan menggunakan fasilitas ‘conditional formating’. Pada posting tersebut prediksi tanggal akhir tiap aktivitas dihitung dengan rumus : [Tanggal Selesai]=[Tanggal Awal]+[Durasi]-1 . Jika menggunakan rumus ini, maka dianggap tidak ada hari libur mulai [Tanggal Awal] sampai [Tanggal Selesai].
Posting kali ini akan dibahas, bagaimana cara membuat schedule otomatis masih menggunakan ‘conditional formatting’ dengan mempertimbangakan hari libur hanya di libur nasional dan hari minggu. Metode atau formula excel yang baru untuk pembuatan manpower schedule dan estimasi biaya juga akan diuraikan lebih lanjut dalam posting ini.
Urutan pembuatan schedule proyek adalah:
- Estimasi [Tanggal Selesai] dengan Input [Tanggal Awal] dan [Durasi]
- Membuat Schedule Proyek (Gantt Chart)
- Memasukkan Rencana Manpower Loading ke Aktivitas
1. Estimasi [Tanggal Selesai] dengan Input [Tanggal Awal] dan [Durasi]
Salah satu fungsi di excel yang populer untuk menentukan tanggal akhir jika dimasukkan parameter durasi adalah WORKDAY(). Untuk pengguna Excel 2003, aktifkan dulu Addin ‘Analysis ToolPak’ sebelum menggunakan fungsi ini.
Bentuk baku rumus WORKDAY() adalah: = WORKDAY(start_date, days [,holidays]). Contoh penggunaan di spread sheet:
Rumus di [G2]=WORKDAY(F2,E2,$A$2:$A$15)
[F2] dan [E2] berturut-turut adalah start_date dan durasi. sedangkan $A$2:$A$15 adalah list hari libur nasional. Hasil dari rumus di atas adalah 28 Maret 2012 yaitu tanggal dimana memperhitungkan hari kerja mulai 26-28 Maret 2012 (3 hari kerja).
Jika diinginkan hari kerja sudah dimulai 22 Maret 2012 dengan libur di hari Hari Raya Nyepi (23 Maret 2012) dan Sabtu-Minggu (24-25 Maret 2012), maka rumus di [G3]=WORKDAY(F3,E3-1,$A$2:$A$15)
Secara default, fungsi WORKDAY() menganggap sabtu dan minggu adalah hari libur sehingga fungsi WORKDAY() tidak bisa dipakai seandainya hari sabtu adalah hari kerja. Salah satu solusi untuk mengatasi masalah tersebut adalah dengan membuat UDF (User Defined Function) menggunakan Visual Basic Application (VBA) atau macro seperti contoh yang aku temukan di internet A Better WORKDAY Function.
Kalo di A Better WORKDAY Function dibuat dengan VBA, maka saya memilih cara lain yaitu dengan menggunakan kombinasi beberapa fungsi menjadi array formula. Karena menggunakan array formula, saat selesai memasukkan atau mengetik formula diakhiri dengan menekan CRTL+SHIFT+ENTER.
Rumus di [G3] adalah:
=IF(E4=0,F4,SMALL(IF((WEEKDAY(ROW(INDIRECT(F4&":"&F4+30)),2)<7)*(COUNTIF($A$2:$A$15,ROW(INDIRECT(F4&":"&F4+30)))=0),ROW(INDIRECT(F4&":"&F4+30))),E4))
Jangan lupa akhiri penulisan atau saat copy rumus di atas dengan CRTL+SHIFT+ENTER. Hasil dari rumus di atas adalah 26 Maret 2012 yaitu tanggal yang sudah memperhitungkan hari sabtu sebagai hari kerja.
2. Membuat Schedule Proyek (Gantt Chart)
Sebelum membuat gant chart, dibuat terlebih dua tabel ‘hari libur nasional’ dan tabel ‘unit price’. Kedua tabel tersebut dibuat dengan fasilitas dynamic range menggunakan fungsi OFFSET() kemudian dynamic range tersebut disimpan dalam ‘named range’. Cara membuat named range telah beberapa kali di posting terdahulu. Misalnya di posting [XLS-PMG-02]: Rumus Terbilang di Excel (Tanpa Macro/VBA) atau di [XLS-MAP-006]: Menampilkan Hasil Koordinat GPS di Chart Excel dengan image dari Google Earth.
2.1. Membuat Tabel ‘Hari Libur Nasional’
Buat sheet baru misalnya [Holidays]
Buat Daftar hari libur misal seperti contoh di atas dimana kolom [A:A] berisi tanggal dan [B:B] adalah keterangan hari liburnya. Buat dynamic range sbb:
| Named range | Refers To | Keterangan rumus range |
| _HariLibur | =OFFSET($B$1,1,-1,COUNT($A:$A)) |
Membuat dynamic range dimulai dari 1 (satu) baris di bawah cell $B$1 dan 1 (satu) kolom di sebelah kiri cell $B$1 dengan tinggi (atau jumlah record) sesuai dengan data angka di kolom $A:$A. Hasil Dynamic Range := $A$2:$A$15, range berubah jika ada penambahan data di kolom $A:$A. |
2.2. Membuat Table ‘Resources dan Unit Rate’ nya
Contoh table resources dan unit rate dibuat seperti di bawah:
| Named range | Refers To | Keterangan rumus range |
| _Resource | =OFFSET($B$1,1,,COUNT($A:$A),6) |
Membuat dynamic range dimulai dari 1 (satu) baris di bawah cell $B$1 di kolom yang sama ($B$1) dengan tinggi (atau jumlah record) sesuai dengan data angka di kolom $A:$A dan lebar = 6 kolom. Hasil Dynamic Range:= $B$2:$G$4, range berubah jika ada penmbahan angka di kolom $A:$A |
| _ResID | =OFFSET(_Resource,,,,1) | Membuat dynamic range dari dynamic range _Resource dengan lebar kolom=1. Hasil dynamic range:= $B$2:$B$4, range berubah jika _resource berubah atau ada penambahan data di kolom $A:$A |
2.3. Membuat schedule
Berbeda dengan schedule yang pernah diuraikan di [XLS-PMG-01]: Schedule Proyek dengan Excel, schedule kali ini sudah dilengkapi dengan baris untuk monitoring progress yaitu baris informasi progress actual untuk masing-masing aktivitas.
Berikut snap shot schedule (gantt chart) yang akan dibuat:
| Range/Cells | Formula | Keterangan |
| [J4] | =–OR(WEEKDAY(J$5,2)>6,COUNTIF(_HariLibur,J$5)) | hasil formula bernilai 1 jika hari libur nasional dan hari minggu, selain hari tersebut bernilai 0 Copy rumus di [J4] sampai ke [AN4] |
| [F6] | =IF(D6=0,E6,SMALL(IF((WEEKDAY(ROW(INDIRECT(E6&":"&E6+30)),2)<7)*(COUNTIF(_HariLibur,ROW(INDIRECT(E6&":"&E6+30)))=0),ROW(INDIRECT(E6&":"&E6+30))),D6)) | Formula array untuk menentukan tanggal selesai aktivitas setelah dimasukkan durasi di kolom [D:D]. Copy rumus di [F6] sampai di [F28] di baris PLAN |
| [I6] | =IF(ISBLANK(A6),I5,A6) | membuat ID activity untuk baris PLAN dan ACTUAL |
| [J6] | =(MEDIAN($E6,J$5,$F6)=J$5)*(J$4=0) | hasil formula bernilai 1 jika hari kerja sesuai dengan jumlah hari kerja yang didefinikan di kolom durasi [D:D]. Copy rumus [J6] sampai [AN6:AN28] di baris |
| _Task | =OFFSET($A$5,1,,COUNT($A$6:$A$614)*2,2) | Dynamic Range untuk Task. Hasil dari dynamic range adalah [A6:B29]. Note: Isian di kolom ID Task harus berupa angka agar dynamic range bisa menghasilkan range yang benar !! |
| [E8] | =OFFSET($F$5, SUMPRODUCT((INDEX(_Task,,1)=$G8)*ROW(INDEX(_Task,,1)))-5,)+$H8 | Menghasilkan tanggal awal aktivitas dengan mengacu ke aktivitas sebelumnya, kolom [G], dan durasi lag (adjustment), kolom [H]. Jika kolom adjust bernilai 0 (nol), maka start aktivitas akan sama dengan akhir aktivitas sebelumnya.
Copy rumus di [E8] sampai [E28] di baris PLAN. Kolom [E] juga bisa dimasukkan tanggal secara manual (constrain starting date). contoh di [E20] |
2.4. Membuat conditional format di schedule (gantt chart)
Pilih cell [J5], kemudian atur rule conditional formating sebagai berikut:
| Rule Type | Formula | Format | Applies to | Keterangan |
| Formula | =($C5=$C$7)*(J$4=0)*(J5>0) |  |
=$J$5:$AN$29 | Arsir cell hari kerja sesuai durasi di baris ACTUAL |
| Formula | =J$4=1 |  |
=$J$5:$AN$29 | Arsir Hari Libur |
| Formula | =($C5=$C$6)*(J$4=0)*(J5>0) |  |
=$J$5:$AN$29 | Arsir cell hari kerja sesuai durasi di baris PLAN |
Jika dikehendakai, set tampilan worksheet agar angko 0 (nol) tidak ditampilkan dalam worksheet. Cara setting bisa dibaca di [XLS-PMG-01]: Schedule Proyek dengan Excel
3. Memasukkan Rencana Manpower Loading ke Aktivitas
Manpower loading dimasukkan dalam sheet tersendiri. Dalam sheet baru ini ditentukan resourse assignment sesuai dengan aktivitasnya kemudian dihitung manhour atau mandaysnya untuk masing-masing resource. Karena unitprice per days sudah ditentukan di sheet yang lain, maka rencana biaya juga bisa dihitung.
Berikut adalah spreadsheet untuk memasukkan manpower loading:
| Range/Cells | Formula | Keterangan |
| [B3] | =VLOOKUP(A3,_Task,2,0) | Menampilkan task description atau deskripsi pekerjaan sesuai dengan TaskID yang dimasukkan di [A3] |
| [D3] | =VLOOKUP($C3,_Resource,2,0) | Menampilkan nama resource, sesuai dengan ResourceID yang dimasukkan di cell [C3] |
| [E3] | =VLOOKUP($C3,_Resource,5,0) | menampilkan unit price, sesuai dengan ResourceID yang dimasukkan di cell [C3] |
| [F3] | =SUM(G3:AL3)*E3 | [Total price]= [total manhours] x [unit price/hour] |
Arsiran di mulai cell [G3] harus sama dengan schedule yang telah dibuat sebelumnya (tahapan 2.3), sedangkan untuk isian jam kerja dimasukkan manual di daerah yang berarsir. Seperti pada tahap 2.4, akan digunakan fasilitas conditional formating untuk otomatisasi arsiran.
Sebelum dilakukan proses conditional formating perlu didefinisikan beberapa dynamic range untuk menyamakan schedule dengan manpower assignment. Dynamin range yang dimaksud adalah:
| Named Range | ReferTo | Keterangan |
| _Calendar | =OFFSET(Schedule!$J$5,,,,COUNT(Schedule!$J$5:$ZZ$5)) | Dynamic range untuk tanggal di Schedule!$J$5:$ZZ$5 |
| _TaskID | =OFFSET(_Task,,,,1) | Mengambil kolom pertama dari range _Task |
| _GantChart | =OFFSET(_Calendar,1,,ROWS(_Task),) | mengambil atau mendefinisikan range gantt chart. hasil dari dynamic range adalah range Schedule!$J$6:$AN$29 |
Pilih cell [G3] kemudian setting condtional formating sebagai berikut:
| Rule Type | Formula | Format | Applies to | Keterangan |
| Formula | =SUMPRODUCT((_Calendar=G$2)*(OFFSET(_TaskID,,2)="PLAN")*(OFFSET(_TaskID,,8)=$A3)* _GantChart)=1 |  |
=$G$3:$AK$32 | Arsir cell hari kerja sesuai durasi di baris PLAN |
Lakukan pengisian rencana jam kerja, contoh di atas jam kerja dimasukkan 9jam/hari, di cell yang diarsir. Jika ingin berpindah resource assigment yang lain, masukkan TaskID di kolom A, kemudian ResourceID di kolom C.
Jika proses manpower loading/resource assignment selesai, dapat dihitung breakdown biaya per resource atau per activity seperti tabel di bawah.
Silahkan download file untuk dipelajari lebih lanjut.
===selamat mencoba==
coretan tentang autocad dan excel 
wooww keren..mantep fren…gw sich nggak ngerti….
cuma salut dengan rasa berbagi yang dalem…sehingga lahir tutorial ini..
Fren..skarang jarang aktif di milis belajar excel…lagi banyak proyek ya….??ajak2 donk..hehehe..
bukan banyak proyek, tetapi jadi kacung
Pak Saya ada kesulitan cara menjalankan perintah Autocad menggunakan Macro..
Saya sudah buat program ploting cross section dan Situasi.. Tapi masih melalui Script, maksud saya biar langsung dari excel langsung ngeplot ke Autocad tanpa Script bagaimana? bsa mnta alamat emailnya nanti saya kirim filenya.. trims
posting sama commentnya kok nggak nyambung ya….
plotting cross dari excel ke autocad, ada di
https://cadex.wordpress.com/2012/05/16/xls-svy-13-plotting-cross-section-dari-excel-ke-autocad-versi-1-0/
Mas, kalau ingin menghitung Estimasi [Tanggal dan Jam Selesai] dengan input [Tanggal dan Jam Awal] dan [Durasi dalam Menit/Jam] bagaimana caranya ?
Mohon bantuannya.
Terimakasih.
Misal A2, B2 dan C2 adalah tanggal, awal, jam awal dan durasi maka estimasi selesainya = A2+B2+C2
catatan
A2 : diisi dengan format tanggal,
B2 : diisi dengan format jam. Misal Jam 12 jam 35 menit, dimasukkan angka di cell tsb 12:35
C2: dimasukkan durasi dalam format jam, misal 3 jam 20 menit, maka di cell tersebut dimasukkan 3:30
D2: hasil tanggal dan jam estimasi. Atur format cell numbernya “d mmm yyyy h:mm” agar bisa ditampilkan tanggal dan jamnya
Mas..klo d jumlah langsung kyk gtu suka beda hasilnya. Misal mulai 3 maret 2015 pkl 7.10 am durasi 8 jam 0 mnit. Wktu kerja 7 jam.hasil akhir yg diinginkan 4 maret 2015 pkl 08.10. Gimana y mas rumus excelnya?
memang bisa beda jika dijumlah langsung dengan melibatkan set durasi melibatkan ketelitian sampai jam. Saya juga sering menemukan perbedaan ini walaupun saya menggunakan software microsoft project. Apalagi kalo project yang melibatkan team menggunakan setting jam kerja yang berbeda. Misal untuk onshore 8 jam/hari sedangkan offshore 24 jam/hari.
untuk kasus Mbak Nita,
Misal
[A3] adalah tanggal & jam mulai, masukkan di excel sbb = 3/3/15 7:10
[A1] satuan kerja perhari 7 jam/hari, masukkan di excel = 7
[B3] durasi dalam satuan jam:menit, masukkan di excel = 8:00
maka rumus akhir aktivitas adalah:
=A3+INT(HOUR(B3)/A1)+MOD(HOUR(B3),A1)/24
Note:
** ganti tanda koma (“,”) dengan titi koma (“;”) jika menggunaka setting excel indonesia
*** urutan input tanggal untuk setting indonesia adalah dd/mm/yy (tanggal/bulan/hari)
Mass.. Rumusny amat sangat membanu..sy awal2 nyoba bkin pusingg…makasih ya.. Tapi ada yg kelupaan finish datenya biar langsung otomatis kedeteksi hari lbur baik sabtu minggu atau ngambil dari data tabel holiday, gmana caranya…
misal [P3:P30] adalah list tanggal libur nasional,
=WORKDAY(A3,INT(HOUR(B3)/A1),P3:P30)+TIME(HOUR(A3),MINUTE(A3),0)+MOD(HOUR(B3),A1)/24
Pak setelah di cek lagi..rumusny berlaku kalau durasinya < 12 jam, tapi klo misal start date 2/17 pkl 10:10 am durasi 13 jam hasil akhirnya end date 2/18 pkl 4:10 am pak..padahal jam kerja cuma 7 jam saja.. Gimana y pak untuk solusinya..maap merepotkan lagi..
Have you ever considered writing an ebook or guest authoring onn other blogs?
Ihave a blog based upon on the same ideas you discuss and would love
to have you share some stories/information. I know
my visitors would value your work. If you’re even remotely interested, feel free tto shoot
mme an e mail.
what are the ideas i would share to your visitors?
Selamat pagi,
Pak mau tanya untuk pembuatan schedule yang rutin, misal per 2minggu caranya bagaimana?
terimakasih sebelumnya.
hanya untuk satu kegiatan atau untuk beberapa kegiatan?
Mas terima kasih banyak atas sharing, ada yang mau saya tanyakan:
Bila kita ingin menambah hari libur diluar libur nasional gimana ya mas tapi ga semua orang ambil libur itu
jadi misal si A start tgl 1 januari, durasi 10 hari workday, ditambah libur nasional, ditambah libur pribadi, nah kita mau tau dia selesai tgl brp?
mohon bantuannya mas terimkasih
Maaf. Saya belum sempat bereksperimen dengan kasus di atas. Tetapi bisa dicoba untuk masing-masing personel mempunyai range hari libur sendiri.
Maaf Mas sedikit koreksi cell:
[A4] adalah tanggal & jam mulai, contoh = 22/3/17 11:10
[C4] adalah durasi dalam satuan jam:menit, contoh = 08:00
[D4] adalah durasi dalam satuan menit. contoh = 00:30
[E4] adalah tanggal dan waktu selesai perkerjaan (yang ingin dicari)
Formula:
=WORKDAY(A4;INT(HOUR(C4)/11))+(TIME(HOUR(A4);MINUTE(A4);0)+TIME(0;MINUTE(D4);0))+MOD(HOUR(C4);11)/24
maaf belum sempat mencobanya
Mas, mohon penjelasannya untuk yang Rumus ini
=IF(D6=0,E6,SMALL(IF((WEEKDAY(ROW(INDIRECT(E6&”:”&E6+30)),2)<7)*(COUNTIF(_HariLibur,ROW(INDIRECT(E6&":"&E6+30)))=0),ROW(INDIRECT(E6&":"&E6+30))),D6))
bagaimana membuat rumus tersebut menjadikan hari minggu sebagai hari aktif, bukan hari libur ?
Terimakasih atas jawaban yang diberikan, semoga Ilmunya barokah dan sukses, aamiin…