Senin, 17 November 2014

[belajar-excel] Digest Number 3188

15 Messages

Digest #3188
1a
Re: Meriew Data by "Heri Pamungkas ( Gmail )" dchoosen88
1b
Re: Meriew Data by "Samsudin"
2a
Re: Merubah Text date by "Bagus" bagus4bls
2b
2c
Re: Merubah Text date by "Subroto, Matzen"
2d
Re: Merubah Text date by "Mr. Kid" nmkid.family@ymail.com
6a
Re: Kategori Umur by "Mr. Kid" nmkid.family@ymail.com
7a

Messages

Sun Nov 16, 2014 9:59 pm (PST) . Posted by:

"Heri Pamungkas ( Gmail )" dchoosen88


السَّلَامُ عَلَيْكُمْ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ
Semangat pagi,.

Coba menggunakan Formula Eomonth.

وَالسَّلَامُ عَلَيْكُمْ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ

Warm Regards,

On 17/11/2014 12:19, Samsudin samsudin@bayan.com.sg [belajar-excel] wrote:
>
> Assalamu'alaikum,
>
> Mr. Bagus,
>
> Terima kasih banyak atas pencerahannya dan ada lagi yang ingin saya
> tanyakan bagaimana caranya cell yang berisi tanggal (sembarang
> tanggal) dan cell lain ingin menghasilkan awal atau tanggal awal dari
> cell tersebut,
>
> Misal cell A1 = 23/1/2014 dan bagaimana formulanya supaya pada cell
> B1 = 1/1/2014.
>
> *Hormat saya,*
>
> **
>
> *Samsudin, ST*
>
> *P&M Dept Head*
>
> **
>
> *Description: Description: cid:image002.jpg@01CB3234.2DD4F390 **PT.
> GUNUNGBAYAN PRATAMACOAL (Bayan Group Resources)*
>
> O: +62 542 875 860 Ext :61519 |
>
> M: +62 811 2810 068| +62 813 6914 7150 | email: samsudin@bayan.com.sg
> <mailto:samsudin@bayan.com.sg>__
>
> PSave a tree & save energy. Don't print this e-mail unless it's really
> necessary. Go Green !!!
>
> ISO 9001 : 2008|ISO 14001 : 2004| OHSAS 18001 : 2007
>
> *From:*belajar-excel@yahoogroups.com
> [mailto:belajar-excel@yahoogroups.com]
> *Sent:* Monday, November 17, 2014 12:56 PM
> *To:* belajar-excel@yahoogroups.com
> *Subject:* Re: [belajar-excel] Meriew Data [1 Attachment]
>
> 
>
> Wa'alaykum salam warohmatullohi wabarokatuhu..
>
> Pak Samsudin;
>
> Sila lihat file terlampir, Helper ada dikolom E
>
> Wassalam
>
> ~ Bagus ~
>
> ----- Original Message -----
>
> *From:*Samsudin samsudin@bayan.com.sg [belajar-excel]
> <mailto:samsudin@bayan.com.sg%20[belajar-excel]>
>
> *To:*belajar-excel@yahoogroups.com
> <mailto:belajar-excel@yahoogroups.com>
>
> *Sent:*Monday, November 17, 2014 9:14 AM
>
> *Subject:*[belajar-excel] Meriew Data [1 Attachment]
>
> *Assalmu'alaikum,*
>
> Dear All Be-Excel,
>
> Mohon bantuannya untuk bisa mencari solusi seperti pada file
> terlampir.
>
> Atas bantuannya saya mengucapkan banyak terima kasih
>
> *Wassalam,*
>
> *Samsudin, ST*
>
> *P&M Dept Head*
>
> **
>
> *Description: Description: cid:image002.jpg@01CB3234.2DD4F390
> **PT. GUNUNGBAYAN PRATAMACOAL (Bayan Group Resources)*
>
> O: +62 542 875 860 Ext :61519 |
>
> M: +62 811 2810 068| +62 813 6914 7150 | email:
> samsudin@bayan.com.sg <mailto:samsudin@bayan.com.sg>
>
> PSave a tree & save energy. Don't print this e-mail unless it's
> really necessary. Go Green !!!
>
> ISO 9001 : 2008|ISO 14001 : 2004| OHSAS 18001 : 2007
>
> ----------------------------------------------------------
>
>
>
>
>
> This message (including any attachments) contains confidential information intended
>
> for a specific individual and purpose, and is protected by law. If you are not the
>
> intended recipient, you should delete this message and hereby notified that any
>
> disclosure, copying, or distribution of this message (or any part thereof), or the
>
> taking of any action based on it, is strictly prohibited. No liability or responsibility
>
> is accepted if information or data is, for whatever reason corrupted or does not reach
>
> its intended recipient. No warranty is given that this email is free of viruses. The
>
> views expressed in this email are, unless otherwise stated, those of the author and
>
> not those of the Company or its management. The Company reserves the right to monitor,
>
> intercept and block emails addressed to its users or take any other action in accordance
>
> with its email use policy.
>
> ----------------------------------------------------------
>
> This message (including any attachments) contains confidential information intended
> for a specific individual and purpose, and is protected by law. If you are not the
> intended recipient, you should delete this message and hereby notified that any
> disclosure, copying, or distribution of this message (or any part thereof), or the
> taking of any action based on it, is strictly prohibited. No liability or responsibility
> is accepted if information or data is, for whatever reason corrupted or does not reach
> its intended recipient. No warranty is given that this email is free of viruses. The
> views expressed in this email are, unless otherwise stated, those of the author and
> not those of the Company or its management. The Company reserves the right to monitor,
> intercept and block emails addressed to its users or take any other action in accordance
> with its email use policy.
>
>

Mon Nov 17, 2014 12:24 am (PST) . Posted by:

"Samsudin"

Assalamu'alaikum,

Mr. Bagus,

Terima kasih formula tersebut sudah Ok (mengubah awal tanggal) dan untuk soal meriew data setelah saya coba ditambah data ke bawah sampai A165, formula tersebut tidak bisa hanya batas sampai A40, padahal rumus (helper) sudah diganti data!$A$5:$A$165 pada cell E5. Dan berikut file terlampir.

Apakah jika ada penambahan baris rumus di sheet view diubah dan bagaimana jika saya tambahkan kolom?

Sebelumnya saya mengucapkan terima kasih.

Hormat saya,

Samsudin, ST
P&M Dept Head

[cid:image001.jpg@01D00272.567B1AA0] PT. GUNUNGBAYAN PRATAMACOAL (Bayan Group Resources)
O: +62 542 875 860 Ext :61519 |
M: +62 811 2810 068 | +62 813 6914 7150 | email: samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>
P Save a tree & save energy. Don't print this e-mail unless it's really necessary. Go Green !!!
ISO 9001 : 2008| ISO 14001 : 2004 | OHSAS 18001 : 2007

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com]
Sent: Monday, November 17, 2014 2:01 PM
To: belajar-excel@yahoogroups.com
Subject: Re: [belajar-excel] Meriew Data




Wa'alaykum salam warohmatullohi wabarkatuhu..

Pak Samsudin;

Coba begini:
=A1-day(A1)+1


Wassalam
~ Bagus ~

----- Original Message -----
From: Samsudin samsudin@bayan.com.sg [belajar-excel]<mailto:samsudin@bayan.com.sg%20[belajar-excel]>
To: belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>
Sent: Monday, November 17, 2014 12:19 PM
Subject: RE: [belajar-excel] Meriew Data


Assalamu'alaikum,
Mr. Bagus,
Terima kasih banyak atas pencerahannya dan ada lagi yang ingin saya tanyakan bagaimana caranya cell yang berisi tanggal (sembarang tanggal) dan cell lain ingin menghasilkan awal atau tanggal awal dari cell tersebut,
Misal cell A1 = 23/1/2014 dan bagaimana formulanya supaya pada cell B1 = 1/1/2014.
Hormat saya,
Samsudin, ST
P&M Dept Head

