Aku terus memiliki masalah bekerja sama dengan tanggal di Excel, saya harus melakukan sesuatu yang salah tapi aku don't memahami apa yang.
Saya memiliki spreadsheet, yang diekspor dari exchange server kami, yang berisi kolom dengan tanggal. Mereka telah keluar di dalam KITA format meskipun saya'm di INGGRIS.
Kolom yang bersangkutan terlihat seperti ini
04/08/2012
04/09/2009
04/01/2010
04/21/2011
04/05/2012
08/30/2009
08/29/2010
08/28/2011
Di Excel, saya telah menyoroti kolom dan pilih Format Cells...
. Di kotak dialog ini, saya telah memilih Tanggal
, pilih bahasa inggris (Amerika Serikat)
sebagai lokal dan memilih sesuai format tanggal dari daftar. Saya tekan OK dan mencoba untuk mengurutkan data berdasarkan kolom ini.
Dalam semacam dialog saya memilih kolom ini, pilih sort pada nilai-Nilai tapi hanya memberi saya pilihan untuk A sampai Z, bukan yang terlama ke terbaru seperti yang saya harapkan.
Hal ini pada gilirannya macam data tanggal dengan dua digit pertama.
Saya sadar saya bisa re-format data ini untuk ISO dan kemudian A sampai Z semacam akan bekerja tapi seharusnya aku't memiliki terlalu, aku'm jelas ada sesuatu yang hilang. Apa itu?
EDIT: aku kacau karunia tapi ini harus pergi ke @r0berts menjawab, pertama saran dari Teks ke Kolom dengan tidak ada pembatas dan memilih 'MDY' sebagai jenis data yang bekerja. Selain itu, jika anda memiliki waktu (yaitu04/21/2015 18:34:22
), anda harus terlebih dahulu menyingkirkan data waktu. Namun setelah itu metode yang disarankan oleh @r0berts bekerja dengan baik.
Masalah: Excel tidak ingin mengenali tanggal sebagai tanggal, meskipun melalui "Format cells - Number - Custom" anda secara eksplisit mencoba untuk memberitahu hal ini adalah tanggal "mm/dd/yyyy
". Seperti yang anda tahu, ketika excel telah mengakui sesuatu sebagai tanggal, lebih lanjut toko-toko ini sebagai nomor - seperti "41004
" tetapi menampilkan tanggal sesuai dengan format yang anda tentukan. Untuk menambah kebingungan excel dapat mengkonversi hanya bagian dari anda seperti 08/04/2009, tetapi meninggalkan yang lain misalnya 07/28/2009 yang belum bertobat.
Solusi: langkah 1 dan 2
1) Pilih kolom tanggal. Berdasarkan Data pilih tombol Teks ke Kolom. Pada layar pertama yang meninggalkan tombol radio pada "dibatasi" dan klik Depan. Lepas klik salah satu pembatas kotak (setiap kotak kosong; tidak ada tanda centang), lalu klik Depan. Di bawah kolom data memilih format Tanggal ** dan pilih MDY di dekat combo box dan klik **Selesai. Sekarang anda punya nilai-nilai tanggal (yaitu Excel telah mengakui nilai-nilai anda sebagai Tanggal
data type), tapi format ini mungkin masih lokal tanggal, bukan mm/dd/yyyy
yang anda inginkan.
2) Untuk mendapatkan hasil yang diinginkan KAMI format tanggal yang ditampilkan dengan benar, anda harus terlebih dahulu memilih kolom (jika tidak dipilih) maka di bawah Format Sel - Jumlah memilih Tanggal DAN pilih Lokal : inggris (US). Ini akan memberikan anda dengan format seperti "m/d/yy
". Kemudian anda bisa pilih Custom dan di sana anda dapat mengetik "mm/dd/yyyy
" atau memilih dari daftar kustom string.
Alternatif : menggunakan LibreOffice Calc. Setelah menyisipkan data dari Patrick's post pilih Paste Special (Ctrl+Shift+V
) dan memilih Teks yang belum Diformat. Ini akan membuka "Impor Text" kotak dialog. Set karakter tetap Unicode tetapi untuk bahasa pilih bahasa inggris(amerika SERIKAT); anda juga harus memeriksa kotak "Mendeteksi nomor khusus". Tanggal segera muncul di default KITA format dan tanggal-sortable. Jika anda ingin khusus KAMI format MM/DD/YYYY anda perlu menentukan ini setelah melalui "format Sel" - baik sebelum atau setelah paste.
Salah satu mungkin berkata Excel harus diakui tanggal segera setelah saya mengatakan itu melalui "Format Sel" dan saya tidak't lebih setuju. Sayangnya hal ini hanya melalui langkah 1 di atas bahwa saya telah mampu membuat Excel mengenali string teks sebagai tanggal. Tentunya jika anda melakukan ini itu adalah rasa sakit di leher dan anda akan menempatkan bersama-sama visual basic rutin yang akan melakukan ini untuk anda dengan menekan sebuah tombol.
Data | Teks ke Kolom
Update memimpin apostrophe setelah paste: bisa Anda lihat di formula bar bahwa dalam sel di mana saat ini tidak diakui ada yang terkemuka apostrof. Itu berarti dalam sel yang diformat sebagai nomor (atau tanggal) ada string teks. Anda bisa mengatakan - terkemuka apostrophe mencegah spreadsheet untuk mengenali nomor. Yang perlu anda ketahui untuk melihat dalam formula bar untuk ini - karena spreadsheet hanya menampilkan apa yang tampak seperti kiri-blok nomor. Untuk menangani masalah ini pilih kolom yang ingin anda benar, pilih di menu Data | Teks ke Kolom
dan klik OK. Kadang-kadang anda akan dapat menentukan jenis data, tapi jika sebelumnya anda telah mengatur format dari kolom yang akan anda khususnya tipe data - anda tidak akan membutuhkannya. Perintah ini benar-benar dimaksudkan untuk memisahkan kolom teks di dua atau lebih menggunakan pembatas, tetapi bekerja seperti pesona untuk masalah ini juga. Saya telah diuji di Libreoffice, tapi ada item menu yang sama di Excel terlalu.
Ini mungkin tidak relevan dengan aslinya penanya, tetapi dapat membantu orang lain yang lebih mampu untuk mengurutkan kolom tanggal.
Saya menemukan bahwa Excel akan tidak mengenali kolom tanggal yang semua sebelum tahun 1900, bersikeras bahwa mereka adalah kolom teks (sejak 1/1/1900 telah numerik yang setara dengan 1, dan bilangan negatif rupanya tidak diperbolehkan). Jadi saya melakukan general mengganti semua tanggal saya (yang pada tahun 1800-an) untuk menempatkan mereka ke 1900-an, misalnya 180 -> 190, 181 -> 191, dll. Proses penyortiran kemudian bekerja dengan baik. Akhirnya aku ganti dengan cara lain, misalnya 190 -> 180.
Semoga ini bisa membantu beberapa sejarawan lain di luar sana.
Saya memiliki masalah yang sama persis dengan tanggal di excel. Format sesuai dengan persyaratan untuk tanggal di excel, namun tanpa editing masing-masing sel itu tidak akan mengenali saat ini, dan ketika anda berurusan dengan ribuan baris itu tidak praktis untuk secara manual mengedit masing-masing sel. Solusi adalah untuk menemukan dan mengganti pada baris tanggal di mana saya diganti hypen dengan tanda hubung. Excel berjalan melalui kolom mengganti suka dengan suka tapi faktor yang dihasilkan adalah bahwa hal itu sekarang recongnises tanggal! TETAP!
Aku sedang berhadapan dengan masalah yang sama dengan ribuan baris diekstrak dari GETAH database dan, entah kenapa, dua format tanggal yang berbeda pada tanggal yang sama kolom (paling menjadi standar kami "YYYY-MM-DD" tetapi sekitar 10% menjadi "MM-DD-YYY"). Mengedit secara manual 650 baris setiap bulan sekali itu bukan pilihan.
Tidak ada (nol... 0... nihil) dari pilihan-pilihan di atas bekerja. Ya, saya mencoba mereka semua. Menyalin ke file teks atau secara eksplisit mengekspor ke txt namun, entah bagaimana, tidak berpengaruh pada format (atau kurangnya oleh kare) dari 10% tanggal yang hanya duduk di sudut mereka menolak untuk berperilaku.
Satu-satunya cara saya bisa memperbaikinya untuk menyisipkan kolom kosong di sebelah kanan nakal kolom tanggal dan menggunakan berikut, agak sederhana rumus:
(dengan asumsi anda nakal data di Kolom D
)
=IF(MID(D2,3,1)="-",DATEVALUE(TEXT(CONCATENATE(RIGHT(D2,4),"-",LEFT(D2,5)),"YYYY-MM-DD")),DATEVALUE(TEXT(D2,"YYYY-MM-DD")))
Saya kemudian bisa copy hasil di baru saya, kolom dihitung atas nakal dengan menyisipkan tanggal "Nilai" hanya setelah yang saya bisa menghapus kolom dihitung.
Saya akan menduga masalah adalah Excel tidak mampu memahami format... Meskipun anda pilih format Tanggal, tetap sebagai Teks.
Anda dapat menguji ini dengan mudah: Ketika anda mencoba untuk memperbarui tanggal format, pilih kolom dan klik kanan di atasnya dan pilih format cells (seperti yang sudah anda lakukan) tapi memilih opsi 2001-03-14 (dekat bawah daftar). Kemudian meninjau format dari sel-sel anda.
Saya akan menduga bahwa hanya beberapa format tanggal update dengan benar, menunjukkan bahwa Excel masih memperlakukannya seperti string, bukan kencan (perhatikan format yang berbeda di screen shot di bawah ini)!
Ada pekerjaan-arounds untuk ini, tetapi, tidak satupun dari mereka akan otomatis hanya karena anda're mengekspor setiap waktu. Saya akan menyarankan menggunakan VBa, di mana anda hanya dapat menjalankan makro yang mengkonversi dari AS ke INGGRIS format tanggal, tapi, ini akan berarti menyalin dan menyisipkan kode VBa anda ke lembar setiap waktu.
Atau, anda membuat Excel yang membaca baru dibuat diekspor Excel lembar (dari exchange) dan kemudian menjalankan VBa untuk memperbarui format tanggal untuk anda. Aku akan menganggap Diekspor Pertukaran file Excel akan selalu memiliki yang sama dengan nama file/direktori dan memberikan contoh kerja:
Jadi, buat baru Excel sheet, yang disebut ImportedData.xlsm (excel diaktifkan). Ini adalah file dimana kita akan mengimpor Pertukaran file Excel ke!
Tambahkan VBa untuk ImportedData.file xlsm
Sub DoTheCopyBit()
Dim dateCol As String
dateCol = "A" 'UPDATE ME TO THE COLUMN OF THE DATE COLUMN
Dim directory As String, fileName As String, sheet As Worksheet, total As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
directory = "C:\Users\Dave\Desktop\" 'UPDATE ME
fileName = Dir(directory & "ExportedExcel.xlsx") 'UPDATE ME (this is the Exchange exported file location)
Do While fileName <> ""
'MAKE SURE THE EXPORTED FILE IS OPEN
Workbooks.Open (directory & fileName)
Workbooks(fileName).Worksheets("Sheet1").Copy _
Workbooks(fileName).Close
fileName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Dim row As Integer
row = 1
Dim i As Integer
Do While (Range(dateCol & row).Value <> "")
Dim splitty() As String
splitty = Split(Range(dateCol & row).Value, "/")
Range(dateCol & row).NumberFormat = "@"
Range(dateCol & row).Value = splitty(2) + "/" + splitty(0) + "/" + splitty(1)
Range(dateCol & row).NumberFormat = "yyyy-mm-dd"
row = row + 1
Loop
End Sub
Apa yang saya juga lakukan adalah mengupdate format tanggal yyyy-mm-dd karena dengan cara ini, bahkan jika Excel memberikan anda semacam filter A-Z bukannya terbaru nilai-nilai, itu masih bekerja!
I'm yakin kode di atas memiliki bug dalam tapi aku jelas tidak tahu apa jenis data yang anda miliki tapi aku diuji dengan satu kolom tanggal (karena anda) dan itu bekerja dengan baik bagi saya!
https://superuser.com/questions/801609/how-do-i-add-vba-in-ms-office
Tampaknya bahwa Excel tidak mengakui tanggal sebagai tanggal, ia mengakui mereka teks, maka anda mendapatkan Semacam pilihan A sampai Z. Jika anda melakukannya dengan benar, anda harus mendapatkan sesuatu seperti ini:
Oleh karena itu, penting untuk memastikan bahwa Excel mengakui saat ini. Cara yang paling sederhana untuk melakukannya adalah menggunakan shortcut CTRL+SHIFT+3.
Berikut ini's apa yang saya lakukan untuk data anda. Aku hanya disalin dari posting anda di atas dan disisipkan dalam excel. Kemudian saya menerapkan shortcut di atas, dan aku yang diperlukan semacam pilihan. Memeriksa gambar.
Solusi sederhana berikut membuat kolom baru menggunakan =datevalue(sel) formula kemudian copy formula ke baris lainnya - beberapa detik untuk memperbaiki
Aku tahu itu!
Ada spasi di awal dan di akhir kencan.
Masalah ini membuatku gila, maka aku tersandung pada sebuah perbaiki mudah. Setidaknya itu bekerja untuk saya data. Hal ini mudah untuk dilakukan dan yang perlu diingat. Tapi aku tidak tahu mengapa ini bekerja tetapi mengubah jenis seperti yang ditunjukkan di atas tidak.
Saya tidak memiliki keberuntungan dengan semua saran di atas - datang dengan solusi yang sangat sederhana yang bekerja seperti pesona. Paste data ke Google Spreadsheet, sorot kolom tanggal, klik format, kemudian nomor, dan nomor sekali lagi untuk mengubahnya ke format angka. Saya kemudian copy dan paste data ke dalam Excel spreadsheet, klik pada peta, kemudian format sel untuk saat ini. Sangat cepat. Semoga ini bisa membantu.
Jika Excel keras kepala menolak untuk mengakui kolom tanggal, menggantinya dengan yang lain :
Format
date
Paste Special
, dan hanya paste nilai-nilai
Aku hanya disalin nomor 1 (satu) dan dikalikan dengan kolom tanggal (data) dengan menggunakan PASTA fungsi KHUSUS. Kemudian perubahan untuk Umum formating saya.e 42102 Kemudian pergi berlaku Tanggal pada formating dan sekarang mengakui itu sebagai kencan.
Semoga ini bisa membantu
Semua solusi di atas - termasuk r0berts - tidak berhasil, tapi ini aneh solusi yang ternyata menjadi yang tercepat memperbaiki.
Saya mencoba untuk memilah "peta" di download spreadsheet transaksi rekening.
Ini telah didownload via browser CHROME. Tidak ada jumlah manipulasi "peta" akan membuat mereka diakui sebagai tua-untuk-baru diurutkan.
Tapi, kemudian saya download via browser INTERNET EXPLORER - menakjubkan - saya bisa langsung tanpa menyentuh kolom.
Saya tidak bisa menjelaskan mengapa browser yang berbeda mempengaruhi format data, kecuali yang dengan jelas apa dan sangat cepat memperbaiki.
BERBAGI SEDIKIT LAMA TETAPI BANYAK SOLUSI YANG LEBIH MUDAH UNTUK DIKENAKAN..... Tidak perlu untuk menjalankan macro atau geeky stuff....sederhana ms excel formula dan editing.
dicampur tanggal excel snapshot:
#NILAI
hasilnya. - Akhirnya, dari string yang kita buat - kita telah dikonversi orang-orang untuk tanggal UPLOAD oleh susu formula.#NILAI
dijemput seperti itu adalah dengan iferror di menambahkan untuk SAAT ini formula & kolom ini dengan format yang diperlukan (di sini, dd/mm/yy)LIHAT FOTO DARI EXCEL SHEET di ATAS (= dicampur tanggal excel snapshot)
Solusi saya di INGGRIS - saya punya tanggal dengan titik-titik di dalamnya seperti ini:
03.01.17
Aku memecahkan ini dengan berikut:
14-03-12
(penting bagi saya untuk memiliki bagian tengah dasbor)Ketika menyortir kolom semua tanggal untuk tahun ini diurutkan.
Saya mencoba berbagai saran, tetapi menemukan solusi termudah bagi saya adalah sebagai berikut...
Lihat Gambar 1 dan 2 untuk contoh... (catatan - beberapa bidang yang tersembunyi sengaja karena mereka tidak memberikan kontribusi untuk contoh). Saya harap ini membantu...
Bidang C - format campuran yang membuatku benar-benar gila. Ini adalah bagaimana data yang datang langsung dari database dan tidak memiliki spasi atau karakter gila. Ketika menampilkannya sebagai teks misalnya, 01/01/2016 ditampilkan sebagai '42504' jenis nilai, bercampur dengan 04/15/2006 yang menunjukkan sebagai.
Bidang F - mana saya memperoleh suhu udara turun lapangan C (LEN formula akan menjadi panjang 5 dari 10 tergantung pada format tanggal). Bidang adalah format Umum untuk kesederhanaan.
Bidang G - mendapatkan bulan komponen dari campuran tanggal - berdasarkan panjang akibat di bidang F (5 atau 10), saya juga mendapatkan bulan dari tanggal nilai di lapangan C, atau mendapatkan bulan karakter dalam string.
Bidang H - mendapatkan hari komponen dari campuran tanggal - berdasarkan panjang akibat di bidang F (5 atau 10), aku mendapatkan hari dari tanggal nilai di lapangan C, atau mendapatkan hari karakter dalam string.
Aku bisa melakukan ini untuk tahun yang baik, kemudian membuat sebuah tanggal dari tiga komponen yang konsisten. Jika tidak, saya dapat menggunakan individu hari, bulan, dan tahun nilai-nilai dalam analisis saya.
Gambar 1: dasar spreadsheet yang menunjukkan nilai-nilai dan nama field
Gambar 2: spreadsheet menunjukkan formula yang cocok penjelasan di atas
Saya harap ini membantu.
Ini terjadi sepanjang waktu ketika bertukar data tanggal antara terkenal di Excel. Jika anda memiliki kontrol atas program VBA bahwa ekspor data, saya sarankan mengekspor jumlah yang mewakili tanggal, bukan tanggal itu sendiri. Nomor unik berkorelasi dengan satu tanggal, terlepas dari format dan lokal. Misalnya, alih-alih file CSV menunjukkan 24/09/2010 ini akan menunjukkan 40445.
Dalam ekspor loop ketika anda memegang setiap sel, jika itu's upload, mengkonversi ke nomor menggunakan CLng(myDateValue). Ini adalah contoh dari loop saya berjalan melalui semua baris dari suatu tabel dan mengekspor ke CSV (catatan saya juga mengganti koma dalam string dengan
arr = Worksheets(strSheetName).Range(strTableName & "[#Data]").Value
For i = LBound(arr, 1) To UBound(arr, 1)
strLine = ""
For j = LBound(arr, 2) To UBound(arr, 2) - 1
varCurrentValue = arr(i, j)
If VarType(varCurrentValue) = vbString Then
varCurrentValue = Replace(varCurrentValue, ",", "<comma>")
End If
If VarType(varCurrentValue) = vbDate Then
varCurrentValue = CLng(varCurrentValue)
End If
strLine = strLine & varCurrentValue & ","
Next j
'Last column - not adding comma
varCurrentValue = arr(i, j)
If VarType(varCurrentValue) = vbString Then
varCurrentValue = Replace(varCurrentValue, ",", "<comma>")
End If
If VarType(varCurrentValue) = vbDate Then
varCurrentValue = CLng(varCurrentValue)
End If
strLine = strLine & varCurrentValue
strLine = Replace(strLine, vbCrLf, "<vbCrLf>") 'replace all vbCrLf with tag - to avoid new line in output file
strLine = Replace(strLine, vbLf, "<vbLf>") 'replace all vbLf with tag - to avoid new line in output file
Print #intFileHandle, strLine
Next i
Saya menggunakan mac.
Pergi ke excel preferensi> Edit> Tanggal pilihan> secara Otomatis mengubah tanggal sistem
Juga,
Pergi ke Meja & Filter> Kelompok tanggal saat penyaringan.
Masalah mungkin dimulai ketika anda menerima file dengan tanggal yang berbeda sistem, dan yang kacau excel fungsi tanggal