2014-09-26 10:46:13 +0000 2014-09-26 10:46:13 +0000
46
46

Excelが列の日付を認識できない

私はExcelで日付を扱う際にいつも問題を抱えていますが、何か間違ったことをしているに違いありません。

問題の列はこのようになっています

04/08/2012
04/09/2009
04/01/2010
04/21/2011
04/05/2012
08/30/2009
08/29/2010
08/28/2011

Excelで、列をハイライトしてFormat Cells...を選択しました。このダイアログボックスで、私はDateを選択し、ロケールとしてEnglish (United States)を選択し、リストから一致する日付形式を選択しました。この列でデータをソートしてみます。

ソートダイアログで、この列を選択し、値でソートを選択しますが、順序はAからZまでのオプションを与えてくれるだけで、私が期待するような古いものから新しいものまでのオプションは与えてくれません。区切り記号なしでText to Columnsにして、データの種類として'MDY'を選択するという彼の最初の提案は、@r0bertsの答えに行く必要がありますが、それは何ですか?さらに、時間(例: 04/21/2015 18:34:22)がある場合は、まず時間データを削除する必要があります。しかし、その後は @r0berts さんが提案してくれた方法で問題なく動作します。

回答 (21)

72
72
72
2014-09-26 12:49:25 +0000

問題点:「セルの書式設定」-「数値」-「カスタム」で、明示的に「mm/dd/yyyy」で日付であることを伝えようとしているにもかかわらず、Excelは日付を日付として認識しようとしません。ご存知のように、エクセルが何かを日付として認識したとき、エクセルはこれをさらに数字として保存します - “41004 "のような - しかし、あなたが指定したフォーマットに従って日付として表示されます。混乱を招くため、エクセルは2009年8月4日のような日付の一部だけを変換して、他の日付(例:2009年7月28日)は変換されないままにしておくことがあります。データ]で[列へのテキスト]ボタンを選択します。最初の画面でラジオボタンを「delimited」のままにして、Nextをクリックします。区切り記号のボックス(**空白のボックス、チェックマークなし)をクリックし、Nextをクリックします。列のデータ形式で *Date** を選択し、隣接するコンボボックスで MDY を選択して Finish をクリックします。これで日付の値が得られましたが(つまり、Excelは値を007データ型として認識しました)、書式設定はロケールの日付のままで、ご希望の007ではない可能性があります。英語(US)を選択します。これで”Date “のようなフォーマットが表示されます。次に、カスタムを選択すると、”mm/dd/yyyy “と入力するか、カスタム文字列のリストからこれを選択することができます。パトリックさんの投稿のデータを貼り付けたら、「特殊貼り付け」(m/d/yy)を選択し、「書式なしテキスト」を選択します。これで「テキストのインポート」ダイアログが開きます。文字セットはUnicodeのままですが、言語は英語(USA)を選択します。あなたの日付はすぐにデフォルトの米国フォーマットで表示され、日付のソートが可能になります。もしあなたが特殊な米国フォーマットMM/DD/YYYYを希望する場合は、"format Cells "で一度指定する必要があります - 貼り付ける前か貼り付け後のどちらかです。残念ながら、Excelにこれらの文字列を日付として認識させることができたのは、上からのステップ1だけです。このようなことを何度も行う場合は、明らかに面倒なので、ボタンを押すだけでこのようなことをしてくれるビジュアルな基本ルーチンを作成してみてください。

Data | Text to Columns

貼り付け後のleading apostropheの更新:数式バーを見ると、日付が認識されないセルには先頭のアポストロフィがあることがわかります。つまり、数字(または日付)としてフォーマットされたセルにテキスト文字列があることを意味します。先頭のアポストロフィがあるために、スプレッドシートが数字を認識できないということになります。スプレッドシートでは、左寄せの数字のように見えるものが単純に表示されるため、この問題については数式バーで確認する必要があります。この問題に対処するには、修正したい列を選択し、メニューの「mm/dd/yyyy」で選択して「OK」をクリックします。データ型を指定できる場合もありますが、あらかじめ列の書式を特定のデータ型に設定している場合は、その必要はありません。このコマンドは、デリミタを使ってテキスト列を2つ以上に分割することを目的としていますが、この問題に対しても魅力的に動作します。Libreofficeでテストしましたが、Excel_にも同じメニュー項目があります。