[cid:image001.jpg@01D00272.567B1AA0] PT. GUNUNGBAYAN PRATAMACOAL (Bayan Group Resources)
O: +62 542 875 860 Ext :61519 |
M: +62 811 2810 068 | +62 813 6914 7150 | email: samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>
P Save a tree & save energy. Don't print this e-mail unless it's really necessary. Go Green !!!
ISO 9001 : 2008| ISO 14001 : 2004 | OHSAS 18001 : 2007
From: belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com> [mailto:belajar-excel@yahoogroups.com]
Sent: Monday, November 17, 2014 12:56 PM
To: belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>
Subject: Re: [belajar-excel] Meriew Data [1 Attachment]



Wa'alaykum salam warohmatullohi wabarokatuhu..
Pak Samsudin;
Sila lihat file terlampir, Helper ada dikolom E
Wassalam
~ Bagus ~
----- Original Message -----
From: Samsudin samsudin@bayan.com.sg [belajar-excel]<mailto:samsudin@bayan.com.sg%20[belajar-excel]>
To: belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>
Sent: Monday, November 17, 2014 9:14 AM
Subject: [belajar-excel] Meriew Data [1 Attachment]

Assalmu'alaikum,
Dear All Be-Excel,
Mohon bantuannya untuk bisa mencari solusi seperti pada file terlampir.
Atas bantuannya saya mengucapkan banyak terima kasih
Wassalam,
Samsudin, ST
P&M Dept Head

[cid:image001.jpg@01D00272.567B1AA0] PT. GUNUNGBAYAN PRATAMACOAL (Bayan Group Resources)
O: +62 542 875 860 Ext :61519 |
M: +62 811 2810 068 | +62 813 6914 7150 | email: samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>
P Save a tree & save energy. Don't print this e-mail unless it's really necessary. Go Green !!!
ISO 9001 : 2008| ISO 14001 : 2004 | OHSAS 18001 : 2007

----------------------------------------------------------





This message (including any attachments) contains confidential information intended

for a specific individual and purpose, and is protected by law. If you are not the

intended recipient, you should delete this message and hereby notified that any

disclosure, copying, or distribution of this message (or any part thereof), or the

taking of any action based on it, is strictly prohibited. No liability or responsibility

is accepted if information or data is, for whatever reason corrupted or does not reach

its intended recipient. No warranty is given that this email is free of viruses. The

views expressed in this email are, unless otherwise stated, those of the author and

not those of the Company or its management. The Company reserves the right to monitor,

intercept and block emails addressed to its users or take any other action in accordance

with its email use policy.


----------------------------------------------------------



This message (including any attachments) contains confidential information intended

for a specific individual and purpose, and is protected by law. If you are not the

intended recipient, you should delete this message and hereby notified that any

disclosure, copying, or distribution of this message (or any part thereof), or the

taking of any action based on it, is strictly prohibited. No liability or responsibility

is accepted if information or data is, for whatever reason corrupted or does not reach

its intended recipient. No warranty is given that this email is free of viruses. The

views expressed in this email are, unless otherwise stated, those of the author and

not those of the Company or its management. The Company reserves the right to monitor,

intercept and block emails addressed to its users or take any other action in accordance

with its email use policy.



----------------------------------------------------------

This message (including any attachments) contains confidential information intended
for a specific individual and purpose, and is protected by law. If you are not the
intended recipient, you should delete this message and hereby notified that any
disclosure, copying, or distribution of this message (or any part thereof), or the
taking of any action based on it, is strictly prohibited. No liability or responsibility
is accepted if information or data is, for whatever reason corrupted or does not reach
its intended recipient. No warranty is given that this email is free of viruses. The
views expressed in this email are, unless otherwise stated, those of the author and
not those of the Company or its management. The Company reserves the right to monitor,
intercept and block emails addressed to its users or take any other action in accordance
with its email use policy.

Sun Nov 16, 2014 10:01 pm (PST) . Posted by:

"Bagus" bagus4bls

Coba begini Pak;

Misal data ada di A2.
=DateValue(A2)

Wassalam

~ Bagus ~

----- Original Message -----
From: 'Subroto, Matzen' matzen.subroto@akzonobel.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Monday, November 17, 2014 12:41 PM
Subject: RE: [belajar-excel] Merubah Text date

Maksudnya tanggal yang sekarang adalah text, saya ingin merubahnya menjadi date

Terima Kasih

Matzen

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com]
Sent: 17 Nopember 2014 11:48
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Merubah Text date

misalnya data ada di

A

1 10/30/2014

rumusnya

=text(A1,"dd-mmm-yy") --------> hasilnya 30-Okt-14

=text(A1,"dd-mmm-yyyy")-------->hasilnya 30 Oktober 2014

Pada Senin, 17 November 2014 11:01, "'Subroto, Matzen' matzen.subroto@akzonobel.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:

Assalamu alaikum,

Dear Pakat Execel

Gimana cara merubah format date dalam bentuk text seperti di bawah ini

10/30/2014

9/28/2014

11/5/2014

Bulan/tanggal/tahun

Wassalamu alaikum

Terima Kasih

Matzen

__________________________________________________________
This email has been scanned for Viruses and Spam. For more information
please contact your local Business Unit Information Security representative.
__________________________________________________________

Sun Nov 16, 2014 10:03 pm (PST) . Posted by:

"Nang Agus"

Pak Matxzen,

Misalnya text di A1, gunakan saja formula:
DATEVALUE(A1)

Jika formula tersebut tidak dapat digunakan coba gunakan yang ini:
=DATE(RIGHT(A1;4);LEFT(A1;FIND("/";A1;1)-1);MID(A1;FIND("/";A1;1)+1;FIND("/";A1;4)-FIND("/";A1;1)-1))

Salam

On 11/17/14, 'Subroto, Matzen' matzen.subroto@akzonobel.com
[belajar-excel] <belajar-excel@yahoogroups.com> wrote:
> Maksudnya tanggal yang sekarang adalah text, saya ingin merubahnya menjadi
> date
>
> Terima Kasih
>
> Matzen
>
> From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com]
> Sent: 17 Nopember 2014 11:48
> To: belajar-excel@yahoogroups.com
> Subject: Bls: [belajar-excel] Merubah Text date
>
>
> misalnya data ada di
> A
> 1 10/30/2014
>
> rumusnya
>
> =text(A1,"dd-mmm-yy") --------> hasilnya 30-Okt-14
> =text(A1,"dd-mmm-yyyy")-------->hasilnya 30 Oktober 2014
>
>
> Pada Senin, 17 November 2014 11:01, "'Subroto, Matzen'
> matzen.subroto@akzonobel.com<mailto:matzen.subroto@akzonobel.com>
> [belajar-excel]"
> <belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>>
> menulis:
>
>
> Assalamu alaikum,
>
> Dear Pakat Execel
>
> Gimana cara merubah format date dalam bentuk text seperti di bawah ini
>
> 10/30/2014
> 9/28/2014
> 11/5/2014
>
> Bulan/tanggal/tahun
>
> Wassalamu alaikum
>
> Terima Kasih
>
> Matzen
>
>
>
>
>
> __________________________________________________________
> This email has been scanned for Viruses and Spam. For more information
> please contact your local Business Unit Information Security
> representative.
> __________________________________________________________
>

Mon Nov 17, 2014 12:23 am (PST) . Posted by:

"Subroto, Matzen"

Dear Pak Nang Agus,

Dah saya coba masih tetap value

Terima Kasih

Matzen

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com]
Sent: 17 Nopember 2014 13:04
To: belajar-excel@yahoogroups.com
Subject: Re: [belajar-excel] Merubah Text date



Pak Matxzen,

Misalnya text di A1, gunakan saja formula:
DATEVALUE(A1)

