スポンサーリンク

【Excel/初心者】エクセル塾:日付時刻の関数③文字列・数値を日付データ(シリアル値)にするには

エクセル塾
スポンサーリンク
※当サイトは広告を含みます

エクセルとの付き合いは〇十年のパート事務員が僭越ながら開催しています、エクセル塾です。
前回は、日付時刻の関数②曜日を表示するには、でした。
【Excel/初心者】エクセル塾:日付時刻の関数②曜日を表示する

今回は、「見た目は日付だけど、ただの文字列」をちゃんとしうた日付データ(シリアル値)に変更するには、をやります。

「別に文字列でもシリアル値でも見た目同じならいいじゃん」というときもありますが、計算に使ったり、曜日を求めたり、書式を変更したり、いろいろするには、日付はシリアル値であるほうが便利な場合が多いです。

1.DATEVALUE関数を利用する方法

DATEVALUE関数は日付と認識できる文字列を日付データ(シリアル値)に変換してくれる関数です。

以下にいくつかやってみました。
「文字列」の列には文字列で日付っぽい値を入力しています。
隣の列にDATEVALUE関数で「文字列」の値を日付変換してみました。
2022/7/4のシリアル値は44746です。
わかりにくいので一番右の列にはDATEVALUEで求めたシリアル値を日付書式にて表しています。

けっこう成功していますね。
ダメだったのは№5の「20220704」と№9の「R4年7月4日」ですね。
DATEVALUE関数は引数に「数値」を指定するとエラーになります。
№5は「数値」とみなされエラーになってしまったようです。
№9はエクセルが「日付である」と認識しなかったようです。

№11の月/日のデータは今日が2022年なので、2022年が補われているようです。
№12の年月だけで日付がないデータは1日が補われていますね。

上記の例ではセルの文字列を参照していますが、
=DATEVALUE(“2022/7/4”)
のように、直接DATEVALUE関数内に文字列の日付を入力することもできます。

関数のダイアログボックスで入力するときに「令和4年7月4日」などと入力すると、自動で文字列の前後にダブルクォーテーションを補ってくれます。
しかし、2022/7/4と入力すると「割り算」になってしまうので、このような場合は手入力で
=DATEVALUE(“2022/7/4”) 
のように、「文字列ですよ!」と主張するダブルクォーテーションを忘れずに入力します。

2.8桁の数値を日付データ(シリアル値)に変換する

先ほどダメだった20220704を文字列取り出し関数とDATE関数を利用して、日付データ(シリアル値)に変換してみましょう。

(1)文字列取り出し関数を使った方法

①LEFT関数で左から4文字取り出す
②MID関数で左から5文字目から2文字取り出す
③RIGHT関数で右から2文字取り出す
④DATE関数に年=①、月=②、日=③を入れる

上図の上段は年、月、日を一つずつ取り出してからそのセルを参照してDATE関数で日付データ(シリアル値)に生成しています。
下段はそれをいっぺんにやったものです。
=DATE(LEFT(セル,4),MID(セル,5,2),RIGHT(セル,2))

(2)TEXT関数を使った方法

こちらのサイトで勉強させていただきました。
http://www.eurus.dti.ne.jp/~yoneyama/Excel/ex-q-a/q_suti_hiduke.html

セルA2に8桁数値の日付20220704があります。
セルB2にTEXT関数を入力し、セルA2の値を 2022/07/04 という書式に変換します。
4桁の数字の次に / を挟み、2桁の数字の次に / を挟み、最後の2桁の数字 という書式にしています。
セルC2でセルB2の「2022/07/04」という文字列を日付データ(シリアル値)に変換します。
セルC2の書式を日付にすると、ちゃんと2022/7/4になります。

ちなみに / の前の ! は、 「/ は意味のない文字列ですよ!」ということを主張するための文字のようです。

セルに分けずにつなげると
=DATEVALUE(TEXT(A2,”0000!/00!/00″))
となります。

3.区切り位置を使って日付データに変換する

エクセルの「区切り位置」という機能を使う方法もあります。
関数と違って、元のデータを置き換えします。

以下の例でやってみましょう。
A列に8桁の数値があります。これを日付データ(シリアル値)に変換したい。

①日付に変換したい数値データが入っているA列を選択した状態で、リボン「データ」の「区切り位置」をクリックします

②ウィザード1/3画面は何も変更せずに「次へ」ボタンを押します
②ウィザード2/3画面も何も変更せずに「次へ」ボタンを押します

④ウィザード3/3画面で、「日付」のラジオボタンをオンにします
⑤「完了」をクリックします。

⑥8桁の数値が日付データに置き換わりました!
 念のため、セルの書式を「標準」にしてみると、シリアル値となっていることが確認できます。

なお、文字列「2022/7/1」でも区切り位置を使った同じ手順でシリアル値に変換することができます。

今回はここまでです。お読みいただきありがとうございました。

日付時刻の関数シリーズ
【Excel/初心者】エクセル塾:日付時刻の関数①
【Excel/初心者】エクセル塾:日付時刻の関数②曜日を表示する
【Excel/初心者】エクセル塾:日付時刻の関数④時刻の関数

コメント

タイトルとURLをコピーしました