[belajar-excel] Formula Tanggal

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[belajar-excel] Formula Tanggal

halak hita
Dear para Master Excel
Saya Export data dari sistim, kemudian tanggal nya menjadi Text.
Tolong dibantu formula yang tepat untuk data berikut sebab menggunakan
Datevalue beberapa berhasil namun ada yg value.

Salam
HH

Formula_Tanggal.xlsx (398K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] Formula Tanggal

Eka Subyantara-2
Coba gunakan cara manual REPLACE ALL (Ctrl+H) tanpa menggunakan
DATEVALUE(), yaitu dengan mengganti SEMUA singkatan nama bulan dengan bulan
yang dikenal dalam sistem penanggalan yang dipakai dalam komputer Anda.
Walaupun harus mengganti dengan yang sama :D
jan ganti dgn jan
feb ganti dgn feb
mar  ganti dgn mar
apr ganti dgn apr
may ganti dgn mei
.... dst sampai
dec ganti dgn des


On Sun, Feb 2, 2020 at 7:22 PM halak hita [hidden email]
[belajar-excel] <[hidden email]> wrote:

>
>
> Dear para Master Excel
> Saya Export data dari sistim, kemudian tanggal nya menjadi Text.
> Tolong dibantu formula yang tepat untuk data berikut sebab menggunakan
> Datevalue beberapa berhasil namun ada yg value.
>
> Salam
> HH
>
>
>
>
Kid
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] Formula Tanggal

Kid
Administrator
In reply to this post by halak hita
Ganti regional setting komputer menjadi English US lalu F9 (calculate)

Jika dengan formula, buat tabel referensi 3 karakter nama bulan dari semua
potensi penulilsan regional setting
MMM     Bulan
Jan         1
Feb         2
Peb        2
dst sampai
Dec      12
Des       12

Lalu formula : =IFError( 1*data , Date( right( data,4) , vlookup(
mid(data,3,3) , referensi , 2 , 0 ) , left( data,2) ) )








On Sun, Feb 2, 2020 at 7:22 PM halak hita [hidden email]
[belajar-excel] <[hidden email]> wrote:

>
>
> Dear para Master Excel
> Saya Export data dari sistim, kemudian tanggal nya menjadi Text.
> Tolong dibantu formula yang tepat untuk data berikut sebab menggunakan
> Datevalue beberapa berhasil namun ada yg value.
>
> Salam
> HH
>
>
>
>
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] Formula Tanggal

Lazuardi Biru
In reply to this post by halak hita
 bisa juga seting bahasa di sistim tidak sama dengan seting bahasa di komputer.

    Pada Minggu, 2 Februari 2020 19.22.46 WIB, halak hita [hidden email] [belajar-excel] <[hidden email]> menulis:  
 
    