Jika formula tersebut tidak dapat digunakan coba gunakan yang ini:
=DATE(RIGHT(A1;4);LEFT(A1;FIND("/";A1;1)-1);MID(A1;FIND("/";A1;1)+1;FIND("/";A1;4)-FIND("/";A1;1)-1))

Salam

On 11/17/14, 'Subroto, Matzen' matzen.subroto@akzonobel.com<mailto:matzen.subroto@akzonobel.com>
[belajar-excel] <belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>> wrote:
> Maksudnya tanggal yang sekarang adalah text, saya ingin merubahnya menjadi
> date
>
> Terima Kasih
>
> Matzen
>
> From: belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com> [mailto:belajar-excel@yahoogroups.com]
> Sent: 17 Nopember 2014 11:48
> To: belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>
> Subject: Bls: [belajar-excel] Merubah Text date
>
>
> misalnya data ada di
> A
> 1 10/30/2014
>
> rumusnya
>
> =text(A1,"dd-mmm-yy") --------> hasilnya 30-Okt-14
> =text(A1,"dd-mmm-yyyy")-------->hasilnya 30 Oktober 2014
>
>
> Pada Senin, 17 November 2014 11:01, "'Subroto, Matzen'
> matzen.subroto@akzonobel.com<mailto:matzen.subroto@akzonobel.com<mailto:matzen.subroto@akzonobel.com%3cmailto:matzen.subroto@akzonobel.com>>
> [belajar-excel]"
> <belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com%3cmailto:belajar-excel@yahoogroups.com>>>
> menulis:
>
>
> Assalamu alaikum,
>
> Dear Pakat Execel
>
> Gimana cara merubah format date dalam bentuk text seperti di bawah ini
>
> 10/30/2014
> 9/28/2014
> 11/5/2014
>
> Bulan/tanggal/tahun
>
> Wassalamu alaikum
>
> Terima Kasih
>
> Matzen
>
>
>
>
>
> __________________________________________________________
> This email has been scanned for Viruses and Spam. For more information
> please contact your local Business Unit Information Security
> representative.
> __________________________________________________________
>


__________________________________________________________
This email has been scanned for Viruses and Spam. For more information
please contact your local Business Unit Information Security representative.
__________________________________________________________

Mon Nov 17, 2014 1:25 am (PST) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

Wa'alaikumussalam wr. wb.

Hai Matzen,
Bagaimana kabarnya ? Moga-moga baik.

Nilai data bertipe text tampak seperti suatu data tanggal dengan format
m/d/yyyy akan diolah agar menghasilkan nilai data bertipe datetime,
sehingga bisa diolah lebih luwes dan luas termasuk ditampilkan dalam format
number apapun.

Contoh data : 9 januari 2014 tertulis di cell A1 sebagai teks (bertipe
text) berbunyi 1/9/2014

Bagian tahun [yyyy] : (4 karakter terkanan)
right( a1 , 4 )

Bagian bulan [m] : (sebanyak karakter sebelum karakter '/' pertama)
left( a1 , find( "/" , a1 ) -1 )

Bagian nilai hari [d] : (mulai dari karakter '/' pertama data diambil
sebanyak 4 karakter dari data yang sudah diubah karakter '/' nya menjadi 2
buah spasi)
mid( substitute( a1 , "/" , " " ) , find( "/" , a1 ) , 4 )

Kemudian menyusun data datetime berdasar informasi tahun, bulan, nilai hari
dengan memanfaatkan fungsi date
=date( *tahun *, *bulan *, nilai_hari )
=date( *right( a1 , 4 )* , *left( a1 , find( "/" , a1 ) -1 ) * , mid(
substitute( a1 , "/" , " " ) , find( "/" , a1 ) , 4 ) )

Wassalamu'alaikum wr wb
Kid.

2014-11-17 14:28 GMT+11:00 'Subroto, Matzen' matzen.subroto@akzonobel.com
[belajar-excel] <belajar-excel@yahoogroups.com>:

>
>
> Assalamu alaikum,
>
>
>
> Dear Pakat Execel
>
>
>
> Gimana cara merubah format date dalam bentuk text seperti di bawah ini
>
>
>
> 10/30/2014
>
> 9/28/2014
>
> 11/5/2014
>
>
>
> Bulan/tanggal/tahun
>
>
>
> Wassalamu alaikum
>
>
>
> Terima Kasih
>
>
>
> *Matzen*
>
>
>
>
>

Sun Nov 16, 2014 10:19 pm (PST) . Posted by:

"Bagus" bagus4bls

Pak AP;

Coba begini:
=MIN(1,SUMPRODUCT(($A4=Distribusi!$B$3:$B$161)*(E$3=Distribusi!$D$1:$F$1)*(Distribusi!$D$3:$F$161)))
Copas kenanan dan kebawah

Wassalam

~ Bagus ~

----- Original Message -----
From: purwadi_arif@yahoo.com [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Monday, November 17, 2014 12:51 PM
Subject: [belajar-excel] Tanya Suhu (summary dari banyak kolom) [1 Attachment]

Dear para Suhu Member belajar-excel group,

Saya mau menanyakan persoalan saya di lampiran berikut, dimana data yang saya inginkan yaitu bila ada 2 artikel yang ada distribusi nya sedangkan grup itemnya sama, tetap terbaca angka 1 distribusinya.

Saya sudah mencoba untuk rumusnya tetapi masih salah, mohon koreksinya suhu. Terimakasih sebelumnya

Regards

AP

Sun Nov 16, 2014 10:23 pm (PST) . Posted by:

"Nang Agus"

Pak Arif Purwadi,

mungkinkah menggunakan sumproduct?

Coba lihat lampiran.

On 11/17/14, purwadi_arif@yahoo.com [belajar-excel]
<belajar-excel@yahoogroups.com> wrote:
> Dear para Suhu Member belajar-excel group,
>
>
> Saya mau menanyakan persoalan saya di lampiran berikut, dimana data yang
> saya inginkan yaitu bila ada 2 artikel yang ada distribusi nya sedangkan
> grup itemnya sama, tetap terbaca angka 1 distribusinya.
>
>
> Saya sudah mencoba untuk rumusnya tetapi masih salah, mohon koreksinya
> suhu. Terimakasih sebelumnya
>
>
> Regards
> AP
>
>

Mon Nov 17, 2014 12:24 am (PST) . Posted by:

purwadi_arif

Dear Pak Agus,


Saat saya menggunakan rumus Pak Agus untuk worksheet yang lain ternyata hasilnya N/A
Bisa mohon diberi pencerahan, terimakasih sebelumnya


Regards
AP

Mon Nov 17, 2014 12:24 am (PST) . Posted by:

purwadi_arif

Dear Pak Bagus,


Terimakasih atas bantuannya, done !!! :)


Regards
AP

Sun Nov 16, 2014 10:31 pm (PST) . Posted by:

"SUYONO"

Siang Pak Boby

Coba seperti ini semoga sesuai keinginan.

Salam
Suyono

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com]
Sent: Monday, November 17, 2014 11:44 AM
To: belajar-excel@yahoogroups.com
Subject: [belajar-excel] Cara membuat Vba tanggal ke text yang hanya membaca bulan dan tahun [1 Attachment]


Kepada Suhu2 Excel
berikut saya lampirkan workbook dan pertanyaan ada diworksheet tsb
Terima kasih sebelum

Mon Nov 17, 2014 12:23 am (PST) . Posted by:

"BOBY CHUA" boby66_chua

Terima kasih banget tambahan ilmunya

salam

Boby

Pada Senin, 17 November 2014 12:50, "Nang Agus nangagus@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


Dear Boby.

Coba tambahkan Cekrip:
ws.Cells(iRow + 1, 3).Value = Me.tdate.Value
ws.Cells(iRow + 1, 3).NumberFormat = "mmm-yy"

sebelum clear data

Salam

On 11/17/14, BOBY CHUA boby66_chua@yahoo.com [belajar-excel]
<belajar-excel@yahoogroups.com> wrote:
> Kepada Suhu2 Excel
> berikut saya lampirkan workbook dan pertanyaan ada diworksheet tsb
> Terima kasih sebelum
>