8
8
8
2015-08-28 17:14:26 +0000

すべての列を選択し、「検索と置換」に移動して、"/"/に置き換えるだけです。

3
3
3
2017-06-25 09:14:10 +0000

これは元の質問者には関係ないかもしれませんが、日付の列をソートできない人の助けになるかもしれません。

私は、Excelがすべて1900年以前の日付の列をテキストの列だと主張して認識しないことに気づきました(1900年1月1日の1/1は数字の1に相当し、負の数字は明らかに許されていないので)。そこで、(1800年代にあった)すべての日付を1900年代に入れるために、一般的な置換を行いました。これで、ソートはうまくいった。最後に、私は、別の方法で、例えば、190 -> 180.

これが、そこにいる他の歴史家の助けになることを願っている。

3
3
3
2017-04-10 21:49:02 +0000

私は、SAPデータベースから抽出された行の数千と同様の問題を扱っていた、と、不可解なことに、同じ日付列に2つの異なる日付フォーマット(ほとんどが私たちの標準的な “YYYY-MM-DD "であるが、約10%は "MM-DD-YYY "である)。毎月1回650行を手動で編集することはオプションではありませんでした。

上記のオプションのうち、どれも(0…0…0…nil)うまくいきませんでした。はい、すべて試してみました。テキストファイルにコピーしたり、明示的にtxtにエクスポートしたりしても、10%の日付のフォーマットには何の効果もありませんでした。

私がそれを修正することができた唯一の方法は、問題のある日付の列の右側に空白の列を挿入し、次のように単純化された式を使用することでした:

(問題のあるデータがColumn Dにあると仮定して)

=IF(MID(D2,3,1)="-",DATEVALUE(TEXT(CONCATENATE(RIGHT(D2,4),"-",LEFT(D2,5)),"YYYY-MM-DD")),DATEVALUE(TEXT(D2,"YYYY-MM-DD")))

私はその後、計算された列を削除することができた後、"Values "を貼り付けることによって、問題のある日付の上に新しい計算された列に結果をコピーすることができました。

3
3
3
2016-02-22 09:06:59 +0000

私は、エクセルの日付と全く同じ問題を抱えていました。フォーマットは、Excelで日付の要件に一致していますが、まだ各セルを編集することなく、それは日付を認識しないだろうし、あなたが何千もの行を扱っているときに、それは手動で各セルを編集することは現実的ではありません。解決策は、検索を実行して、私はハイフンでハイフンを置き換えた日付の行に置き換えることです。エクセルは列を実行して、似たようなものを似たようなものに置き換えますが、その結果、日付を再整合させることができました! 修正されました。

2
2
2
2014-09-26 11:41:43 +0000

それはExcelが日付として日付を認識していないようですが、それはそれらをテキストとして認識し、それ故にあなたはAからZまでのように並べ替えオプションを取得します。それを行うための最も簡単な方法は、ショートカットCTRL+SHIFT+3を使用しています。

ここで私はあなたのデータに何をしたかです。私は単に上記のあなたの投稿からそれをコピーし、エクセルに貼り付けました。その後、上記のショートカットを適用して、必要なソートオプションを取得しました。画像を確認してみてください。

2
2
2
2015-03-17 09:14:25 +0000

私は、Excelがフォーマットを理解できないことが問題であると疑うだろう… 日付書式を選択しても、テキストのままです。

これは簡単にテストできます。あなたが日付のフォーマットを更新しようとすると、列を選択し、それを右クリックして、(あなたがすでに行っているように)セルの書式設定を選択しますが、オプション2001-03-14(リストの下部近く)を選択します。そして、セルのフォーマットを確認してください。

私は、Excelがまだ日付ではなく、文字列のようにそれを処理していることを示す、日付フォーマットの一部だけが正しく更新されることを疑うでしょう(下のスクリーンショットの異なるフォーマットに注意してください)!

これのための回避策がありますが、あなたが毎回エクスポートしているので、それらのどれもが単純に自動化されません。私はVBaを使用することを提案します。ここでは、単に米国から英国の日付フォーマットに変換するマクロを実行することができますが、これは、毎回シートにVBaをコピー&ペーストすることを意味します。

または、新しく作成されたエクスポートされたExcelシートを読み取るExcelを作成し、VBaを実行して日付フォーマットを更新します。私は、エクスポートされたExchangeのExcelファイルが常に同じファイル名/ディレクトリを持っていると仮定して、作業例を提供します:

そこで、ImportedData.xlsm(excelが有効になっている)という名前の新しいExcelシートを作成します。これは、エクスポートされたExchange Excelファイルをインポートするファイルです!

このVBaをImportedData.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

私がしたことは、この方法では、Excelが最新の値の代わりにソートフィルタA-Zを与える場合でも、それはまだ動作しますので、yyyy-mm-ddに日付形式を更新しました!

私がしたことを確認してください!

私がしたことを確認してください。

私は上記のコードにバグがあると確信していますが、私は明らかにあなたが持っているデータの種類を知らないが、私は(あなたが持っているように)日付の単一の列でそれをテストし、それは私のために正常に動作します! [ どのように私はMS OfficeでVBAを追加するには?

2
2
2
2016-06-23 23:35:04 +0000

https://support.office.com/en-us/article/Convert-dates-stored-as-text-to-dates-8df7663e-98e6-4295-96e4-32a67ec0a680

ここでは簡単な解決策 - 新しい列を作成して使用=datevalue(cell)式を使用して、他の行に式をコピーして修正するには数秒です。

1
1
1
2015-05-14 16:46:45 +0000

謎が解けました!

日付の最初と最後にスペースがありますが、これは何ですか?月の数字の直前をクリックして、シフトキーと左矢印を押して選択してコピーする必要があります。時々、マウスを使ってスペースを選択する必要があります。そして、これをスペースとして貼り付けると、すべての日付が日付になります。 4. If there is space and date Select Data>Go to Data>Text to columns>Delimited>Space as separator and then finish.

0
0
0
2017-11-09 14:24:33 +0000

私は、上記のすべての提案との運を持っていなかった - 魅力のように動作する非常にシンプルなソリューションを思い付いた。Googleシートにデータを貼り付け、日付の列を強調表示し、フォーマットをクリックし、次に番号をクリックし、もう一度番号をクリックして番号形式に変更します。私はその後、データをコピーしてExcelスプレッドシートに貼り付け、日付をクリックして、セルを日付に書式設定します。非常に迅速です。これが役立つことを願っています。

0
0
0
2015-11-17 08:03:48 +0000

私は、単に数字の1をコピーして、それを日付の列(データ)に特殊な貼り付け機能を使って掛け合わせただけです。その後、一般的な書式設定に変更されます。

0
0
0
2016-12-13 11:25:56 +0000

イギリスでの私の解決策 - 私は、このようにそれらの中にドットで日付を持っていました:

03.01.17

私は次のように解決しました:

1.列全体を強調表示します。2.検索して選択/置換してください。私は真ん中のダッシュ例えば03.01.17 03-01-17とすべての完全な停止を置き換えました。列をハイライトしたままにしておきます。 5.セルの書式設定、数字タブで日付を選択します。 6.タイプ14-03-12を使用します。

0
0
0
2016-08-28 14:45:47 +0000

**マクロを実行する必要はありません……シンプルなMSエクセルの数式を編集します。 - 最後に、作成した文字列から、DATE式で日付に変換しました。

0
0
0
2017-01-13 13:02:55 +0000

様々な提案を試してみましたが、私にとって最も簡単な解決策は以下の通りでした…

例の画像1と2を参照してください… (注意 - いくつかのフィールドは例に寄与しないため、意図的に非表示にしています)。私はこれが役立つことを願っています…

  1. フィールドC - 私を絶対に夢中にさせた混合フォーマット。これは、データがデータベースから直接送られてきたもので、余分なスペースやおかしな文字はありませんでした。例えば、テキストとして表示すると、2016年01月01日は「42504」タイプの値として表示され、そのまま表示された2006年04月15日の値と混ざっていました。フィールドF - フィールドCの長さを取得したところ(LEN式では日付形式によっては10のうち5の長さになります)。フィールドは簡単のために一般的なフォーマットです。フィールドG - 混合日付の月成分の取得 - フィールドFの長さの結果(5または10)に基づいて、フィールドCの日付値から月を取得するか、文字列の月文字を取得します。フィールド H - 混合日付の日成分の取得 - フィールド F (5 または 10) の長さの結果に基づいて、フィールド C の日付の値から日を取得するか、文字列の中の日の文字を取得します。 画像1: 値とフィールド名を示す基本的なスプレッドシート 画像2: 上記の説明と一致する数式を示すスプレッドシート

お役に立てることを願っています。

0
0
0
2017-11-20 15:59:18 +0000

この問題は私を狂わせていましたが、私は簡単な修正方法を見つけました。少なくとも私のデータには効果がありました。それは簡単にできて、覚えておくことができます。しかし、私はこれが動作しますが、上記のようにタイプを変更しても動作しない理由がわかりません。1. 日付のある列を選択します。2. 日付の中から年を検索します。選択して、同じ年、2015ですべてを置き換える。3. 年ごとに繰り返します。これは、年ごとに実際の日付にタイプを変更します。より高速なのは、201を検索して201に置き換える(2010年から2019年までを置き換える)、200を200に置き換える(2000年から2009年までを置き換える)、などです。

0
0
0
2017-07-06 23:51:28 +0000

日付を選択して、このコードを実行します。

0
0
0
2016-04-24 01:24:57 +0000

r0bertsを含む上記のすべての解決策は失敗しましたが、この奇妙な解決策は最速の解決策であることが判明しました。

しかし、その後、INTERNET EXPLORERのブラウザでダウンロードしてみたところ、驚くべきことに、列に触れることなく瞬時にソートすることができました。

0
0
0
2015-03-17 10:01:08 +0000

Excelが頑なに列を日付として認識しない場合は、別のものに置き換えてください:

  • 古い列の右に新しい列を追加
  • 新しい列を右クリックしてFormat を選択
  • フォーマットをdate に設定 - 古い列全体をハイライトしてコピー
  • 新しい列の一番上のセルをハイライトしてPaste Specialを選択 values を貼り付けるだけ - これで古い列を削除することができます。
0
0
0
2017-09-27 10:50:00 +0000

これは、Excelでロケール間で日付データを交換するときにいつも起こります。データをエクスポートするVBAプログラムを制御できるのであれば、日付そのものではなく、日付を表す数値をエクスポートすることをお勧めします。数値は、フォーマットやロケールに関係なく、単一の日付と一意に相関します。例えば、CSVファイルが2010年9月24日を表示する代わりに、40445を表示します。

各セルを保持しているときのエクスポートループで、それが日付であれば、CLng(myDateValue)を使用して数値に変換します。これは、テーブルのすべての行を実行してCSVにエクスポートするループの例です(文字列のカンマをインポート時に取り除くタグで置き換えていますが、これは必要ないかもしれません):

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
-1
-1
-1
2017-11-30 10:36:49 +0000

私はMacを使用しています。

また、

テーブルとフィルタに行く>グループの日付をフィルタリングするときにフィルタリングします。

-2
-2
-2
2014-09-26 11:15:58 +0000

私は通常、ソートや集計のために余分な列を作成するだけなので、year(a1)&if len(month(a1))=1,),"")&month(a1)&day(a1)を使用します。007を使用することで、月1~9のために余分なゼロを追加することができます。