Hot Fox: Re: ]] XL-mania [[ Schedule pengiriman barang time based

sexta-feira, 30 de outubro de 2009

Re: ]] XL-mania [[ Schedule pengiriman barang time based

Setiap tanggal yg diJadwalkan (berdasarkan kolom Day dan Kolom Time) itu

sebenarnya dicari dengan fungsi WORKDAY; karena, tidak boleh jatuh pada

hari Minggu (kalau dikehendaki, bisa juga : Tidak boleh Jatuh pd hari Libur Resmi)

Sayangnya Fungsi WORKDAY meLIBURkan bukan hanya Minggu tetapi juga Sabtu.

Sedangkan Jadwal pak Zaki hanya meliburkan Hari Minggu (weekday = 1)

Maka itu harus dibuat sendiri Fungsi cWorkday yg BISA diatur Excluding Date-nya !!

lihat : http://tech.groups.yahoo.com/group/XL-mania/message/14025

Permasalahannya bertambah "agak rumit" karena Schedulled_Date dlm 1 bulan

harus dicari beberapa kali, sedangkan setiap mencari Schedulled_Date berikutnya,

fungsi cWorkday memerlukan Tgl Awal (yaitu Scedulled_Date yg terahir ditemukan.)

Dengan mengandalkan rumus murni saja, mungkin agak repot 'mengarang' formulanya..

Maka itu dibalik formula yg tertulis di sheets' cell: terdapat fungsi bantuan (UDF)

sebanyak 3 bijik : cWorkday, SchedDate dan SchedList

di sini diutipkan code prosedur fungsi : SchedDate dan SchedList

mudah-mudahan ada manfaatnya...

'-------------

Function SchedDate(TglBar As Range, Nhari As Integer, CurTgl As Date)
   ' siti Vi //tgl terjadwal dgn selang Nhari yg bukam Minggu
   '---------------------------------------------------------
   Dim i As Long, j As Long, bl As Integer, Schedu As Date
   Dim Txt As String, Tgl As String, cel As Range
   For Each cel In TglBar
      If Not Weekday(cel.Value) = 1 Then
         j = j + 1
         If j = 1 Then
            Txt = Txt & "\" & Day(cel)
            Schedu = cel.Value
         End If
         Exit For
      End If
   Next
   Txt = Txt & SchedList(Schedu, Nhari)
   Tgl = "\" & Day(CurTgl) & "\"
   SchedDate = IIf(InStr(1, Txt, Tgl) = 0, False, True)

End Function

Private Function SchedList(Sched1 As Date, ByVal Nday As Long)
   ' siti Vi / 30 okt 09
   ' daftar HariTerjadwal (string)
   '--------------------------------
   Dim i As Integer, ScList As String, Sched2 As Date
   Sched2 = Sched1
   ScList = "\" & Day(Sched1)
   For i = 1 To (31 / Nday)
      Sched2 = cWORKDAY(Sched2, Nday, 1)
      If Month(Sched2) <> Month(Sched1) Then Exit For
      ScList = ScList & "\" & Day(Sched2)
   Next
   SchedList = ScList & "\"
End Function

'-------------------

----- Original Message -----
From: Zaki
To: XL-mania@yahoogroups.com
Sent: Thursday, October 29, 2009 03:48PM
Subject: ]] XL-mania [[ Schedule pengiriman barang time based

Dear para XL-Mania,
Adakah formula untuk membuat schedule pengiriman based on time?,
contoh data terlampir.

Thanks,
regards

.:: zaki ::.

__._,_.___

Messages in this topic (5) Reply (via web post) | Start a new topic

Messages

+-:: XL-mania ::::::::::::::::::::----------------------------------+
| Moderasi akan lambat minggu ini, momods pergi ke tempat jauh      |
| http://www.facebook.com/group.php?gid=37671048001&ref=mf          |
+-------------------------------------------------------------------+
| DILARANG : MLM, money game, OOT, iklan tanpa izin, SARA, testing, |
| pembicaraan pribadi, one line message, melecehkan, tidak sopan. |
+-------------------------------------------------------------------+
| Buat subjek yang kreatif, jangan : "tanya", "help", "mohon bantu" |
| Usahakan besar attachment < 200 kb. Gunakan winzip jika perlu. |
+-------------------------------------------------------------------+
| Ajak teman-teman Anda bergabung dengan mengirim e-mail kosong ke |
| XL-mania-subscribe@yahoogroups.com atau kirimkan mereka file dari |
| http://groups.yahoo.com/group/XL-mania/files/Promotion/           |
+-------------------------------------------------------------------+
| Berikan testimoni di : http://www.friendster.com/xlmania atau... |
| http://www.xl-mania.com/2008/06/testimoni-xl-mania.html           |
+-------------------------------------------------------------------+
| Message lama ada di :                                             |
| http://groups.yahoo.com/group/XL-mania/messages [perlu yahoo id] |
| http://www.mail-archive.com/xl-mania@yahoogroups.com              |
+-------------------------------------------------------------------+

MARKETPLACE

Mom Power: Discover the community of moms doing more for their families, for the world and for each other