------------------------------------
Posted by: Nang Agus <nangagus@gmail.com>
------------------------------------

============================================================
Pojok Lowongan Kerja yang disediakan milis :
http://milis-belajar-excel.1048464.n5.nabble.com/Pojok-Lowongan-Kerja-f5725753.html
*** Posting lowongan kerja : ke link tersebut dan klik New Topic
============================================================
bergabung ke milis (subscribe), kirim mail kosong ke: belajar-excel-subscribe@yahoogroups.com

posting ke milis, kirimkan ke: belajar-excel@yahoogroups.com

berkunjung ke web milis : http://tech.groups.yahoo.com/group/belajar-excel/messages

melihat file archive / mendownload lampiran : http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di : http://milis-belajar-excel.1048464.n5.nabble.com/

menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com

keluar dari membership milis (UnSubscribe), kirim mail kosong ke : belajar-excel-unsubscribe@yahoogroups.com
----------------------------------------------------------
------------------------------------

Yahoo Groups Links

https://info.yahoo.com/legal/us/yahoo/utos/terms/

Mon Nov 17, 2014 1:03 am (PST) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

Wa'alaikumussalam wr wb

Jadi umur lebih dulu ya.
Formulanya akan bekerja dengan alur logika yang berbeda dengan makna bunyi
pasal 6 tentang kriteria.
Cell L6 :
=1+((YEARFRAC(I6;TODAY())>=4)+(G6>=16000)>0)+2*(G6=0)
copy ke bawah

cell M6 :
=(G6<>0)*LOOKUP($L6,{1;2;3;4},{6;4;200;100})*1000+G6
copy ke bawah dan ke kanan

*** jika regional setting komputer setempat adalah Indonesian, ganti
seluruh karakter koma dalam formula menjadi titik koma.

Wassalamu'alaikum wr wb
Kid.

2014-11-17 12:37 GMT+11:00 Samsudin samsudin@bayan.com.sg [belajar-excel] <
belajar-excel@yahoogroups.com>:

