[[20210918081327]]　『EXCEL2016 ダウンロードしたデータの日付の表示形』（ちよ）　ページの最後に飛ぶ

『EXCEL2016 ダウンロードしたデータの日付の表示形式を一括変更したい』（ちよ）

ダウンロードしたデータには、日付がmyyで表示されています。
417　→ 2017年4月のこと
1124 → 2024年11月のこと

やりたいこと
417　を　2017/04　と表示させたい
1124 を 2024/11 と表示させたい

そのためには、どうすればよいでしょうか。よろしくお願いします。

P.S
その後、期限が切れているかどうかを関数で求める予定です

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

417のあるセルを選択したとき、数式バーには2021/4/17などと表示されますか？
それなら、単に表示形式を"yyyy/mm"に変更すればいいですね。
それらのセルをCtrlキーを押しながら次々に選択して、表示形式を変更します。

（γ） 2021/09/18(土) 08:38

数式バーには 417 としか表示されていません。
（ちよ） 2021/09/18(土) 10:09

 表示形式では（汎用性あるものとしては）無理じゃないですかね。
 どこか別セルに数式で出すのが妥当と思います。

 ＞その後、期限が切れているかどうかを関数で求める予定です
 それより、直接それをやる訳にはいかないですか？
 そちらは数式なのでなんとでもなります。

 ・・と言うか、表示形式は見た目だけなんで、そのデータの実体は何も変わってないです。

（半平太） 2021/09/18(土) 10:50

 単なる文字列だったということですね。
 既に指摘があるように、
 期限到来をチェックする数式内で変換したほうが手間はかからないでしょうね。

 どうしても見てくれも変換したいということなら、マクロということになるでしょう。
 変換後も単なる"2017/04"という単なる文字列でいいんですか。
 （期限到来は年月だけで判定するものとすると、
   形式さえ統一されれば、文字列の大小関係で代替できますからね。）

 対象となるセルを選択したうえで、以下のマクロを実行してください。
 Sub test()
     Dim r As Range
     For Each r In Selection
         r.Value = "20" & Right(r.Value, 2) & "/" & Left(Right("0" & r.Value, 4), 2)
     Next
 End Sub

 ===================
 ついでに2017/4/1という日付データにして、表示を"yyyy/mm"にする奴も書いておきましょう。
 Sub test2()
     Dim r As Range
     Dim s As String
     Dim y&, m&
     For Each r In Selection
         s = r.Value
         y = 2000 + CLng(Right(s, 2))
         m = CLng(Left(s, Len(s) - 2))
         r.NumberFormatLocal = "yyyy/mm"
         r.Value = DateSerial(y, m, 1)
     Next
 End Sub

（γ） 2021/09/18(土) 12:15

コメントありがとうございます。

「417」を「2017/04」と表示させるのが目的ではなく、
「417」が期限が切れているかどうかを知ることが目的です。

私の乏しい知識では、「417」をいったん「2017/04」と表示させないと
その後の有効期限が切れているか、切れていないかは求められないと思っていました。

変換しなくても求められるなら、その方法を教えていただきたいです。（どんな数式を入れるのか）
現在、有効期限が切れているか、切れていないかを求めたいです。
そして、それぞれ何件あるかを知りたいです。

よろしくお願いします。
（ちよ） 2021/09/18(土) 13:21

＞417　を　2017/04　と表示させたい
＞1124 を 2024/11 と表示させたい
＞そのためには、どうすればよいでしょうか。
と明確に書いているじゃありませんか？
提示したコードもそっちのけで、
今更、
＞「417」を「2017/04」と表示させるのが目的ではなく、
というのはどうなんですか？
失礼じゃないかな、少し。

（γ） 2021/09/18(土) 13:27

=20&RIGHT(A1,2) & "/" & LEFT(A1,LEN(A1)-2)
（考え無） 2021/09/18(土) 13:35

あ、γさんのマクロとほぼ同じでした
（考え無） 2021/09/18(土) 13:38

申し訳ありませんでした。おっしゃる通りです。本当にすみません。
マクロをご提示いただいたのに、お礼も申し上げず、すみませんでした。
私にはマクロは少しハードルが高いなーと思ってます。。。
（ちよ） 2021/09/18(土) 13:40

 ＞その後、期限が切れているかどうかを関数で求める予定です

 少なくとも「yyyy/mm」の形になっていたら ↑ は自力でできるということですよね？
 その場合はどんな数式なんですか？
 その式を提示してください。

 それと「417」とかのデータはどの列に入ってるんですか？

 とりあえず以上です
（笑） 2021/09/18(土) 13:47

ありがとうございます！

＞その場合はどんな数式なんですか？
　その式を提示してください

以下の式を入れています。

=IF(F2>$I$1,"",1)

◆「417」のデータは、F列です

◆ I1セルに「2021/08」と入れています

◆有効期限内のものはブランク
　有効期限が切れたものには「1」をたてて、後で「1」の数を集計しようと思ってます

よろしくお願いします。

（ちよ） 2021/09/18(土) 14:26

I1セルが文字列だとすれば、
F2の代わりに提示された式を入れるだけですね。

先程の件、了解です。
繰り返しますが、元が文字列なら書式変更ではどうにもなりません。
別のセルに移してから戻すか、マクロで更新するかです。
基本的なことですから頭の片隅において下さい。
（γ） 2021/09/18(土) 14:32

 ＞I1セルに「2021/08」と入れています
 I1セルの表示形式はどうなっていますか？
 文字列なのか、ユーザー定義の yyyy/mm（つまりシリアル値）なのか？

 シリアル値なら
 =IF(DATE(20&RIGHT(F2,2),LEFT(F2,LEN(F2)-2),1)>$I$1,"",1)

 文字列なら
 =IF(DATE(20&RIGHT(F2,2),LEFT(F2,LEN(F2)-2),1)>$I$1*1,"",1)
                                                   ~~~
                                                   ↑「1」を掛ける
 ちなみにユーザー定義を yyyy/mm　にしておけば
 I1セルには 2021/8 と入力するだけです。

 以上
