oalahhh.... hehehe... saya yang salah... maaf..maaf..coba pakai rumus ini pak
=ROUND( C4+E4+((B4+D4)>(C4+E4))*"12:00:00"-B4-D4,6)
Best Regards,
Fajar Fatahillah
On Thursday, June 4, 2015 12:55 PM, "'Mr. Kid' [email protected]
[belajar-excel]" <[email protected]> wrote:
loooh...
kok nilai waktu yang berbasis 12 jam AM dan 12 jam PM tidak diberitahukan
sih... Lagi pula salah input nilai tuh...
Nilai waktu yang ditulis :
6:15:10
berarti jam 6 pagi lewat 15 menit 10 detik
alias jam :
6:15:10 AM -> tanda AM/PM harus ditulis lengkap saat input nilainya
(entry data)
sedangkan jam :
1:30:15
berarti jam 1 malem lewat 30 menit 15 detik
alias jam :
1:30:15 AM
Kalau jam 1:30:15sebenarnya dimaksudkan jam 1 siang, nulisnya (entry di
cellnya) begini :
1:30:15 PM -> PM nya harus diketik...
hasilnya akan tampak di cell begini :
1:30:15 PM -> kalau formatnya AM/PM
atau
13:30:15 -> kalau formatnya 24 hours
Sebaiknya entry-annya diperbaiki...
btw,
formula untuk entryan yang keliru tersebut bisa berbunyi :
=hari_finish + waktu_finish + "12:00:00" - hari_mulai - waktu_mulai
asumsi :
waktu_mulai selalu dimaksudkan AM alias dari jam 00:00:00 sampai 11:59:59
waktu_finish selalu dimaksudkan PM alias dari jam 12:00:00 sampai 23:59:59
cell hasil di format cells -> tab Number -> opsi Custom -> isi di textbox yang
ada bunyi berikut ini (sama persis) :
[HH]:mm:ss
Wassalam,
Kid
2015-06-04 15:34 GMT+10:00 'Subroto, Matzen' [email protected]
[belajar-excel] <[email protected]>:
Mas Fajar, Seperti ini loh hasilnya
| Tanggal start | Tanggal Finish | Waktu start | Waktu Finish | Durasi waktu
yang dipakai |
| 25 Mei 2015 | 25 Mei 2015 | 6:15:10 | 6:15:10 | 12:00:00 |
| 25 Mei 2015 | 27 Mei 2015 | 6:15:10 | 1:30:15 | 55:15:05 |
| 25 Mei 2015 | 30 Mei 2015 | 6:15:10 | 1:30:15 | 127:15:05 |
| 25 Mei 2015 | 02 Juni 2015 | 6:15:10 | 1:30:15 | 199:15:05 |
Terima kasih atas perhatian dan kerjasamanya, dan saya mohon maaf apabila ada
kata-kata yang kurang berkenan Terima Kasih Matzen From:
[email protected] [mailto:[email protected]]
Sent: 04 Juni 2015 11:53
To: [email protected]
Subject: Re: [belajar-excel] Durasi Waktu Siap pak Matzen,hehehe.. masih
penasaran nih,soalnya dengan file yang sama dan rumus yang sama, di tempat saya
hasilnya benar.apakah mungkin karena settingan komputernya atau format excelnya
yang beda ya??penasaran.com Best Regards,
Fajar Fatahillah On Thursday, June 4, 2015 9:35 AM, "'Subroto,
Matzen'[email protected] [belajar-excel]"
<[email protected]> wrote: Dea mas Fajar Terima kasih atas formula
ya, akan tetapi ketika saya coba di hari yang sama hanya berbeda beberapa jam
saja, maka hasilnya tidak sesuai dengan jumlah durasi jam sebenarnya. Terima
kasih atas perhatian dan kerjasamanya, dan saya mohon maaf apabila ada
kata-kata yang kurang berkenan Terima Kasih Matzen
From:[email protected] [mailto:[email protected]]
Sent: 03 Juni 2015 16:56
To: [email protected]
Subject: Re: [belajar-excel] Durasi Waktu [1 Attachment] di excel saya bisa
pak, file terlampir, bisa lihat file excelnya lagi pak? Best Regards,
Fajar Fatahillah On Wednesday, June 3, 2015 4:42 PM, "'Subroto,
Matzen'[email protected] [belajar-excel]"
<[email protected]> wrote: Untuk formula yang di bawah sudah saya
coba tapi hasilnya seperti ini
| Tanggal start | Tanggal Finish | Waktu start | Waktu Finish | Durasi waktu
yang dipakai |
| 25 Mei 2015 | 26 Mei 2015 | 6:15:10 | 1:30:15 | 19:15:05 |
| 25 Mei 2015 | 27 Mei 2015 | 6:15:10 | 1:30:15 | 19:15:05 |
| 25 Mei 2015 | 30 Mei 2015 | 6:15:10 | 1:30:15 | 19:15:05 |
| 25 Mei 2015 | 02 Juni 2015 | 6:15:10 | 1:30:15 | 19:15:05 |
From:[email protected] [mailto:[email protected]]
Sent: 03 Juni 2015 13:58
To: [email protected]
Subject: Re: [belajar-excel] Durasi Waktu Dear pak Matzen, mungkin formulanya
seperti ini: = ( Tanggal_Finish + Waktu_Finish ) - ( Tanggal_Start +
Waktu_Start) maaf, tidak bisa melampirkan file excel-nya. cmiiw, Regards,
-putri- Pada 3 Juni 2015 12.53, 'Subroto, Matzen'[email protected]
[belajar-excel] <[email protected]> menulis: Dear master Semoga
semua selalu dalam Limpahan Berkah dan Hidayah NyaMohon tambahan ilmunya,
dengan kasus yang saya dapati, yaitu menghitung Durasi waktu dengan hari yang
berbeda seperti file terlampir Terima kasih atas perhatian dan kerjasamanya,
dan saya mohon maaf apabila ada kata-kata yang kurang berkenan 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.
____________________________________________________________________________
____________________________________________________________________________
This email has been scanned for Viruses and Spam. For more information
please contact your local Business Unit Information Security representative.
____________________________________________________________________________
#yiv1114845003 #yiv1114845003 -- #yiv1114845003ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1114845003
#yiv1114845003ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1114845003
#yiv1114845003ygrp-mkp #yiv1114845003hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv1114845003 #yiv1114845003ygrp-mkp #yiv1114845003ads
{margin-bottom:10px;}#yiv1114845003 #yiv1114845003ygrp-mkp .yiv1114845003ad
{padding:0 0;}#yiv1114845003 #yiv1114845003ygrp-mkp .yiv1114845003ad p
{margin:0;}#yiv1114845003 #yiv1114845003ygrp-mkp .yiv1114845003ad a
{color:#0000ff;text-decoration:none;}#yiv1114845003 #yiv1114845003ygrp-sponsor
#yiv1114845003ygrp-lc {font-family:Arial;}#yiv1114845003
#yiv1114845003ygrp-sponsor #yiv1114845003ygrp-lc #yiv1114845003hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1114845003
#yiv1114845003ygrp-sponsor #yiv1114845003ygrp-lc .yiv1114845003ad
{margin-bottom:10px;padding:0 0;}#yiv1114845003 #yiv1114845003actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1114845003
#yiv1114845003activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1114845003
#yiv1114845003activity span {font-weight:700;}#yiv1114845003
#yiv1114845003activity span:first-child
{text-transform:uppercase;}#yiv1114845003 #yiv1114845003activity span a
{color:#5085b6;text-decoration:none;}#yiv1114845003 #yiv1114845003activity span
span {color:#ff7900;}#yiv1114845003 #yiv1114845003activity span
.yiv1114845003underline {text-decoration:underline;}#yiv1114845003
.yiv1114845003attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv1114845003 .yiv1114845003attach div a
{text-decoration:none;}#yiv1114845003 .yiv1114845003attach img
{border:none;padding-right:5px;}#yiv1114845003 .yiv1114845003attach label
{display:block;margin-bottom:5px;}#yiv1114845003 .yiv1114845003attach label a
{text-decoration:none;}#yiv1114845003 blockquote {margin:0 0 0
4px;}#yiv1114845003 .yiv1114845003bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv1114845003
.yiv1114845003bold a {text-decoration:none;}#yiv1114845003 dd.yiv1114845003last
p a {font-family:Verdana;font-weight:700;}#yiv1114845003 dd.yiv1114845003last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1114845003
dd.yiv1114845003last p span.yiv1114845003yshortcuts
{margin-right:0;}#yiv1114845003 div.yiv1114845003attach-table div div a
{text-decoration:none;}#yiv1114845003 div.yiv1114845003attach-table
{width:400px;}#yiv1114845003 div.yiv1114845003file-title a, #yiv1114845003
div.yiv1114845003file-title a:active, #yiv1114845003
div.yiv1114845003file-title a:hover, #yiv1114845003 div.yiv1114845003file-title
a:visited {text-decoration:none;}#yiv1114845003 div.yiv1114845003photo-title a,
#yiv1114845003 div.yiv1114845003photo-title a:active, #yiv1114845003
div.yiv1114845003photo-title a:hover, #yiv1114845003
div.yiv1114845003photo-title a:visited {text-decoration:none;}#yiv1114845003
div#yiv1114845003ygrp-mlmsg #yiv1114845003ygrp-msg p a
span.yiv1114845003yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1114845003
.yiv1114845003green {color:#628c2a;}#yiv1114845003 .yiv1114845003MsoNormal
{margin:0 0 0 0;}#yiv1114845003 o {font-size:0;}#yiv1114845003
#yiv1114845003photos div {float:left;width:72px;}#yiv1114845003
#yiv1114845003photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv1114845003
#yiv1114845003photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1114845003
#yiv1114845003reco-category {font-size:77%;}#yiv1114845003
#yiv1114845003reco-desc {font-size:77%;}#yiv1114845003 .yiv1114845003replbq
{margin:4px;}#yiv1114845003 #yiv1114845003ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv1114845003 #yiv1114845003ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1114845003
#yiv1114845003ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1114845003
#yiv1114845003ygrp-mlmsg select, #yiv1114845003 input, #yiv1114845003 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv1114845003
#yiv1114845003ygrp-mlmsg pre, #yiv1114845003 code {font:115%
monospace;}#yiv1114845003 #yiv1114845003ygrp-mlmsg *
{line-height:1.22em;}#yiv1114845003 #yiv1114845003ygrp-mlmsg #yiv1114845003logo
{padding-bottom:10px;}#yiv1114845003 #yiv1114845003ygrp-msg p a
{font-family:Verdana;}#yiv1114845003 #yiv1114845003ygrp-msg
p#yiv1114845003attach-count span {color:#1E66AE;font-weight:700;}#yiv1114845003
#yiv1114845003ygrp-reco #yiv1114845003reco-head
{color:#ff7900;font-weight:700;}#yiv1114845003 #yiv1114845003ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv1114845003 #yiv1114845003ygrp-sponsor
#yiv1114845003ov li a {font-size:130%;text-decoration:none;}#yiv1114845003
#yiv1114845003ygrp-sponsor #yiv1114845003ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv1114845003
#yiv1114845003ygrp-sponsor #yiv1114845003ov ul {margin:0;padding:0 0 0
8px;}#yiv1114845003 #yiv1114845003ygrp-text
{font-family:Georgia;}#yiv1114845003 #yiv1114845003ygrp-text p {margin:0 0 1em
0;}#yiv1114845003 #yiv1114845003ygrp-text tt {font-size:120%;}#yiv1114845003
#yiv1114845003ygrp-vital ul li:last-child {border-right:none
!important;}#yiv1114845003