>
>
> Assalamu'alaikum,
>
>
>
> Mr. Kid,
>
>
>
> Terima kasih atas penjelasannya dan jika harus memilih ya … Umur yang
> dicapai terlebih dahulu, sehingga formulanya diubah seperti ini
> =IF(G6,IF((G6<4)+(YEARFRAC(I6,TODAY())<4),1,2),IF(YEARFRAC(I6,TODAY())<4,3,4))
>
>
>
> *wassalam,*
>
>
>
> *Samsudin, ST*
>
> *P&M Dept Head*
>
>
>
> *[image: Description: Description: cid:image002.jpg@01CB3234.2DD4F390] **PT.
> GUNUNGBAYAN PRATAMACOAL (Bayan Group Resources)*
>
> O: +62 542 875 860 Ext :61519 |
>
> M: +62 811 2810 068 | +62 813 6914 7150 | email: samsudin@bayan.com.sg
>
> P Save a tree & save energy. Don't print this e-mail unless it's
> really necessary. Go Green !!!
>
> ISO 9001 : 2008| ISO 14001 : 2004 | OHSAS 18001 : 2007
>
>
>
> *From:* belajar-excel@yahoogroups.com [mailto:
> belajar-excel@yahoogroups.com]
> *Sent:* Monday, November 17, 2014 8:54 AM
> *To:* BeExcel
> *Subject:* Re: [belajar-excel] Kategori Umur
>
>
>
>
>
> Wa'alaikumussalam wr wb
>
> Boleh tahu, bagaimana Excel bisa tahu bahwa baris 20 itu tercapai lebih
> dulu berdasar kolom umur dibanding kolom HM nya ?
>
>
>
> Kalau ada data alat buatan tahun 2000 yang saat ini memiliki HM 18000 :
>
> Alat mencapai HM lebih dari 16000 lebih dulu atau mencapai umur lebih dari
> 4 tahun lebih dulu ?
>
>
>
> Kalau kasus di atas keduanya tercapai dengan pasal 2, tetapi adakalanya
> stategi perusahaan yang menerapkan agar lulus commissioning dibuatkan
> cara-cara yang kotor yaitu HM disetel pada angka di bawah 16000 tetapi
> umurnya sudah lebih dari 4 tahun beroperasi, makanya kita menerapkan pasal
> tersebut, secara logika semakin bertambahnya umur alat maka jam kerjanya
> semakin tinggi (HM). Dan suatu alat bisa terjadi HM (jam kerjanya sedikit)
> tetapi umurnya sudah lebih dari 4 tahun, biasanya alat tersebut pada saat
> beroperasi 1 tahun mengalami kerusakan fatal sehingga memerlukan penangaan
> yang lama untuk bisa dioperasikan kembali (memerlukan waktu lebih dari 4
> tahun) tetapi catatan HM masih dibawah 16000 jam.
>
>
>
> Kemudian, coba bayangkan, apakah Excel bisa mengira-ira untuk menjawab
> seperti Anda ?
>
> Jika Anda rasa Excel tidak mampu mengira-ira seperti Anda, informasi apa
> yang harus diberikan ke Excel tentang mana yang harus didahulukan antara
> nilai HM dengan nilai umur ?
>
>
>
> Wassalamu'alaikum wr wb
>
> Kid.
>
>
>
> 2014-11-17 11:39 GMT+11:00 Samsudin samsudin@bayan.com.sg [belajar-excel]
> <belajar-excel@yahoogroups.com>:
>
>
>
> Assalmu'alaikum,
>
>
>
> Mr. Kid,
>
>
>
> Betul sekali untuk kolom M & N sudah tidak masalah, tetapi bagiamana untuk
> kolom L khusus kategori umur 1 & 2 yang harus membaca dua kolom yaitu G &
> I, mohon pencerahannya untuk formula yang bisa digunakan, karena dari
> contoh kasus pada L20 = 1 seharusnya L20 = 2 karena factor umur yang sudah
> tercapai lebih dahulu (16 tahun) walaupun catatan HM masih 123 jam.
>
>
>
> Ada tambahan lagi jika kita menginginkan agar pada salah satu kolom dibuat
> tulisan berkedip (flash) sebagai reminder yang mengingatkan massanya akan
> berakhir, apakah bisa dibuatkan kondisi seperti itu dalam sheet excel?
>
>
>
> Dan sebelumnya saya mengucapkan banyak terima kasih.
>
>
>
> *Hormat saya,*
>
>
>
> *Samsudin, ST*
>
> *P&M Dept Head*
>
>
>
> *[image: Description: Description: cid:image002.jpg@01CB3234.2DD4F390] **PT.
> GUNUNGBAYAN PRATAMACOAL (Bayan Group Resources)*
>
> O: +62 542 875 860 Ext :61519 |
>
> M: +62 811 2810 068 | +62 813 6914 7150 | email: samsudin@bayan.com.sg
>
> P Save a tree & save energy. Don't print this e-mail unless it's
> really necessary. Go Green !!!
>
> ISO 9001 : 2008| ISO 14001 : 2004 | OHSAS 18001 : 2007
>
>
>
> *From:* belajar-excel@yahoogroups.com [mailto:
> belajar-excel@yahoogroups.com]
> *Sent:* Monday, November 17, 2014 7:45 AM
> *To:* BeExcel
> *Subject:* Re: [belajar-excel] Kategori Umur
>
>
>
>
>
> Wa'alikumussalam wr wb
>
> hehehe...
>
> ya deh... nilai kriterianya jadi berapa saja boleh deh. Karena masalah
> utamanya bukan di nilai kriteria tersebut. Tapi di cara penentuan
> kelompoknya.
>
> Contoh dari file lampiran yang Anda kirim terakhir (sorry baru bisa buka
> pagi ini).
>
> Anda berpesan :
> "misal L20, seharusnya di L20 = 2"
>
> data di baris 20 :
>
> HM (jam) = 123
>
> Tahun = 1998 berarti umur sekitar 16 Tahun yak...betul Mr. Kid
>
>
> Pertanyaan pokoknya :
> Dengan kriteria nomor 1 dan nomor 2, maka data tersebut bisa di kelompok 1
> atau kelompok 2. Apakah semua kondisi yang bisa di kelompok 1 atau kelompok
> 2 pasti akan masuk kelompok 2 ?
>
>
>
> Untuk kasus di atas masuk ke kategori umur 2 karena sudah berumur 16 tahun
> tetapi HM = 123 jam.
>
>
>
> Jika pertanyaan diatas sudah menghasilkan kepastian kriteria, maka bisa
> disusun formula untuk kolom L yang akan menghasilkan nilai 1 sampai 4
> (nomor kelompok).
>
> Asumsikan saja kolom L sudah menghasilkan dengan tepat.
>
> Commissioning bisa disusun berdasar hasil kolom L dengan fungsi IF
>
> =IF( l6=1 , 6000+bla , if( l6=2 , dst ,dst ))
>
>
>
> Khusus untuk yang tergantung kelompok 3 dan 4 saja, berarti IF nya dimulai
> langsung cek kolom L bernilai 3 lalu cek 4, dst
>
> =if( l6=3 , sekiun + bla, if( dst,dst,dst) )
>
> Begitu kan ya
>
> Wassalamu'alaikum Wr. Wb.
> Kid.
>
>
>
>
>
>
>
> 2014-11-17 9:23 GMT+11:00 Samsudin samsudin@bayan.com.sg [belajar-excel] <
> belajar-excel@yahoogroups.com>:
>
>
>
> Assalamu'alaikum
>
>
>
> Mr. Kid
>
>
>
> Sorry kriteria tetap 16000 jam, jadi khusus untuk pasal 1 & 2 adalah mana
> yang lebih tercapai lebih dulu dan hasilnya masuk kedalam kategori umur 1
> atau 2, dimana jika masuk kedalam :
>
>
>
> 1. Kategori umur 1 maka penambahan atau unit tersebut akan
> dilakukan commissioning ulang setelah beroperasi 6000 jam atau pada kolom
> next commissioning pada kolom HM (+6000)
>
> 2. Kategori umur 2 maka penambahan atau unit tersebut akan
> dilakukan commissioning ulang setelah beroperasi 4000 jam atau pada kolom
> next commissioning pada kolom HM (+4000)
>
>
>
> Jadi ada dua sumber acuan untuk menentukan kategori umur pada unit dengan
> menggunakan HM (kolom G & I, disini mana yang tercapai lebih dulu),
> sedangkan untuk kategori umur 3 & 4 tidak masalah menggunakan formula yang
> sudah diberikan.
>
>
>
> Mohon pencerahannya untuk kasus ini, sebelumnya saya mengucapkan terima
> kasih.
>
>
>
>
>
>
>
> *Wasssalm,*
>
>
>
> *Samsudin, ST*
>
> *P&M Dept Head*
>
>
>
> *[image: Description: Description: cid:image002.jpg@01CB3234.2DD4F390] **PT.
> GUNUNGBAYAN PRATAMACOAL (Bayan Group Resources)*
>
> O: +62 542 875 860 Ext :61519 |
>
> M: +62 811 2810 068 | +62 813 6914 7150 | email: samsudin@bayan.com.sg
>
> P Save a tree & save energy. Don't print this e-mail unless it's
> really necessary. Go Green !!!
>
> ISO 9001 : 2008| ISO 14001 : 2004 | OHSAS 18001 : 2007
>
>
>
> *From:* belajar-excel@yahoogroups.com [mailto:
> belajar-excel@yahoogroups.com]
> *Sent:* Sunday, November 16, 2014 3:01 PM
>
>
> *To:* BeExcel
> *Subject:* Re: [belajar-excel] Kategori Umur
>
>
>
>
>
> Wa'alaikumussalam Wr. Wb.
>
> oh kriterianya diganti dari batasan 16000 jam jadi 6000 jam ya...
>
> baiklah, kalau begitu datanya begini :
>
> HM (jam) umur (tahun)
> 100 2
>
> 100 4
> 100 17
> 6000 2
> 6000 4
> 6000 17
> 789000 2
> 789000 4
> 789000 17
>
> Pastikan jawabannya yang konsisten. Dengan begitu akan diketahui kriteria
> yang benar harusnya bagaimana.
>
>
>
> data HM (jam) bernilai 100 jam
>
> data umur (tahun) bernilai 17 tahun
>
> soal : berapakah nilai kelompoknya ?
>
>
>
> Berdasar bunyi pasal bagian :
> 1. Kategori umur 1 adalah alat yang menggunakan HM dengan umur
> dibawah 4 tahun atau alat tersebut belum mencapai 6000 jam
>
> HM 100 jam berarti MEMENUHI kelompok 1 alias *TRUE *bahwa alat
> tersebut belum mencapai 6000 jam
>
> umur 17 tahun berarti TIDAK MEMENUHI kelompok 1 alias *FALSE *bahwa
> umur dibawah 4 tahun
>
> *TRUE *OR *FALSE *equal TRUE
>
> berarti data HM 100 dengan umur 17 tahun akan menjadi anggota
> kelompok 1
>
>
>
> 2. Kategori umur 2 adalah alat yang menggunakan HM dengan umur
> diatas 4 tahun atau alat tersebut diatas 6000 jam
>
> HM 100 jam berarti TIDAK MEMENUHI kelompok 2 alias *FALSE* bahwa alat
> tersebut diatas 6000 jam
>
> umur 17 tahun berarti MEMENUHI kelompok 2 alias *TRUE *bahwa umur
> diatas 4 tahun
>
> *FALSE* OR *TRUE *equal TRUE
>
> berarti data HM 100 dengan umur 17 tahun akan menjadi anggota
> kelompok 2
>
>
>
> *Berdasar bunyi pasal-pasalnya, data tersebut bisa di kelompok 1 dan bisa
> di kelompok 2.*
>
> Data tersebut akan masuk kelompok mana ?
>
>
>
> Wassalamu'alaikum Wr. Wb.
>
> Kid.
>
>
>
>
>
>
>
> On Sun, Nov 16, 2014 at 5:41 PM, Samsudin samsudin@bayan.com.sg
> [belajar-excel] <belajar-excel@yahoogroups.com> wrote:
>
>
>
> Assalamu'alaikum,
>
>
>
> Mr. Kid,
>
>
>
> Maksudnya pasal tsb adalah :
>
>
>
> 1. Kategori umur 1 adalah alat yang menggunakan HM dengan umur
> dibawah 4 tahun atau alat tersebut belum mencapai 6000 jam
>
> 2. Kategori umur 2 adalah alat yang menggunakan HM dengan umur
> diatas 4 tahun atau alat tersebut diatas 6000 jam
>
> 3. Kategori umur 3 adalah alat yang menggunakan KM dengan umur
> dibawah 4 tahun
>
> 4. Kategori umur 4 adalah alat yang menggunakan KM dengan umur
> diatas 4 tahun
>
>
>
>
>
>
>
> *Wassalamu'alaikum,*
>
>
>
> *Samsudin, ST*
>
> *P&M Dept Head*
>
>
>
> *[image: Description: Description: cid:image002.jpg@01CB3234.2DD4F390] **PT.
> GUNUNGBAYAN PRATAMACOAL (Bayan Group Resources)*
>
> O: +62 542 875 860 Ext :61519 |
>
> M: +62 811 2810 068 | +62 813 6914 7150 | email: samsudin@bayan.com.sg
>
> P Save a tree & save energy. Don't print this e-mail unless it's
> really necessary. Go Green !!!
>
> ISO 9001 : 2008| ISO 14001 : 2004 | OHSAS 18001 : 2007
>
>
>
> *From:* belajar-excel@yahoogroups.com [mailto:
> belajar-excel@yahoogroups.com]
> *Sent:* Sunday, November 16, 2014 2:31 PM
> *To:* BeExcel
> *Subject:* Re: [belajar-excel] Kategori Umur
>
>
>
>
>
> Pak Samsudin,
>
> Berhubung saya bukan ahlli bahasa hukum, maka dengan susunan kriteria
> berbunyi :
> Ketentuan umur Commissioning (GBP2-SOP-PM05-REV.01 Pasal 6.17) :
> 1. 6.000 jam untuk Alat umur <16.000 jam atau <4 Tahun
> 2. 4.000 jam untuk Alat umur >16.000 jam atau >4 Tahun
> 3. 200.000 km untuk Alat umur <4 Tahun yang tidak memiliki Hour Meter (HM)
> 4. 100.000 km untuk Alat umur >4 Tahun yang tidak memiliki Hour Meter (HM)
> 5. Untuk alat yang terjadi kecelakaan akan di commissioning ulang
> tergantung dari hasil rekomendasi investigasi kecelakaan
>
> Diasumsikan kelompok akan dipilih dengan prioritas dari urutan paling atas
> (1 lalu 2 dst)
>
> Coba Anda isi mengikuti kaidah tersebut mengikuti aturan pasal di atas :
>
> HM (jam) umur (tahun)
> 100 2
>
> 100 4
> 100 17
> 16000 2
> 16000 4
> 16000 17
> 789000 2
> 789000 4
> 789000 17
>
> Pastikan jawabannya yang konsisten. Dengan begitu akan diketahui kriteria
> yang benar harusnya bagaimana.
>
> Berdasar bunyi pasal bagian :
> 1. 6.000 jam untuk Alat umur <16.000 jam atau <4 Tahun
>
> 100 jam ATAU 17 tahun ? YA atau TIDAK hasilnya TRUE or FALSE = TRUE
> -> kelompok 6000 jam alias kelompok 1
>
> 2. 4.000 jam untuk Alat umur >16.000 jam atau >4 Tahun
>
> 100 jam ATAU 17 tahun ? TIDAK atau YA hasilnya FALSE or TRUE = TRUE
> -> kelompok 4000 jam alias kelompok 2
>
> Mana yang akan dipilih ?
>
> Berhubung saat ini Kid cuma ada 1, saya pilih yang kelompok 1 sajah...
> kalo salah ya harap maklum.... namanya juga yang rasanya pas buat Kid....
>
> ;)
>
> Wassalam,
> Kid.
>
>
>
>
>
>
>
> On Sun, Nov 16, 2014 at 5:14 PM, Samsudin samsudin@bayan.com.sg
> [belajar-excel] <belajar-excel@yahoogroups.com> wrote:
>
>
>
> Assalamu'alaikum,
>
>
>
> Mr Kid,
>
>
>
> Terima kasih atas formulanya tetapi masih ada yang belum pas (file
> terlampir)
>
>
>
>
>
> *Wassalamu'alaikum,*
>
>
>
> *Samsudin, ST*
>
> *P&M Dept Head*
>
>
>
> *[image: Description: Description: cid:image002.jpg@01CB3234.2DD4F390] **PT.
> GUNUNGBAYAN PRATAMACOAL (Bayan Group Resources)*
>
> O: +62 542 875 860 Ext :61519 |
>
> M: +62 811 2810 068 | +62 813 6914 7150 | email: samsudin@bayan.com.sg
>
> P Save a tree & save energy. Don't print this e-mail unless it's
> really necessary. Go Green !!!
>
> ISO 9001 : 2008| ISO 14001 : 2004 | OHSAS 18001 : 2007
>
>
>
> ----------------------------------------------------------
>
>
>
>
>
> This message (including any attachments) contains confidential information intended
>
> for a specific individual and purpose, and is protected by law. If you are not the
>
> intended recipient, you should delete this message and hereby notified that any
>
> disclosure, copying, or distribution of this message (or any part thereof), or the
>
> taking of any action based on it, is strictly prohibited. No liability or responsibility
>
> is accepted if information or data is, for whatever reason corrupted or does not reach
>
> its intended recipient. No warranty is given that this email is free of viruses. The
>
> views expressed in this email are, unless otherwise stated, those of the author and
>
> not those of the Company or its management. The Company reserves the right to monitor,
>
> intercept and block emails addressed to its users or take any other action in accordance
>
> with its email use policy.
>
>
>
> ----------------------------------------------------------
>
>
>
> This message (including any attachments) contains confidential information intended
>
> for a specific individual and purpose, and is protected by law. If you are not the
>
> intended recipient, you should delete this message and hereby notified that any
>
> disclosure, copying, or distribution of this message (or any part thereof), or the
>
> taking of any action based on it, is strictly prohibited. No liability or responsibility
>
> is accepted if information or data is, for whatever reason corrupted or does not reach
>
> its intended recipient. No warranty is given that this email is free of viruses. The
>
> views expressed in this email are, unless otherwise stated, those of the author and
>
> not those of the Company or its management. The Company reserves the right to monitor,
>
> intercept and block emails addressed to its users or take any other action in accordance
>
> with its email use policy.
>
>
>
> ----------------------------------------------------------
>
>
>
> This message (including any attachments) contains confidential information intended
>
> for a specific individual and purpose, and is protected by law. If you are not the
>
> intended recipient, you should delete this message and hereby notified that any
>
> disclosure, copying, or distribution of this message (or any part thereof), or the
>
> taking of any action based on it, is strictly prohibited. No liability or responsibility
>
> is accepted if information or data is, for whatever reason corrupted or does not reach
>
> its intended recipient. No warranty is given that this email is free of viruses. The
>
> views expressed in this email are, unless otherwise stated, those of the author and
>
> not those of the Company or its management. The Company reserves the right to monitor,
>
> intercept and block emails addressed to its users or take any other action in accordance
>
> with its email use policy.
>
>
>
> ----------------------------------------------------------
>
>
>
> This message (including any attachments) contains confidential information intended
>
> for a specific individual and purpose, and is protected by law. If you are not the
>
> intended recipient, you should delete this message and hereby notified that any
>
> disclosure, copying, or distribution of this message (or any part thereof), or the
>
> taking of any action based on it, is strictly prohibited. No liability or responsibility
>
> is accepted if information or data is, for whatever reason corrupted or does not reach
>
> its intended recipient. No warranty is given that this email is free of viruses. The
>
> views expressed in this email are, unless otherwise stated, those of the author and
>
> not those of the Company or its management. The Company reserves the right to monitor,
>
> intercept and block emails addressed to its users or take any other action in accordance
>
> with its email use policy.
>
>
>
> ----------------------------------------------------------
>
> This message (including any attachments) contains confidential information intended
> for a specific individual and purpose, and is protected by law. If you are not the
> intended recipient, you should delete this message and hereby notified that any
> disclosure, copying, or distribution of this message (or any part thereof), or the
> taking of any action based on it, is strictly prohibited. No liability or responsibility
> is accepted if information or data is, for whatever reason corrupted or does not reach
> its intended recipient. No warranty is given that this email is free of viruses. The
> views expressed in this email are, unless otherwise stated, those of the author and
> not those of the Company or its management. The Company reserves the right to monitor,
> intercept and block emails addressed to its users or take any other action in accordance
> with its email use policy.
>
>
>