（笑） 2021/09/18(土) 14:53

たくさん教えていただき、ありがとうございます。
エクセル、これから必死に勉強します。

＞I1セルの表示形式はどうなっていますか？
　文字列なのか、ユーザー定義の yyyy/mm（つまりシリアル値）なのか？

　ユーザー定義の yyyy/mm になっています。

＞シリアル値なら

 =IF(DATE(20&RIGHT(F2,2),LEFT(F2,LEN(F2)-2),1)>$I$1,"",1)

　>より左の式がよく分かりません。
　「2017/04」のことだと思いますが、

　・RIGHTは、F2セルの右から2番目までを取ってくるという意味ですか
　・LEFTは、どういう意味ですか。[LEN(F2)]と[-2]が何を意味しているか、分かりません。

   ・[-2]の右隣りの[1]は何でしょうか？

　式全体の説明をお願いできないでしょうか。分からない事ばかりで、ほんとすみません。
　
（ちよ） 2021/09/18(土) 15:30

 ＞=IF(DATE(20&RIGHT(F2,2),LEFT(F2,LEN(F2)-2),1)>$I$1,"",1)

 この式で希望通りの結果になったんですか？

 できてないのに式の説明をしてもむなしさが募るだけなので
 一応、先にお聞きします。

 とりあえず以上です
（笑） 2021/09/18(土) 15:49

おっしゃる通りですね。先に結果をお伝えすべきでした。

今、やってみたところ、希望通りの結果になりました！
15:30に投稿した時は、「1900/01」になったので、何か間違っているのかな？
と思ってましたが、表示形式をユーザー定義の[yyyy/mm]から[標準]に変更したところ
希望通りになりました。ありがとうございます！

より左の()の中の RIGHTとLEFTの間の「,」も何か意味があると思うのですが、分かりません。

何度もすみませんが、どうぞよろしくお願いいたします。
（ちよ） 2021/09/18(土) 16:43

 まず「DATE 関数」についてネットとかで調べてみてください（LEFT、RIGHT、LENも）

 簡単に言うと　=DATE(年,月,日)　という形で日付データ（シリアル値）にします。
 =DATE(2021,9,18)　なら「2021/9/18」の日付になる。

 それだけのことなら、セルに「2021/9/18」と入力すればいいだけですよね。
 DATE関数を使うとき普通は「年月日」のどれか（または全部）を数式で指定します。
 ※年月日すべて直接数値で指定する場合もあります（説明省略）

 =DATE(20&RIGHT(F2,2),LEFT(F2,LEN(F2)-2),1)　という式なら

 年：20&RIGHT(F2,2)
 月：LEFT(F2,LEN(F2)-2)
 日：1

 日は数式ではなく直接「1」を指定。
 なので
 ＞[-2]の右隣りの[1]は何でしょうか？ 
 これは月初の「1日」のこと。
 そこを「18」にすれば18日になります。

 ＞RIGHTとLEFTの間の「,」
 =DATE(2021,9,18)　の「,」と同じです。

 ■年：20&RIGHT(F2,2) について
 F列の値は下2桁が「年」なので、RIGHT(F2,2) でそれを抜き出す。
 417 → 「17」、1124 → 「24」という文字列

 ただし、DATE 関数で年を2桁にして　=DATE(17,10,1)　などとすると
 2017年ではなく「1917年」になってしまいます。

 そのため頭に「20」を付けて　20&RIGHT(F2,2)　とし、年を2000年代の4桁にします。
 17 → 2017、24 → 2024　のように
 2000+RIGHT(F2,2)　でもオッケー

 ■月：LEFT(F2,LEN(F2)-2) について
 年はすべて下2桁ですけど、月は違いますよね。
 417だったら左1文字の「4」、1124だったら左2文字の「11」
 だから　LEFT(F2,2)　としてもダメです。

 LEN(F2)-2　※LEN は文字数を数える関数
 F列の値は 3桁か4桁のどちらか。
 F列が3桁なら 3-2 で「1」、4桁なら 4-2 で「2」

 LEFT(F2,LEN(F2)-2) とすれば、417なら「4」、1124なら「11」を取り出すことができます。

 ■ついでに･･･
 I1セルに「2021/08（または2021/8）」と日にちを省略して入力しても
 それは「2021/8/1（月初の日付）」と同じ値です。

 以上
（笑） 2021/09/18(土) 18:17

とても丁寧に説明していただき、本当にありがとうございました。
今日一日で、すごく勉強になりました。
まだしっかりとは理解できていませんが、ネット等でも調べて理解したいと思います。

今日一日、本当にありがとうございました。

最後に、エクセルが使えるようになるには、まず何からやればよいでしょうか。
今回のように、困ったことにぶつかった時に、ひとつずつ理解していく、その繰り返しでしょうか。
何かアドバイスがあればお願いします。
（ちよ） 2021/09/18(土) 20:02

コメント返信：

[ 一覧(最新更新順) ]

[[20210918081327]] 『EXCEL2016 ダウンロードしたデータの日付の表示形』（ちよ） ページの最後に飛ぶ

[[20210918081327]]　『EXCEL2016 ダウンロードしたデータの日付の表示形』（ちよ）　ページの最後に飛ぶ