Dear para Master Excel
Saya Export data dari sistim, kemudian tanggal nya menjadi Text.
Tolong dibantu formula yang tepat untuk data berikut sebab menggunakan Datevalue beberapa berhasil namun ada yg value.
SalamHH


  #yiv0922913398 #yiv0922913398 -- #yiv0922913398ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv0922913398 #yiv0922913398ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv0922913398 #yiv0922913398ygrp-mkp #yiv0922913398hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv0922913398 #yiv0922913398ygrp-mkp #yiv0922913398ads {margin-bottom:10px;}#yiv0922913398 #yiv0922913398ygrp-mkp .yiv0922913398ad {padding:0 0;}#yiv0922913398 #yiv0922913398ygrp-mkp .yiv0922913398ad p {margin:0;}#yiv0922913398 #yiv0922913398ygrp-mkp .yiv0922913398ad a {color:#0000ff;text-decoration:none;}#yiv0922913398 #yiv0922913398ygrp-sponsor #yiv0922913398ygrp-lc {font-family:Arial;}#yiv0922913398 #yiv0922913398ygrp-sponsor #yiv0922913398ygrp-lc #yiv0922913398hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv0922913398 #yiv0922913398ygrp-sponsor #yiv0922913398ygrp-lc .yiv0922913398ad {margin-bottom:10px;padding:0 0;}#yiv0922913398 #yiv0922913398actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv0922913398 #yiv0922913398activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv0922913398 #yiv0922913398activity span {font-weight:700;}#yiv0922913398 #yiv0922913398activity span:first-child {text-transform:uppercase;}#yiv0922913398 #yiv0922913398activity span a {color:#5085b6;text-decoration:none;}#yiv0922913398 #yiv0922913398activity span span {color:#ff7900;}#yiv0922913398 #yiv0922913398activity span .yiv0922913398underline {text-decoration:underline;}#yiv0922913398 .yiv0922913398attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv0922913398 .yiv0922913398attach div a {text-decoration:none;}#yiv0922913398 .yiv0922913398attach img {border:none;padding-right:5px;}#yiv0922913398 .yiv0922913398attach label {display:block;margin-bottom:5px;}#yiv0922913398 .yiv0922913398attach label a {text-decoration:none;}#yiv0922913398 blockquote {margin:0 0 0 4px;}#yiv0922913398 .yiv0922913398bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv0922913398 .yiv0922913398bold a {text-decoration:none;}#yiv0922913398 dd.yiv0922913398last p a {font-family:Verdana;font-weight:700;}#yiv0922913398 dd.yiv0922913398last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv0922913398 dd.yiv0922913398last p span.yiv0922913398yshortcuts {margin-right:0;}#yiv0922913398 div.yiv0922913398attach-table div div a {text-decoration:none;}#yiv0922913398 div.yiv0922913398attach-table {width:400px;}#yiv0922913398 div.yiv0922913398file-title a, #yiv0922913398 div.yiv0922913398file-title a:active, #yiv0922913398 div.yiv0922913398file-title a:hover, #yiv0922913398 div.yiv0922913398file-title a:visited {text-decoration:none;}#yiv0922913398 div.yiv0922913398photo-title a, #yiv0922913398 div.yiv0922913398photo-title a:active, #yiv0922913398 div.yiv0922913398photo-title a:hover, #yiv0922913398 div.yiv0922913398photo-title a:visited {text-decoration:none;}#yiv0922913398 div#yiv0922913398ygrp-mlmsg #yiv0922913398ygrp-msg p a span.yiv0922913398yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv0922913398 .yiv0922913398green {color:#628c2a;}#yiv0922913398 .yiv0922913398MsoNormal {margin:0 0 0 0;}#yiv0922913398 o {font-size:0;}#yiv0922913398 #yiv0922913398photos div {float:left;width:72px;}#yiv0922913398 #yiv0922913398photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv0922913398 #yiv0922913398photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv0922913398 #yiv0922913398reco-category {font-size:77%;}#yiv0922913398 #yiv0922913398reco-desc {font-size:77%;}#yiv0922913398 .yiv0922913398replbq {margin:4px;}#yiv0922913398 #yiv0922913398ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv0922913398 #yiv0922913398ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv0922913398 #yiv0922913398ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv0922913398 #yiv0922913398ygrp-mlmsg select, #yiv0922913398 input, #yiv0922913398 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv0922913398 #yiv0922913398ygrp-mlmsg pre, #yiv0922913398 code {font:115% monospace;}#yiv0922913398 #yiv0922913398ygrp-mlmsg * {line-height:1.22em;}#yiv0922913398 #yiv0922913398ygrp-mlmsg #yiv0922913398logo {padding-bottom:10px;}#yiv0922913398 #yiv0922913398ygrp-msg p a {font-family:Verdana;}#yiv0922913398 #yiv0922913398ygrp-msg p#yiv0922913398attach-count span {color:#1E66AE;font-weight:700;}#yiv0922913398 #yiv0922913398ygrp-reco #yiv0922913398reco-head {color:#ff7900;font-weight:700;}#yiv0922913398 #yiv0922913398ygrp-reco {margin-bottom:20px;padding:0px;}#yiv0922913398 #yiv0922913398ygrp-sponsor #yiv0922913398ov li a {font-size:130%;text-decoration:none;}#yiv0922913398 #yiv0922913398ygrp-sponsor #yiv0922913398ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv0922913398 #yiv0922913398ygrp-sponsor #yiv0922913398ov ul {margin:0;padding:0 0 0 8px;}#yiv0922913398 #yiv0922913398ygrp-text {font-family:Georgia;}#yiv0922913398 #yiv0922913398ygrp-text p {margin:0 0 1em 0;}#yiv0922913398 #yiv0922913398ygrp-text tt {font-size:120%;}#yiv0922913398 #yiv0922913398ygrp-vital ul li:last-child {border-right:none !important;}#yiv0922913398