Mon Nov 17, 2014 1:06 am (PST) . Posted by:

"Bagus" bagus4bls

Wa'alaykum salam warohmatullohi wabarokatuhu..

Pak Samsudin;

File terlampir ada penambahan helper pada A1

Sila dilihat juga pada Name Manager

Wassalam

~ Bagus ~

----- Original Message -----
From: Samsudin samsudin@bayan.com.sg [belajar-excel]
To: belajar-excel@yahoogroups.com
Sent: Monday, November 17, 2014 1:41 PM
Subject: RE: [belajar-excel] Meriew Data [1 Attachment]

Assalamu'alaikum,

Mr. Bagus,

Terima kasih formula tersebut sudah Ok (mengubah awal tanggal) dan untuk soal meriew data setelah saya coba ditambah data ke bawah sampai A165, formula tersebut tidak bisa hanya batas sampai A40, padahal rumus (helper) sudah diganti data!$A$5:$A$165 pada cell E5. Dan berikut file terlampir.

Apakah jika ada penambahan baris rumus di sheet view diubah dan bagaimana jika saya tambahkan kolom?

Sebelumnya saya mengucapkan terima kasih.

Hormat saya,

Samsudin, ST

P&M Dept Head

PT. GUNUNGBAYAN PRATAMACOAL (Bayan Group Resources)

O: +62 542 875 860 Ext :61519 |

M: +62 811 2810 068 | +62 813 6914 7150 | email: samsudin@bayan.com.sg

P Save a tree & save energy. Don't print this e-mail unless it's really necessary. Go Green !!!

ISO 9001 : 2008| ISO 14001 : 2004 | OHSAS 18001 : 2007

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com]
Sent: Monday, November 17, 2014 2:01 PM
To: belajar-excel@yahoogroups.com
Subject: Re: [belajar-excel] Meriew Data



Wa'alaykum salam warohmatullohi wabarkatuhu..

Pak Samsudin;

Coba begini:

=A1-day(A1)+1

Wassalam

~ Bagus ~

----- Original Message -----

From: Samsudin samsudin@bayan.com.sg [belajar-excel]

To: belajar-excel@yahoogroups.com

Sent: Monday, November 17, 2014 12:19 PM

Subject: RE: [belajar-excel] Meriew Data

Assalamu'alaikum,

Mr. Bagus,

Terima kasih banyak atas pencerahannya dan ada lagi yang ingin saya tanyakan bagaimana caranya cell yang berisi tanggal (sembarang tanggal) dan cell lain ingin menghasilkan awal atau tanggal awal dari cell tersebut,

Misal cell A1 = 23/1/2014 dan bagaimana formulanya supaya pada cell B1 = 1/1/2014.

Hormat saya,

Samsudin, ST

P&M Dept Head

PT. GUNUNGBAYAN PRATAMACOAL (Bayan Group Resources)

O: +62 542 875 860 Ext :61519 |

M: +62 811 2810 068 | +62 813 6914 7150 | email: samsudin@bayan.com.sg

P Save a tree & save energy. Don't print this e-mail unless it's really necessary. Go Green !!!

ISO 9001 : 2008| ISO 14001 : 2004 | OHSAS 18001 : 2007

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com]
Sent: Monday, November 17, 2014 12:56 PM
To: belajar-excel@yahoogroups.com
Subject: Re: [belajar-excel] Meriew Data [1 Attachment]



Wa'alaykum salam warohmatullohi wabarokatuhu..

Pak Samsudin;

Sila lihat file terlampir, Helper ada dikolom E

Wassalam

~ Bagus ~

----- Original Message -----

From: Samsudin samsudin@bayan.com.sg [belajar-excel]

To: belajar-excel@yahoogroups.com

Sent: Monday, November 17, 2014 9:14 AM

Subject: [belajar-excel] Meriew Data [1 Attachment]

Assalmu'alaikum,

Dear All Be-Excel,

Mohon bantuannya untuk bisa mencari solusi seperti pada file terlampir.

Atas bantuannya saya mengucapkan banyak terima kasih

Wassalam,

Samsudin, ST

P&M Dept Head

PT. GUNUNGBAYAN PRATAMACOAL (Bayan Group Resources)

O: +62 542 875 860 Ext :61519 |

M: +62 811 2810 068 | +62 813 6914 7150 | email: samsudin@bayan.com.sg

P Save a tree & save energy. Don't print this e-mail unless it's really necessary. Go Green !!!

ISO 9001 : 2008| ISO 14001 : 2004 | OHSAS 18001 : 2007

---------------------------------------------------------- This message (including any attachments) contains confidential information intendedfor a specific individual and purpose, and is protected by law. If you are not theintended recipient, you should delete this message and hereby notified that anydisclosure, copying, or distribution of this message (or any part thereof), or thetaking of any action based on it, is strictly prohibited. No liability or responsibilityis accepted if information or data is, for whatever reason corrupted or does not reachits intended recipient. No warranty is given that this email is free of viruses. Theviews expressed in this email are, unless otherwise stated, those of the author andnot those of the Company or its management. The Company reserves the right to monitor,intercept and block emails addressed to its users or take any other action in accordancewith its email use policy.

---------------------------------------------------------- This message (including any attachments) contains confidential information intendedfor a specific individual and purpose, and is protected by law. If you are not theintended recipient, you should delete this message and hereby notified that anydisclosure, copying, or distribution of this message (or any part thereof), or thetaking of any action based on it, is strictly prohibited. No liability or responsibilityis accepted if information or data is, for whatever reason corrupted or does not reachits intended recipient. No warranty is given that this email is free of viruses. Theviews expressed in this email are, unless otherwise stated, those of the author andnot those of the Company or its management. The Company reserves the right to monitor,intercept and block emails addressed to its users or take any other action in accordancewith its email use policy.

----------------------------------------------------------

This message (including any attachments) contains confidential information intended
for a specific individual and purpose, and is protected by law. If you are not the
intended recipient, you should delete this message and hereby notified that any
disclosure, copying, or distribution of this message (or any part thereof), or the
taking of any action based on it, is strictly prohibited. No liability or responsibility
is accepted if information or data is, for whatever reason corrupted or does not reach
its intended recipient. No warranty is given that this email is free of viruses. The
views expressed in this email are, unless otherwise stated, those of the author and
not those of the Company or its management. The Company reserves the right to monitor,
intercept and block emails addressed to its users or take any other action in accordance
with its email use policy.

Mon Nov 17, 2014 1:34 am (PST) . Posted by:

"Samsudin"

Assalamu'alaikum,

Mr. Bagus,

Sekali lagi saya mengucapkan terima kasih atas pencerahannya dan saya sudah mencoba membuka-buka file tersebut, dimana pada saat saya klik CHP ternyata hasilnya blank dan saya sudah cek di name manager ternyata nama CHP tidak ada, apakah karena ini sehingga datanya blank?


C H P

No

Equipment Type

Control No Unit

New Base Litre per Week



































































































































































































TOTAL

0


Dan bagaimana caranya jika data tersebut akan dibuat secara kelompok, misalnya :


1. Mining Dept adalah : MOS, MES, QC, Survey, Explorasi, dan IF

2. CHP Dept adalah : Manau dan tepian ulak

3. HRD Dept adalah : HRD, Con.dev, camp service, dan land comp

4. HSE Dept adalah : HSE, Enviro F, Enviro V

5. P&M Dept adalah : P&M dan warehouse

6. GBP adalah Mining dept, CHP Dept, HRD Dept, HSE Dept, P&M Dept.

Sebelumnya tidak lupa saya sampaikan terima kasih.

wassalam,

Samsudin, ST
P&M Dept Head

[cid:image001.jpg@01D0028C.3277E6A0] PT. GUNUNGBAYAN PRATAMACOAL (Bayan Group Resources)
O: +62 542 875 860 Ext :61519 |
M: +62 811 2810 068 | +62 813 6914 7150 | email: samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>
P Save a tree & save energy. Don't print this e-mail unless it's really necessary. Go Green !!!
ISO 9001 : 2008| ISO 14001 : 2004 | OHSAS 18001 : 2007

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com]
Sent: Monday, November 17, 2014 5:09 PM
To: belajar-excel@yahoogroups.com
Subject: Re: [belajar-excel] Meriew Data [1 Attachment]




Wa'alaykum salam warohmatullohi wabarokatuhu..

Pak Samsudin;


File terlampir ada penambahan helper pada A1

Sila dilihat juga pada Name Manager



Wassalam
~ Bagus ~

----- Original Message -----
From: Samsudin samsudin@bayan.com.sg [belajar-excel]<mailto:samsudin@bayan.com.sg%20[belajar-excel]>
To: belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>
Sent: Monday, November 17, 2014 1:41 PM
Subject: RE: [belajar-excel] Meriew Data [1 Attachment]


Assalamu'alaikum,
Mr. Bagus,
Terima kasih formula tersebut sudah Ok (mengubah awal tanggal) dan untuk soal meriew data setelah saya coba ditambah data ke bawah sampai A165, formula tersebut tidak bisa hanya batas sampai A40, padahal rumus (helper) sudah diganti data!$A$5:$A$165 pada cell E5. Dan berikut file terlampir.
Apakah jika ada penambahan baris rumus di sheet view diubah dan bagaimana jika saya tambahkan kolom?
Sebelumnya saya mengucapkan terima kasih.
Hormat saya,
Samsudin, ST
P&M Dept Head

[cid:image001.jpg@01D0028C.3277E6A0] PT. GUNUNGBAYAN PRATAMACOAL (Bayan Group Resources)
O: +62 542 875 860 Ext :61519 |
M: +62 811 2810 068 | +62 813 6914 7150 | email: samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>
P Save a tree & save energy. Don't print this e-mail unless it's really necessary. Go Green !!!
ISO 9001 : 2008| ISO 14001 : 2004 | OHSAS 18001 : 2007
From: belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com> [mailto:belajar-excel@yahoogroups.com]
Sent: Monday, November 17, 2014 2:01 PM
To: belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>
Subject: Re: [belajar-excel] Meriew Data



Wa'alaykum salam warohmatullohi wabarkatuhu..
Pak Samsudin;
Coba begini:
=A1-day(A1)+1
Wassalam
~ Bagus ~
----- Original Message -----
From: Samsudin samsudin@bayan.com.sg [belajar-excel]<mailto:samsudin@bayan.com.sg%20[belajar-excel]>
To: belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>
Sent: Monday, November 17, 2014 12:19 PM
Subject: RE: [belajar-excel] Meriew Data

Assalamu'alaikum,
Mr. Bagus,
Terima kasih banyak atas pencerahannya dan ada lagi yang ingin saya tanyakan bagaimana caranya cell yang berisi tanggal (sembarang tanggal) dan cell lain ingin menghasilkan awal atau tanggal awal dari cell tersebut,
Misal cell A1 = 23/1/2014 dan bagaimana formulanya supaya pada cell B1 = 1/1/2014.
Hormat saya,
Samsudin, ST
P&M Dept Head

[cid:image001.jpg@01D0028C.3277E6A0] PT. GUNUNGBAYAN PRATAMACOAL (Bayan Group Resources)
O: +62 542 875 860 Ext :61519 |
M: +62 811 2810 068 | +62 813 6914 7150 | email: samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>
P Save a tree & save energy. Don't print this e-mail unless it's really necessary. Go Green !!!
ISO 9001 : 2008| ISO 14001 : 2004 | OHSAS 18001 : 2007
From: belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com> [mailto:belajar-excel@yahoogroups.com]
Sent: Monday, November 17, 2014 12:56 PM
To: belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>
Subject: Re: [belajar-excel] Meriew Data [1 Attachment]



Wa'alaykum salam warohmatullohi wabarokatuhu..
Pak Samsudin;
Sila lihat file terlampir, Helper ada dikolom E
Wassalam
~ Bagus ~
----- Original Message -----
From: Samsudin samsudin@bayan.com.sg [belajar-excel]<mailto:samsudin@bayan.com.sg%20[belajar-excel]>
To: belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>
Sent: Monday, November 17, 2014 9:14 AM
Subject: [belajar-excel] Meriew Data [1 Attachment]

Assalmu'alaikum,
Dear All Be-Excel,
Mohon bantuannya untuk bisa mencari solusi seperti pada file terlampir.
Atas bantuannya saya mengucapkan banyak terima kasih
Wassalam,
Samsudin, ST
P&M Dept Head

[cid:image001.jpg@01D0028C.3277E6A0] PT. GUNUNGBAYAN PRATAMACOAL (Bayan Group Resources)
O: +62 542 875 860 Ext :61519 |
M: +62 811 2810 068 | +62 813 6914 7150 | email: samsudin@bayan.com.sg<mailto:samsudin@bayan.com.sg>
P Save a tree & save energy. Don't print this e-mail unless it's really necessary. Go Green !!!
ISO 9001 : 2008| ISO 14001 : 2004 | OHSAS 18001 : 2007

----------------------------------------------------------





This message (including any attachments) contains confidential information intended

for a specific individual and purpose, and is protected by law. If you are not the

intended recipient, you should delete this message and hereby notified that any

disclosure, copying, or distribution of this message (or any part thereof), or the

taking of any action based on it, is strictly prohibited. No liability or responsibility

is accepted if information or data is, for whatever reason corrupted or does not reach

its intended recipient. No warranty is given that this email is free of viruses. The

views expressed in this email are, unless otherwise stated, those of the author and

not those of the Company or its management. The Company reserves the right to monitor,

intercept and block emails addressed to its users or take any other action in accordance

with its email use policy.


----------------------------------------------------------



This message (including any attachments) contains confidential information intended

for a specific individual and purpose, and is protected by law. If you are not the

intended recipient, you should delete this message and hereby notified that any

disclosure, copying, or distribution of this message (or any part thereof), or the

taking of any action based on it, is strictly prohibited. No liability or responsibility

is accepted if information or data is, for whatever reason corrupted or does not reach

its intended recipient. No warranty is given that this email is free of viruses. The

views expressed in this email are, unless otherwise stated, those of the author and

not those of the Company or its management. The Company reserves the right to monitor,

intercept and block emails addressed to its users or take any other action in accordance

with its email use policy.


----------------------------------------------------------



This message (including any attachments) contains confidential information intended

for a specific individual and purpose, and is protected by law. If you are not the

intended recipient, you should delete this message and hereby notified that any

disclosure, copying, or distribution of this message (or any part thereof), or the

taking of any action based on it, is strictly prohibited. No liability or responsibility

is accepted if information or data is, for whatever reason corrupted or does not reach

its intended recipient. No warranty is given that this email is free of viruses. The

views expressed in this email are, unless otherwise stated, those of the author and

not those of the Company or its management. The Company reserves the right to monitor,

intercept and block emails addressed to its users or take any other action in accordance

with its email use policy.



----------------------------------------------------------

This message (including any attachments) contains confidential information intended
for a specific individual and purpose, and is protected by law. If you are not the
intended recipient, you should delete this message and hereby notified that any
disclosure, copying, or distribution of this message (or any part thereof), or the
taking of any action based on it, is strictly prohibited. No liability or responsibility
is accepted if information or data is, for whatever reason corrupted or does not reach
its intended recipient. No warranty is given that this email is free of viruses. The
views expressed in this email are, unless otherwise stated, those of the author and
not those of the Company or its management. The Company reserves the right to monitor,
intercept and block emails addressed to its users or take any other action in accordance
with its email use policy.
============================================================
Pojok Lowongan Kerja yang disediakan milis :
http://milis-belajar-excel.1048464.n5.nabble.com/Pojok-Lowongan-Kerja-f5725753.html
*** Posting lowongan kerja : ke link tersebut dan klik New Topic
============================================================
bergabung ke milis (subscribe), kirim mail kosong ke: belajar-excel-subscribe@yahoogroups.com

posting ke milis, kirimkan ke: belajar-excel@yahoogroups.com

berkunjung ke web milis : http://tech.groups.yahoo.com/group/belajar-excel/messages

melihat file archive / mendownload lampiran : http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di : http://milis-belajar-excel.1048464.n5.nabble.com/

menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com

keluar dari membership milis (UnSubscribe), kirim mail kosong ke : belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------
Diposting oleh EXCEL FEED LEARNING di 01.34

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)