mm/dd/yyの日付をExcelにコピーしたときにyy/mm/ddの順番の正しい日付へ修正するの巻

はじめに

タイトルちょっと何言ってるかわかんないかもですが。。。

mm/dd/yyの日付のテキストデータをExcelに貼り付けたらなんかうまくいかなくってですね。

どういう風にうまくいかないのかと言いますと、

となっている日付のデータがテキストであって、これは

のことなんですが、

コピーして普通にExcelへ貼り付けたら、

と日付が変わっちゃうんです。

時刻の方は合ってるのでこれでいいんですが、年月日が全く変わってしまうのでどうしたもんかいな。

という話です。

原因としては、Excel（というかOS）の日付の表示形式の設定がyyyy/mm/ddの形になっているために、テキストデータの07/02/21を持ってくると素直に（おせっかいに）2007年2月21日と認識されてしまう、と。おそらくは。

そんなわけなので、OSのそもそもの日付表示形式の設定をmm/dd/yyにしておくっていう方法があるようですが、そうすると今度は他のところで影響が出てしまいそうなので、それはやりたくない、と。

あと、mm/dd/yyのテキストデータのままで扱えばいいじゃん、ていう考え方もありますが、日付と時刻を使って集計をするので、計算に使用可能な正しい日付のデータにしておく必要があるのでテキストデータのままだとダメなのよ。

とまあ、なんかいろいろ試行錯誤したので解決方法について忘れないようにメモです。

解決方法

準備　文字列としてExcelに貼り付ける

まず、明示的に文字列としてExcelに貼り付けます。そうしないとExcelに持って行った瞬間に日付データに変換されちゃうので（今回の場合は2021年7月1日なのに、2007年1月21日の日付データにしれっと変換されてしまう）。

まず、貼り付けたい領域の書式設定を「文字列」にしておきます。

コピーしておいたデータを「ペースト」の「貼り付け先の書式に合わせる」で貼り付けます。

これで文字列として貼り付けできました。

もしかしたらコピー元によっては「貼り付け先の書式に合わせる」が出てこないかもですが、なんとかして（!?）Excel上で文字列の状態にします。（今回はWebページに表示した表からコピーして「貼り付け先の書式に合わせる」で貼り付けています。）

この先は2つほど方法が。

方法１ DATE関数を使う

別のセル（ここではセルB7）に、先ほどの文字列から必要な箇所をMID関数またはLEFT関数で取り出して、DATE関数を使って欲しい形の日付データにする計算式を入れます。

=DATE(2000+MID(B3,7,2),LEFT(B3,2),MID(B3,4,2))+MID(B3,10,17)

説明的な図にするとこんな感じ。（それぞれの関数の説明については省略します。）

つまりは

= DATE(2021, 07, 01) + 02:42 pm

となるようにしています。隣のセルも同様にして計算式を入れときます。

で、「セルの書式設定」で「日付」の「2012/3/14 1:30 PM」を選択してやります（種類はお好みで）。

すると、

でけた。表示形式「日付」でかつ正しい日付へと修正できました。これでよしと。

方法2　区切り位置指定ウィザードを使用する

変更したい文字列のセルを選択して、「データ」タブの「区切り位置」を使います。

区切り位置指定ウィザードが出てくるので、そのまま「次へ」

日付の部分と時刻の部分で区切れるように調整して「次へ」

（ちなみに日付と時刻を一緒にまとめてしまうと、うまく修正できませんでした。）

日付のデータの列について、「列のデータ形式」を「日付：MDY」を設定。

時刻のデータの列は「G/標準」のままで。表示先は今回はセルB12を指定しています。

「完了」すると、

でけた。こちらも表示形式「日付」でかつ正しい日付へと修正できました。

ちなみに時刻は「h:mm AM/PM」の表示形式となっていましたので、こちらもOK、と。

end timeの方も同様に（以下略）。

方法1のように日付と時刻を同じセルで表示したいときは双方を足して新たなセルにて表示してやってください。

これでよしと。

おしまい。

実施環境

Microsoft Excel for Mac バージョン16.50