coretan tentang autocad dan excel

March 18, 2012

[XLS-PMG-06]:Schedule Proyek dengan Setting Hari Libur dan Hari Kerja di Microsoft Excel

Filed under: Excel, Project Management — Tags: , — cadex @ 00:26
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:

  1. Estimasi [Tanggal Selesai] dengan Input [Tanggal Awal] dan [Durasi]
  2. Membuat Schedule Proyek (Gantt Chart)
  3. 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:

image

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:

image

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:

image

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) image =$J$5:$AN$29 Arsir cell hari kerja sesuai durasi di baris ACTUAL
Formula =J$4=1 image =$J$5:$AN$29 Arsir Hari Libur
Formula =($C5=$C$6)*(J$4=0)*(J5>0) image =$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:

image

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 image =$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.

image

Silahkan download file untuk dipelajari lebih lanjut.

===selamat mencoba==

17 Comments »

  1. 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..

    Comment by cor3dan — August 29, 2012 @ 18:54

    • bukan banyak proyek, tetapi jadi kacung

      Comment by cadex — September 1, 2012 @ 06:58

  2. 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

    Comment by wawan — October 3, 2012 @ 23:21

  3. 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.

    Comment by Amin — January 6, 2015 @ 12:42

    • 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

      Comment by cadex — January 18, 2015 @ 22:00

      • 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?

        Comment by nita — March 15, 2015 @ 11:52

      • 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)

        Comment by cadex — March 18, 2015 @ 12:27

      • 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…

        Comment by nita — March 18, 2015 @ 22:25

      • 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

        Comment by cadex — March 20, 2015 @ 09:04

  4. 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..

    Comment by nita — March 28, 2015 @ 20:06

  5. 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.

    Comment by chocolate — April 15, 2015 @ 14:14

    • what are the ideas i would share to your visitors?

      Comment by cadex — April 19, 2015 @ 21:09

  6. Selamat pagi,

    Pak mau tanya untuk pembuatan schedule yang rutin, misal per 2minggu caranya bagaimana?
    terimakasih sebelumnya.

    Comment by victor — April 13, 2016 @ 10:30

    • hanya untuk satu kegiatan atau untuk beberapa kegiatan?

      Comment by cadex — May 9, 2016 @ 17:47

  7. 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

    Comment by tommy — April 17, 2016 @ 23:11

    • Maaf. Saya belum sempat bereksperimen dengan kasus di atas. Tetapi bisa dicoba untuk masing-masing personel mempunyai range hari libur sendiri.

      Comment by cadex — May 9, 2016 @ 17:45


RSS feed for comments on this post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: